[Perspective] NamedQueryTransaction Error

Ignition
, , ,
1

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

# 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:

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

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.

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

https://docs.inductiveautomation.com/display/DOC81/system.db.beginNamedQueryTransaction

2 Likes
3

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

1 Like
4

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.

5

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

6

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?

1 Like
7

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

8

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?

1 Like
9

Line 38 is

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.

10

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

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

SET NOCOUNT ON;

UPDATE RECIPE_PARAMETERS SET Value = :parameter_value ....

as your named query.

1 Like
11

Unfortunately, that doesn’t solve the issue either. :frowning:

12

Undo that part. Is your named query defined as an update query? Like here -
image

2 Likes
13

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

1 Like
14

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.

3 Likes