Named Queries syntax error when passing tag path strings

I have a named query in Ignition 8 like the following:

SELECT TagId FROM sn_tags_and_items where DomainId = :domainId and TagPath like :tagPath

where I pass domainId=99 and a tagPath=/my/tag/path

the query works correctly in Authoring/Testing window of the Named Query tool but as soon as I bind it to some page I am returned in the binding preview with a:

Error_Configuration("RuntimeException: Syntax Error on token: 'DIVIDE (Line 1, char 7)")

notice I have no DIVIDE in my query so I don't know where DIVIDE comes from, probably behind the lines some Ignition 8 escaping is involved.

Do you have a log from the gateway, those usually show the sql

1 Like

The tagpath might pass the tag provider in []. This causes escape issues in SQL.

Show your binding. I suspect the issue is not in the named query, but how your binding is passing the value.

Do you have quotes around the tag path you’re passing in? If not, SQL is taking your / in the tag path to be a division symbol, which is why you’re getting an error about DIVIDE.

Like others have said, if you show more information we can help you better.

I don't think so. The Named query parameter is in value mode, so it will not be parsed as SQL. The error also does not look like it comes from SQL, but from expression evaluation.

Doh! I wrote that part of the code and I still never remember which symbol is string substitution and which is value. :flushed:

Think CSS variable, : sets a value :ok_hand:

I’m a backend dev. CSS is not in my vocabulary. :laughing:

1 Like

What log name do I have to increase debug level for? I tried putting everything containing "query" and "sql" in the name to debug level but I don't see any log related to the problem yet :frowning:

I think it is related to the escaping of symbols because if I put this query:

SELECT * FROM sn_tags_and_items where TagPath LIKE 'Indus/L'

it issues the error:

Error_Configuration(“RuntimeException: Syntax Error on token: 'DIVIDE (Line 1, char 7)”)

if I put this query:

SELECT * FROM sn_tags_and_items where TagPath LIKE '%Indus%'

it throws the error:

"Error_Configuration(“RuntimeException: Syntax Error on token: 'MOD (Line 1, char 11)”)"

just as if it interpreted the % as a modulus operator instead of an SQL percent matching symbol.

Again notice the same query in the query editor throws no warning whatsoever

What database is this? It seems like a JDBC bug.

Lucky

When supplying a constant expression as a value in a binding, you have to use expression language quoting rules. (See the little fx icon…) For comparison, store your string in a custom string property and pass that instead.

1 Like

thank you, that makes totally sense now.