Filter table with Named Query issue

Ignition
1

Hello,

I’m tryng to make a simple filter on a datatable with a named query but I can’t make it works…
I tryed with a simple query like

SELECT * FROM products WHERE product_type=1

I wanto to parameterize the where clause.
So I created a named query with a String parameter named prod_type

The named query will be:
SELECT * FROM products WHERE :prod_type

Then I want to bind the :prod_type parameter to a String property
Something like if the property value is “1=1” the where clause is ever True
and if the property value is “product_type=2” I can see only filtered rows.

The problem is that while testing the parameter of the named query, it works with “1=1” value but it doesn’t work with “product_type=2” value.

Where is the mistake?

Thanks in advance,
Federico

2

The :variable syntax is used to insert parameters, not parts of query strings. This means they will get escaped in SQL (wrapped in quotes and any quotes escaped).

If you want to pass parts of query strings (which is less secure and more likely to have bugs, potentially erasing data), you should use a “QueryString” parameter type, and the {sqlQueryPart} syntax.

So you should probably use the following query, and pass on a prod_type value:

SELECT * FROM products WHERE product_type = :prod_type
3

Thank you for your answer. I supposed it was an escape problem.
I tried to use querystring type and now It works…even if this is not the best pratice.
Thank you Sanderd17