Cannot pass ADO connection through global variable

[Landauer]

I am running a query in my program, if I open an ADO connection reference in another vi and try to pass it through a global variable the connection is lost.  The global variable loses all of the connection properties.  If I open the ADO connection and directly pass it to DB Tools Execute Query.vi everything works fine.  Unfortunately, I have about 70 different queries throughout the program so opening a connection then closing it for each query would be cumbersome.

[Sima]

Now, I haven't worked with ADO connections in LabVIEW, but I have in other languages, and it was usually a good idea to open the DB connection, get all the info you want (fill up the recordsets based on the different queries), and close it, so that it doesn't hold many resources hostage for too long. For example, using ASP, it was bad practice to store the actual DB connection object in the global.asa file. Instead, I would open connection, get recordset, close connection and clear recordset for each page that had dynamic data.

I don't know whether this would work for your program though. Could you perhaps have one VI that does this communication with the DB, and then passes out the data to the other VIs? Just a thought.

[Landauer]

We tried that with another system we use and it boged the database down with too many simultaneous connections.  We switched to opening the connection once and storing the reference in a global and it has worked great.  In that case we used Easy SQL vis.  Now the DB connectivity vis have changed and I cannot store the reference globally.

[Sima]

Hmm, would the other option work for you? Have one connection, run all the queries, and then pass the data?

[tst]

It works for me, but only if there is always a VI either running or reserved for running holding the reference. Once all the VIs holding the reference go idle it seems that LV automatically clears the reference and it becomes invalid.

What you can try doing instead is using an LV2 style global which will have several states - at first it receives the connection information and remembers it. Then, whenever you request a connection, it runs one of the DB VIs to see that the connection is valid and if it isn't, it calls the Open Connection state again (since it remembers the connection information). In the end, that VI returns either a valid connection or an error which you can use.