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)
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.
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.
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
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?
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 ....
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.