Using Multiple Databases

Ignition
1

I experienced something that is probably correct, but I didn’t expect it. I need to understand what’s going on.

I was running a series of batch queries (I was reading from some SQL files) and writing to a different databse that is in my connection properties. Basically, my query looked like this:

system.db.runQpdateQuery(" BEGIN TRANSACTION USE Archives SET IDENTITY_INSERT dbo.Parts ON blah, blah, blah, COMMIT")

This went fine. But, the next time I started the designer all of the queries in my bindings failed and everything was red, obviously because I was still “using” the database in the batch query. I restarted the designer again, but with the same results. All I could do to get out of it was to make a temporary button that ran a short query to “USE MyNormalDatabase” and all was good again.

Does this seem right, that the gateway continued to use the database from the batch query even after I restarted the designer? What would I expect to see if one of the clients were to run a similar query- would it affect the others? Is there a better way to do this that is more Ignition-esque?

2

This is not surprising. The Gateway maintains a pool of database connections that will be used by all clients/designers. If you do something with a connection that fundamentally alters its future behavior, then yeah, other clients/designers are going to be affected. Restarting the Designer has no effect on the database connection pool. Only restarting the Gateway would do that.

I think that if you were to do that within a transaction (see system.db.beginTransaction) then the effect of the USE would be cleared when you close the transaction, but this behavior would be database-dependent.

3

This problem has come up before, in a different situation (‘row count’ session variable in SQL Server). This is probably highly dependent on the database your using, but in that case we found that you could basically do a bit of connection initialization by modifying the “validation query” on the connection, and set it to “test on borrow”. For example, in this case, you might be able to do the following:

Validation query: USE dbname; SELECT 1;

Regards,