How to pass WHERE IN [values for int] to named query as parameter

Ignition
1

Hi Team,

In one of my application I want to multi select the values in drop down and getting results from database
here I am using where in (for int ) in named query
for example :
I have values like 1,2 here when I pass the values in named query(in where) it takes only 1st given value(1)
Is there any other ways of how to pass this as a parameter?

THANK YOU

2

Answered here:

3

Hi,
My db datatype itself int. Hence I need to pass int values as paramater

Thankyou

4

Doesn't change the answer. Just don't use quotes in the query string.

A query authored like:

select someColumn
from someTable
WHERE someColumn IN {someQueryStringParameter}

Would then be called like:

system.db.runNamedQuery('Path/To/SomeNamedQuery',{'someQueryStringParameter':'(1,2,3)'})

Note that the parameter is not sanitized and since there are no single quotes included as part of the parameter value, then they will be interpreted as integers in the database.

EDIT: Corrected query example per @pturmel's correction.

5

Not quite. QueryString parameters must be wrapped in curly braces. Leading colon markers are only used with Value parameters.

3 Likes
6

My avoidance of using them is showing. If I'm writing a Named query and I run into something that can't be handled by any other means, I just use system.db.runPrepQuery().

2 Likes
7

Concur. Just say no to QueryString parameters. Besides, you can pass arrays to "Prep" queries if you have a competent database and JDBC drivers.

In this case, the PostgreSQL syntax for runPrepQuery would look like this:

SELECT * FROM some_table WHERE some_column = ANY (?)
1 Like