CODEWITHSUNDEEP
sqlstringpostgresqlcsvthingworx
aki123
aki123

Reputation: 47

How to cast string input into "where in"

I have a Thingworx Service (SQL Query) in my Database Thing, eg. GetCookiesByIds

I have a parameter ids (STRING) with value 1,2,3

I would like to make a query:

SELECT * FROM Cookies WHERE id IN ([[ids]])`

But I have an exception.

How to cast my parameter ids into the valid format?

Upvotes: 1

Views: 752

Answers (4)

abatista
abatista

Reputation: 79

Add some more details to Tom V's correct answer...

CONTEXT

Surrounding the input parameter with double square brackets [[IDs]] is parameter substitution, which treats the input as a parameter ONLY. In order to pass a string variable in the input parameter, you must use the double angle brackets <<IDs>> which allows for string substitution.

But be WARNED, string substitution can expose your database to SQL injection so be sure to sanitize the input and secure the services using string substitution.

EXAMPLE

Input Parameter: IDs (STRING)
[the value can have a space but MUST be comma separated to return multiple values]

IDs = 26,504

Output Parameter: result (Infotable)
[in my example an Infotable is returned, but the output can be any data type]

Query:

SELECT * FROM Cookies WHERE id IN (<<IDs>>);

(Referenced article can be found here)

image shows id casting

Upvotes: 0

Tom V
Tom V

Reputation: 1496

The syntax if you want to do that is like this

SELECT * FROM Cookies WHERE id IN (<<ids>>)

The Thingworx service will concatenate your input parameter in the query if you use <<>> and define it as actual parameters if you use [[]]

Upvotes: 0

aki123
aki123

Reputation: 47

A)

SELECT * FROM cookies WHERE id = any([[ids]]::int[])

The value of ids must be like: {1,2,3}

B)

SELECT * FROM cookies WHERE id = any(
    CONCAT( '{', [[ids]], '}' ) ::int[]
)

The value of ids must be like: 1,2,3

Upvotes: 1

GMB
GMB

Reputation: 222412

A simple an portable option is:

select *
from cookies
where ',' || $1 || ',' like '%,' || id || ',%'

... where $1 represents the CSV string that is the parameter to your query.

In Postgres, we can also use arrays:

select *
from cookies
where id = any(string_to_array($1, ','))

Upvotes: 0

Related Questions