Quick question about Database Connectivity, SQL, and Intermittent Connections

[DDang]

Hi There,

Our company has just begun the shift over to a SQL database. Completely new to me so forgive me for my ignorance. I've figured out how to set up the UDL and all that and I can write and read to the DB no problem. However, we also have a manufacturing plant off-site in which the internet connection isn't the most stable. Before we made the switch over to SQL, I used to log data locally, and then transfer that data whenever a connection was available.

My question is, does the database connectivity toolkit have any feature in which this is accomplished or do I have to develop a way to combat an intermittent internet connection? Is there a predeveloped way to combat this issue? My concern also involves storing this in memory in labview. If the connection is down for an extended period of time and the data is stored in a variable, I don't want the possible situation of labview/computer crashing that would result in loss of data. Maybe my only option is redeveloping my write local -> send to server in a SQL format?

Thanks!

[rolfk]

The database Connectivity toolkit is for the most part simply a convinient LabVIEW wrapper around the ADO interface in Windows. This was not meant as a layer to transparently push data to a specific database without having to worry about the underlaying communications reliability but rather to have one interface to many different databases.

While particular drivers might implement caching either for performance and/or reliability reasons it's not an explicit part of the ADO architecture.

So no you can not assume that ADO/the DB Toolkit takes any specific meseaures to work around unreliable network resources. What you can assume though is that if a specific insert or update operation returned successfully, that the data has been stored in the database in some way that allows the database to represent a consistent state to any subsequent query.

[aputman]

Personally, I don't open an SQL connection and leave it open.  I open the connection, write my data and close it so that I don't have to deal with lost connections as regularly. This is my preference because i am not writing loads and loads of data (maybe two rows each minute). 

One thing you can do is prepare your SQL INSERT statements as text and write them to a file.  When you have a good connection, execute as many of the statements as possible and as they are successful, remove them from the file.  You could do this in a separate while loop where its only job is to check the contents of this file and execute the statements.  You can store the data as comma separated values for example and then build the SQL statements just before writing if you like but by storing the SQL statements, you can INSERT the data manually into the database if needed (in the event of a program crash) with no effort at all simply by executing the contents of this file in SQL Studio.

[Sam_Sharp]

Yeah, you should absolutely be buffering the data locally in case a connection isn't available (even with the best IT department in the world, these things still happen!). One method to do this is to write them to a 'pending transfer' file and then when you get the connection try to push as much data as you can.

I've even written a LabVIEW service before which looks at the pending transfer file and periodically tries to send the data to the database, even if the actual application itself isn't running!

[DDang]

Sounds like I've been on the right mindset all along! Sam_Sharp, if possible, would you mind posting a snippet of that labVIEW service you developed? Always curious to see how others write the same thing. Being as I've taught myself a lot of labview on my own, it's nice to compare to ohers to see different architecture setups. If not possible, I understand. Will probably post my code when developed for review.

[aputman]

I posted a snippet recently regarding handling errors in the DB toolkit.  Basically it tries to reconnect 10 times to a server if the connection fails.  This could be adapted to your situation and how you want to handle the disconnect.  You can find it here.

https://forums.ni.com/t5/LabVIEW/Error-inserting-data-with-DB-connectivity-toolkit/td-p/3189765

[DDang]

 Awesome, thanks aputman! Appreciate it, will take a look at it now.

[mikeporter]

Another potential answer would be to leverage solutions that the DBMS already has built in. All major databases incorporate functionality called replication that is ideal for handling intermittent, slow or otherwise dodgy connections. Think of it like file sync but what you are synchronizing are database records. You database folks set it up once and there is zero maintenance or ongoing effort required.

Mike...