# \[Perspective\] NamedQueryTransaction Error

**URL:** <https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836>\
**Category:** Ignition\
**Tags:** ignition81, scripting, database, perspective\
**Created:** [March 4, 2022, 1:25pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836 "2022-03-04T13:25:10Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 1:25pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/1 "2022-03-04T13:25:10Z")

</div>

Hello.  
I'm trying to update a DB table using a NamedQueryTransaction.

```auto
# Logger
logger = system.util.getLogger("write_recipe_parameters")
# References
custom = self.view.custom

try:
	# Begin the transaction.
	project = "Framework"
	database = "Recipes"
	isolation_level = system.db.READ_COMMITTED
	timeout = 50000 # Milliseconds
	tx_number = system.db.beginNamedQueryTransaction(project=project, database=database , isolationLevel=isolation_level, timeout=timeout)

	for row in system.dataset.toPyDataSet(custom.table.data):	 
		# Start by running a Named Query against the transaction.
		named_query_path = "Config/Recipes/updateRecipeParameter"
		
		params = {
			"recipe_id": custom.recipe.id,
			"parameter_tag_path": row["tag_path"],
			"parameter_value": row["value"]
		}

			result = system.db.runNamedQuery(path=named_query_path, parameters=parameters, tx=tx, getKey=1)

	# Complete the transaction.
	system.db.commitTransaction(tx_number)
	# Close the transaction now that we are done.
	system.db.closeTransaction(tx_number)
except:
	import traceback
	logger.error("Error: %s" % traceback.format_exc())
	raise

```

Still, I'm receiving this error:

> Error: Traceback (most recent call last): File "function:runAction", line 36, in runAction Exception: java.lang.Exception: java.sql.SQLException: Datasource "2" does not exist in this Gateway.

I cannot understand what **Datasource "2"** is. Any hints?

**Line 36** corresponds to:

```auto
result = system.db.runNamedQuery(project, named_query_path, params, tx_number)

```

---

<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:** [March 4, 2022, 1:57pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/2 "2022-03-04T13:57:34Z")

</div>

I believe the error here is because this is running in Gateway Context and `system.db.beginNamedQueryTransaction()` takes 4 arguments not 3. Since the isolation\_level and timeout are both optional you don’t actually get the error until you call the named query.

You’ll notice that the actual value of `system.db.READ_COMMITTED` is 2

In gateway context you must supply `system.db.beginNamedQueryTransaction()` with the database to begin the transaction on.

```auto
tx_number = system.db.beginNamedQueryTransaction(project, 'database connection', isolation_level, timeoutd)

```

[https://docs.inductiveautomation.com/display/DOC81/system.db.beginNamedQueryTransaction](https://docs.inductiveautomation.com/display/DOC81/system.db.beginNamedQueryTransaction)

---

<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:** [March 4, 2022, 2:00pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/3 "2022-03-04T14:00:48Z")

</div>

Recently had a similar issue, solution that worked for me is in this thread-

> [@\[IGN-3829\] Error executing system.db.commitTransaction](https://forum.inductiveautomation.com/t/ign-3829-error-executing-system-db-committransaction/49595):
>
> When I use a named query transaction, but do not actually run any queries against it I receive an error. If I do the same thing using system.db.beginTransaction I do not get an error. Any ideas on how to get around this? txId = system.db.beginNamedQueryTransaction("myDatabaseConnection") system.db.commitTransaction(txId) system.db.closeTransaction(txId) Java Traceback: Traceback (most recent call last): File "\<input\>", line 28, in \<module\> at com.inductiveautomation.ignition.common.scrip…

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 2:02pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/4 "2022-03-04T14:02:08Z")

</div>

Yep, that was the issue. Thank you!  
Still, I'm now getting:

> Error: Traceback (most recent call last): File "function:runAction", line 38, in runAction Exception: java.lang.Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 2:06pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/5 "2022-03-04T14:06:38Z")

</div>

It looks like I’m using all the parameters, mandatory or not. Am I missing something?

---

<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:** [March 4, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/6 "2022-03-04T14:07:23Z")

</div>

If it’s working now then I don’t think you’re missing anything. I think you have a different problem now so I would ignore my earlier post.

Is your named query an UPDATE query?

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 2:09pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/7 "2022-03-04T14:09:00Z")

