Named Query Parameters not Working via Variable Assignment

Ignition
, ,
1

Hey all,
I'm having an issue executing a Named Query with parameters of type QueryString that get their value from a variable.

Database: MySql

I have the following code for running a Named Query:

table_name = query_params[0]
column_name = query_params[1]
			
system.perspective.print(table_name)
system.perspective.print(column_name)
			
# Convert duration of query into Days, as this is used in Named Query
duration_in_days = Cpv_Utilities.minutes_to_days(self.custom["chart-data"]["duration-minutes"])
			
# Parameters for Named Query
params = {"tableName":table_name, "columnName":column_name, "duration":duration_in_days} 			
data = system.db.runNamedQuery("CpvDashboard/ManualChartDataDaily", params)

The Named Query's parameter types for tableName and columnName are QueryString and the Data Type assigned to them (in the Named Query editor) is String.

I have verified, via the print statements, that I have the correct string values for script variables table_name and column_name.

If I use string literals for the values of keys tableName and columnName in the params, the Named Query works. But when I try to assign those keys their value via variable assignment, it fails.

I would appreciate any feedback on this. Thank you.

ETA:

Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"report_p4" WHERE t_stamp >= (UNIX_TIMESTAMP(NOW() - INTERVAL 90 DAY) * 100' at line 5

at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)

at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:972)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)

at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeQuery(SRConnectionWrapper.java:973)

at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runPrepQuery(SRConnectionWrapper.java:167)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.runPrepQuery(NamedQueryExecutor.java:490)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:399)

... 32 common frames omitted
2

What is the resulting stacktrace/exception/error?

3

@cmallonee

Sorry about that. I posted the exception. I see that it is triggering because the SQL sees a "report_p4" with quotes inside its query, but I'm too dumb to know how to remove those quotes from entering the query.

4
  1. Check the Named Query and make sure you're not wrapping the incoming param in quotes.
  2. Make sure that the query_params iterable you're dealing with is not storing the values as strings-in-strings, like this: '"report_p4"'.
2 Likes
5

Dude, you're my Siddhartha. Thank you very much!

The query_params iterable is the array returned from a system.tag.readBlocking on a tag whose value is an Array of Strings. Because I eat crayons, I had manually entered the value for each index wrapped in quotes. Removing the quotes from the elements of the tag's value fixed this.

3 Likes