Named Query with Transaction

Ignition
1

NamedQuery will not run. Keeps going to except. Runs and deletes as expected when tx = txID is removed.

def onMessageReceived(self, payload):
	# Get the selected row or rows.
	selRows = self.getSibling('Table').props.selection.data
	
	# Begin a transaction - do not delete unless all can be deleted
	txID = system.db.beginNamedQueryTransaction("SampleTestV2",database = "SampleTestV2")
	
	# If the user gave confirmation, we need to loop through all of them.
	for row in selRows:		             
	    # For each selected row, we need to get the value of the id column in that row.
	    productid = row["ProductID"]
	    try:
	    	# Use the id of the row to delete it from the database table.
	   		system.db.runNamedQuery("Configurations/DeleteProduct", {"ID":productid}, tx = txID)
	    except:
	    	self.getSibling("Status").props.text = "Could not delete. Selection(s) potentially referenced somewhere."
	    	system.db.rollbackTransaction(txID)
	    	system.db.closeTransaction(txID)
	    	
    self.getSibling("Status").props.text = "Successfully deleted"
	system.db.commitTransaction(txID)
	system.db.closeTransaction(txID)
2

Have you tried catching the exception and seeing what it is? It is hard to help without knowing what the exception is.

3

I have but do not know how to get the error and see it.

4

you could try printing the exception to the console like this:

import exceptions
import java.lang.Exception
try:
    # stuff you want to try
except (java.lang.Exception, Exception) as e:
    system.perspective.print(e)
    # whatever else you want to do in Except block

Edit typo in code

1 Like
5

I know that the recommendation is "Use Named Queries", and a lot of the times that is a good answer, but in some cases (for instance this one), a prep query is really the better tool for the job.

Imagine a user selects 10 rows for deletion, do you really want to run 10 quries against the database when you can just run 1?

What about if 5 different users select 2 rows? or 5 rows.

You should really loop through the selected rows gathering all of the ids, and then run a single query.

Just like @pascal.fragnoud showed you here:

1 Like
6

Let's say I do this: How can I assure they all get deleted, or none get deleted?

q = """
delete from your_table
where id in ({})
""".format(','.join('?' * len(selRows)))

system.db.runPrepUpdate(q, [product['ProductID'] for product in selRows])
7

runPrepUpdate returns the number of rows affected, so if it is zero then nothing was deleted.

8

What if I did not want it to delete any row unless every row was able to be deleted?

9

You would use system.db.beginTransaction to get a tx id then use that with your prepUpdates. If something fails you can rollback or if all is well then commit and close the transaction.

system.db - Ignition User Manual 8.1 - Ignition Documentation (inductiveautomation.com)

10

Thats what I am thinking, but how do I know if it fails, a try and except???

11

What database do you use in the named query? vs the one in beginNamedQueryTransaction?

12

The same database

13

As you specify the project in beginNamedQueryTransaction, can you try to specify the project in runNamedQuery also?

14

Depends on what you call a fail.

It's usually a good idea to catch exceptions (note that in this case you'll need to catch java's exceptions, or throwable), something like

from java.lang import Throwable

open_transaction()
try:
  your_queries
except Throwable as e:
  rollback_transaction()
else:
  commit_transaction()
finally:
  close_transaction()

But not all unsuccessful operations raise an error, so it's up to you to do whatever check is necessary.

15

Would this not rollback all the ones that raise an exception and commit the ones that do not ?

16

He could check the number of expected rows affected with what were actually affected by the prepUpdate, then rollback if they don't match.

Here is a good post to look at
Script Benchmarking with Decorators Example - Ignition - Inductive Automation Forum

It's all or nothing with the transaction, it's not individual transactions, it's individual queries inside one transaction, if anything fails everything inside the transaction will rollback unless you break them up into multiple transactions.

2 Likes
17

Nope, it rolls back the ENTIRE transaction.

1 Like