Batch SQL statements

Is there a way to batch several SQL statements and then send them to the database to be committed?

I am looping through several components and would like to reduce my hits to the database.

Jonathan, one thing I do, that may also work for you, is to add a hidden table to a window that has several components on it, and bind that table to a SQL query that selects the values for all the other components. I then bind the other components to different fields in the table. This gives only one hit to the SQL server.

Hey RRRancher, just a quick note: What you’re doing is a good idea, but if the table isn’t needed for display, just add a dataset custom property on the root container of the window, and bind that. Minimal difference, but it does avoid the unnecessary component on the window.

Jonathan, there isn’t really a way to group multiple queries into one command (different JDBC drivers do different things if you try, for example by separating queries by “;”, and many will throw an error saying it’s a security risk), beside the more abstract grouping of transactions through scripting (which may help performance, but won’t save on comm transfers).

What kind of queries are you running?

Regards,

I have up to 32 components with a drop down box and a text field. On a save button action performed event I am looping through the containers and checking if the value has changed. If there is a record already existing I update and if it is a new I do an insert.

Would be nice to send all the updates and inserts at one time.

I see there is a java library java.sql with a way to use addBatch to a statement data type, but i cannot figure out how to use it within Ignition.

Jonathan
That really sounds like it might be good place to use transaction group if you aren’t already.

I am, but that still hits the database for every iteration of the loop. The transaction helps from leaving the database in an inconsistent state if one of the statements fails.

Another solution I have tried is passing a combination of string values to a stored procedure and parsing out all the details on the database side, but that isn’t very elegant.

After I came across this a few days ago I thought maybe there was a way in Ignition to do the same thing. http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

No, there isn’t a way to get to the JDBC batching functions. Is running 32 queries at a time really putting that much of a strain on your db/network, though?

I forget what database you guys use, but there is likely some type of query that lets you avoid doing a “select then update/insert”, such as mysql’s INSERT… ON DUPLICATE KEY UPDATE This would at least reduce the amount of work required…

Hope this helps,