I am trying to pass in the name of a table to a named query, If I hard code the table name in the query it works, but when I pass in the name as a parameter I get a syntax error. Any ideas?
Change the type of the parameter from Value to QueryString, and use curly braces around it in the SQL. Per the manual:
https://docs.inductiveautomation.com/display/DOC80/Named+Query+Parameters
1 Like
1 Like
I found that unlike the named query test, when I use it in a binding I need single quotes (‘List’). Now I have to figure out how to put those into a dynamic value reference
When using a query string parameter, whatever parameter you supply will be inserted verbatim into the executed query. So if you’re using an expression to reference someTable, it’ll go into your query as:
SELECT *
FROM someTable
You can just add the quotes in your named query, since, again, it’s a literal string replacement, not the parameter substitution that’s preferred for named queries (because it’s a literal string replacement, it re-opens the SQL injection vulnerability named queries were designed to prevent). So:
SELECT *
FROM '{TableList}'
Should work just as well.
EDIT: Nope, Phil and Oscar are right, ignore me.
The reason you needed single quotes in the binding is because when you set values in there the input is actually an expression editor which expects string literals to be in quotes. This allows you to dynamically assign values in bindings. You’d see more of the editor if you hit the little “fx” button on the right of the cell.
4 Likes
Uhm, no. Single quotes in SQL are always value quotes, never identifier quotes. But see @osolorzano's comment for why single quotes work in this case (they aren't injected into the SQL).
1 Like