SQL query working in Named Queries, not as a binding

Ignition Early Access
#1

Dear Sirs

I’m trying to read all data from a table. I want to be able to select the table from a different component, by changing a parameter

I have made the following query:
SELECT {analyse}.*
FROM {analyse}

This works in the testing tab when I execute the query, with the analyse parameter SAMPLES. When using the Named query in a view I have to use “SAMPLES” with quotes to work.
but when try to use a binding to a params property I cant find a way to make it work. I would assume this should work: “{view.params.analyse}”

searching earlier topics I found that the MySQL db should have ANSI_QUOTES activated, but I don’t have admin access to the database

1 Like
#2

IIRC, that binding text box expects an expression. Using “{view.params.analyse}” make the expression use the literal value between the quotes. You’d need to escape the quotes and use concat() or + between the quotes and the property reference to get what you want. Check out https://docs.inductiveautomation.com/display/DOC80/Expression+Overview+and+Syntax to see more about escaping and strings.

#3

Well, concat() wouldn’t be applicable in this case–the substitution is in the SQL structure, not a value.

#4

Thanks! I bookmarked that page.

I managed to make the proper string, but pturmel is also correct that it does not help :frowning:

I’m not sure if I understand why it does not work, seems like the “value” behaves differently if you directly adds a value compared to adding an expression that returns the same value.

what I tested this morning:

concat(concat(""", {view.params.analyse}), “”") this returns “samples” with quotes, This does not work
writing “samples” with quotes manually still works

#5

The structure of a SQL query must be complete and static at the point it is submitted to the JDBC driver by Ignition. Structure includes schema, table, and column names. The DB parses (not executes) the structure to create an “execution plan” that identifies the tables and indices involved. Curly brace substitutions are pure string substitutions that happen before the SQL is passed to JDBC. So your original code should have worked, if ANSI quotes really is enabled. Try using reverse-single-quotes instead of the double quotes. That’s MySQL’s proprietary identifier quoting character.

By contrast, ? and :param substitutions (for prep queries and named queries, respectively) take the place of data values in the SQL. And only data values. These substitutions happen during query execution, and use the DB’s binary protocols to include data type information, so no value quoting is appropriate.