</div>

Yup. This is my named query:

`UPDATE RECIPE_PARAMETERS SET Value = :parameter_value FROM RECIPES r INNER JOIN RECIPE_TO_GROUP rtg ON rtg.RECIPES __Id = r.ID INNER JOIN RECIPE_PARAMETER_GROUPS rpg ON rtg.RECIPE_PARAMETER_GROUPS__ Id = rpg.ID INNER JOIN RECIPE_PARAMETER_GROUP_TYPES rpgt ON rpgt.ID = rpg.RECIPE_PARAMETER_GROUP_TYPES __Id INNER JOIN RECIPE_PARAMETERS rp ON rp.RECIPE_PARAMETER_GROUPS__ Id = rpg.ID WHERE r.ID = :recipe_id AND rp.TagPath = :parameter_tag_path`

---

<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:** [March 4, 2022, 2:12pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/8 "2022-03-04T14:12:10Z")

</div>

Ok that’s what I thought. Is line 38 this line `result = system.db.runNamedQuery(project, named_query_path, params, tx_number)`? What are you expecting as the result? You can add a `getKey=1` kwarg like

`result = system.db.runNamedQuery(project, named_query_path, params, tx_number, getKey=1)` to get the number of rows affected by your query otherwise I don’t know what you would be getting.

Though I also think you should just get a None type if there was truly nothing returned and everything should be ok regardless. What is your line 38?

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 2:15pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/9 "2022-03-04T14:15:29Z")

</div>

Line 38 is

```auto
result = system.db.runNamedQuery(path=named_query_path, parameters=parameters, tx=tx, getKey=1)

```

Adding the kwarg doesn’t seem to solve the issue. I’m sorry, but I’m pretty new to Perspective and I’m still getting the hang of it.

---

<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:** [March 4, 2022, 2:22pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/10 "2022-03-04T14:22:07Z")

</div>

Ok. So one thing to note is `com.microsoft.sqlserver.jdbc.SQLServerException` in your error - so this error is actually coming directly from SQL Server.

Googling this error yielded

> <https://stackoverflow.com/questions/20669815/why-does-my-code-produce-the-error-the-statement-did-not-return-a-result-set>

Looks like a solution is to run the statement `SET NOCOUNT ON` so that has to get incorporated somewhere. You can try putting this above your UPDATE statement in your named query though I don’t know if it will work. But try that first. So

```auto
SET NOCOUNT ON;

UPDATE RECIPE_PARAMETERS SET Value = :parameter_value ....

```

as your named query.

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 2:24pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/11 "2022-03-04T14:24:57Z")

</div>

Unfortunately, that doesn’t solve the issue either. ☹

---

<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:** [March 4, 2022, 2:25pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/12 "2022-03-04T14:25:29Z")

</div>

Undo that part. Is your named query defined as an update query? Like here -  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/a/7a13463a4c87f8a34bb632f21eefe66b8bda73b6.png)

---

<div class="post-metadata">

**Author:** ![Davide3i](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/davide3i/32/27811_2.png) [@Davide3i](https://forum.inductiveautomation.com/u/Davide3i)\
**Post date:** [March 4, 2022, 2:26pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/13 "2022-03-04T14:26:27Z")

</div>

That was indeed the issue and now I feel pretty dumb.  
Thank you so much!

---

<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:** [March 4, 2022, 2:28pm UTC](https://forum.inductiveautomation.com/t/perspective-namedquerytransaction-error/56836/14 "2022-03-04T14:28:34Z")

</div>

No problem. Just so you know how I figured it - in the stack overflow post the second answer was this -  
`Use execute statement for data manipulation like insert, update and delete and executeQuery for data retrieval like select`

Named queries in Ignition are similar I think in that if it’s a Query Query type, then its expecting a result set of some sort, but you don’t get one from update statements.

In addition, I’ve been refactoring a project to all named queries, and I have made this mistake myself very recently haha.