# Named Query with Transaction

**URL:** <https://forum.inductiveautomation.com/t/named-query-with-transaction/83999>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [January 9, 2024, 7:20pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999 "2024-01-09T19:20:30Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 9, 2024, 7:20pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/1 "2024-01-09T19:20:30Z")

</div>

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

```auto
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)
		

```

---

<div class="post-metadata">

**Author:** ![chandler](https://avatars.discourse-cdn.com/v4/letter/c/e9a140/32.png) [@chandler](https://forum.inductiveautomation.com/u/chandler)\
**Post date:** [January 9, 2024, 7:44pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/2 "2024-01-09T19:44:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 9, 2024, 7:48pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/3 "2024-01-09T19:48:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![chandler](https://avatars.discourse-cdn.com/v4/letter/c/e9a140/32.png) [@chandler](https://forum.inductiveautomation.com/u/chandler)\
**Post date:** [January 9, 2024, 8:15pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/4 "2024-01-09T20:15:11Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [January 9, 2024, 9:36pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/5 "2024-01-09T21:36:50Z")

</div>

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:

> [@GUI - Perspective - Confirm / Cancel popup](https://forum.inductiveautomation.com/t/gui-perspective-confirm-cancel-popup/83968/41):
>
> Any error message in your logs ? Try removing the project name. As @lrose said, you're using a regular transaction for named queries. Use a namedQueryTransaction. But I also agree with him: That's hardly transaction-worthy. If you're never rolling back... In this particular case, I'd probably just build the query dynamically and use runPrepUpdate just once. something like q = """ delete from your\_table where id in ({}) """.format(','.join('?' \* len(selRows))) system.db.runPrepUpdate(q, […

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 10, 2024, 1:15pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/6 "2024-01-10T13:15:39Z")

</div>

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

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

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

```

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 10, 2024, 1:17pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/7 "2024-01-10T13:17:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 10, 2024, 1:18pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/8 "2024-01-10T13:18:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 10, 2024, 1:20pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/9 "2024-01-10T13:20:36Z")

</div>

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)](https://docs.inductiveautomation.com/display/DOC81/system.db)

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 10, 2024, 1:23pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/10 "2024-01-10T13:23:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![calimero100582](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/calimero100582/32/18630_2.png) [@calimero100582](https://forum.inductiveautomation.com/u/calimero100582)\
**Post date:** [January 10, 2024, 1:26pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/11 "2024-01-10T13:26:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 10, 2024, 1:27pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/12 "2024-01-10T13:27:50Z")

</div>

The same database

---

<div class="post-metadata">

**Author:** ![calimero100582](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/calimero100582/32/18630_2.png) [@calimero100582](https://forum.inductiveautomation.com/u/calimero100582)\
**Post date:** [January 10, 2024, 1:31pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/13 "2024-01-10T13:31:04Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [January 10, 2024, 1:32pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/14 "2024-01-10T13:32:09Z")

</div>

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

```python
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.

---

<div class="post-metadata">

**Author:** ![HelloWorld](https://avatars.discourse-cdn.com/v4/letter/h/b782af/32.png) [@HelloWorld](https://forum.inductiveautomation.com/u/HelloWorld)\
**Post date:** [January 10, 2024, 1:34pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/15 "2024-01-10T13:34:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 10, 2024, 1:36pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/16 "2024-01-10T13:36:25Z")

</div>

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](https://forum.inductiveautomation.com/t/script-benchmarking-with-decorators-example/63507/5)

> [@HelloWorld](#):
>
> Would this not rollback all the ones that raise an exception and commit the ones that do not ?

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.

---

<div class="post-metadata">

**Author:** ![bschroeder](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bschroeder/32/13260_2.png) [@bschroeder](https://forum.inductiveautomation.com/u/bschroeder)\
**Post date:** [January 10, 2024, 1:37pm UTC](https://forum.inductiveautomation.com/t/named-query-with-transaction/83999/17 "2024-01-10T13:37:39Z")

</div>

Nope, it rolls back the ENTIRE transaction.