# How to test transactions

**URL:** <https://forum.inductiveautomation.com/t/how-to-test-transactions/79295>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [September 18, 2023, 3:25pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295 "2023-09-18T15:25:41Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![wpmccormick](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/wpmccormick/32/13940_2.png) [@wpmccormick](https://forum.inductiveautomation.com/u/wpmccormick)\
**Post date:** [September 18, 2023, 3:25pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/1 "2023-09-18T15:25:41Z")

</div>

What is the easiest way to test SQL transactions?

I'm looking for some way a non-programmer QA tester can affect series of named queries within a transaction to validate that the transaction as a whole is working as intended.

Cheers!

---

<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:** [September 18, 2023, 3:32pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/2 "2023-09-18T15:32:47Z")

</div>

I guess you could put an insert query in a transaction, then make a page with a form to input the query parameters, and throw a table on that page to display the contents of the db table you inserted into ?

---

<div class="post-metadata">

**Author:** ![wpmccormick](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/wpmccormick/32/13940_2.png) [@wpmccormick](https://forum.inductiveautomation.com/u/wpmccormick)\
**Post date:** [September 18, 2023, 3:39pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/3 "2023-09-18T15:39:12Z")

</div>

I should have mentioned this is on an existing system, where previous developers were somewhat nonchalant when it came to using NQ's to make DB updates. So we're having to go back into existing code and wrap the NQ's in transactions.

My best solution so far is to _lock_ a table (make it RO) so the NQ fails.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 18, 2023, 3:52pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/4 "2023-09-18T15:52:01Z")

</div>

Hopefully some of these tables have some data integrity rules on it, either uniqueness or non-allowed values. Try inserting something that violates one of those to cause a SQL error to make sure your rollback works correctly.

If there is nothing like this, you can always add a temporary insert/update trigger that always raises an error no matter what to induce a SQL error and ensure your code is handling SQL exceptions/rolling back correctly.

---

<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:** [September 18, 2023, 3:53pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/5 "2023-09-18T15:53:30Z")

</div>

I'm not sure I can help you with how to test a transaction, to be frank I'm not sure what exactly you want to test.... but

> [@wpmccormick](#):
>
> So we're having to go back into existing code and wrap the NQ's in transactions.

I had to wrap a bunch of queries into transactions. I made a `transaction` decorator so I wouldn't have to do it for EVERY function. Maybe this can give you ideas if you're wondering how to speed up the process.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 18, 2023, 3:56pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/6 "2023-09-18T15:56:07Z")

</div>

Hard agree on this, a decorator is a very nice way to handle a transaction - then your business logic functions should boil down to the series of named queries you are running in a row essentially.

Here's an example I've posted before about a decorator that handles a transaction and returns a GUI-agnostic response object so that you can then decide what to do with the results -

> [@Script Benchmarking with Decorators Example](https://forum.inductiveautomation.com/t/script-benchmarking-with-decorators-example/63507/5):
>
> I made something similar to what you are aiming for but its also made for managing database transactions and errors in a standard way by creating a Response object that then I inspect after. import functools import timeit import traceback from forms2.structs import Response import java.lang.Throwable class handleTransaction(object): def \_\_init\_\_(self, logger): self.logger = logger def \_\_call\_\_(self, inner\_func): """ We do 'tx' not in kwargs a few times here. If a fucntion has tx=None…

There's a few other examples as well across the forum with decorators handling transactions, and you may want to modify it for your use case, but this is the gist of it.

---

<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:** [September 18, 2023, 4:08pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/7 "2023-09-18T16:08:00Z")

</div>

Mine is... quite different.  
I use function style instead of class, and I raise custom errors instead of returning a response.

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [September 18, 2023, 4:10pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/8 "2023-09-18T16:10:04Z")

</div>

As long as we're on this tangent...  
I have plans to add a first-party `Transaction` context manager, so something like:

```python
with system.db.transaction(settings...) as tx:
    tx.runPrepUpdate()
    tx.commit()

```

To at least avoid the explicit `begin` and `close` transaction calls, the need to repeat the transaction ID, etc.

---

<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:** [September 18, 2023, 4:12pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/9 "2023-09-18T16:12:30Z")

</div>

I actually tried to go the context manager way before implementing the decorator, but it somehow wouldn't work :X

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 18, 2023, 4:15pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/10 "2023-09-18T16:15:05Z")

</div>

Yea I am partial the class style now - makes feeding things into the decorator easier (for me) like the LOGGER for whatever scripting library I am calling it in / using the ` __init__ ` part of the class to default a logger if needed (though I don't think I do that in the version I linked).

Ha, I tried the making a context manager style too originally but couldn't get it working either.

---

<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:** [September 18, 2023, 4:28pm UTC](https://forum.inductiveautomation.com/t/how-to-test-transactions/79295/11 "2023-09-18T16:28:42Z")

</div>

I get what you mean for decorator parameters.  
But in this case I don't use any, I declare the logger like this:

```python
logger = system.util.getLogger("transaction_".format(func. __name__ ))

```