What’s the timescale for support for database transactions in Ignition?
Ignition has the SQL Bridge Module … same functionality as FactorySQL … is this what your speaking of?
What types of transactions would you be looking to manage? Basic transaction groups are just doing a single sql statement (insert, update), but if you needed something else like business logic or data validation/lookups, couldn’t you just wrap all that up in a stored procedure and handle commits/rollbacks there?
I think he’s looking more for transaction support in the scripting language, so that from Jython scripts you could use/manage transactions. This would be useful for more complicated scenarios in the Vision module, like inserting/updating several related records off of a button press.
It’s something that we’re definitely going to do sometime soon, though I don’t have an exact time frame.
It shouldn’t be very tough for us to add this. I’ve scheduled it into the 7.1 timeframe.
Hmmm, I’ve been doing this all along. Do you mean something besides something like this?
q= "BEGIN TRANSACTION "
(A whole bunch of python code that creates a long sql transaction)
q+= " COMMIT "
system.db.runUpdateQuery(q)
I do this a lot when I have to initialize some starting production data with, say, 1000 parts, or update several different tables at once. The one thing I couldn’t get working (Bobby saw the same thing) is using scope_identity() inside the query. It would be cool to be able to do that, since I then wouldn’t have to break the query up into several queries just to get the key with system.db.runUpdateQueryGetKey().
But in this example you send the queries to the database all at once. What would be nice is if you could start a transaction, issue many queries via separate calls to runUpdateQuery, and then commit or rollback.
Ok, I’m trying to learn something. What is the advantage of doing it with multiple calls vs one call? Is it so you could first analyze the result of the previous queries as you go through them, and then roll them back at any time if you so choose?
Yep, thats right. There are various reasons why you might not be able to know all the queries you’ll want to issue in advance.
Transaction support has been added for Ignition 7.1.5
That’s great Carl. Any idea when this will be released? I downloaded version 7.1.5.5650-beta7 but there is no sign of transaction support (at least in the documentation).
Today.