I switched most of my SQL queries to use Params instead of formatting directly the SQL.

But until now i havent figured a way to pass an array of values (mostly integers) to be used with IN operators.

Example:

SQL: Select * from Customer where Id in (:idlist)

I need to query customers with Ids 1, 2 and 3

If i do

ParamByName('idlist').AsString := '1,2,3';

will not work.

Is there a way to accomplish this?

Luiz

But until now i havent figured a way to pass an array of values (mostly

integers) to be used with IN operators.

That, unfortunately, is not possible. It's a DBMS limitation, parameterized values are actually passed as is to DBMS. So there's nothing you can do unless you can convince DBMS maintainer to implement it.

Is there a way to accomplish this?

No other way than direct formatting for now.

Did you try putting quotes around the param ID in the sql query? in(":myparam")
I am guessing it's the commas that are the problem.

Sqlite will accept double qoutes as will MySQL postgres will not though.

The problem with that Tony is that the the bind might make :myparam a string, so you'll be doing a 1-integer to 1-string comparison, which won't give you any results. Essentially you'll be asking for a list of results whos field is equal to "1,2,3,4", not a list of results in which the field in question contains the numbers 1, or 2, or 3, or 4.

At OP

Due to the nature of the bind mechanism, you won't be able to do it this way. The only way you'll be able to do that is with your program doing string substitution instead of doing the bind. Since you're dealing with integers only, you'll just need to make sure that every entry you're substituting for is actually an integer.

2016-04-10 11:01 GMT-03:00 Tony Caduto :

Did you try putting quotes around the param ID in the sql query? in(":myparam") I am guessing it's the commas that are the problem.

Its the other way around the problem is the quotes

See the attached app.

When using Select * from Test where Id in (1,3) it works

but using Select * from Test where Id in (:idlist) and doing the param binding does not work because is translated into:
Select * from Test where Id in ('1,3')

Luiz

2016-04-10 15:39 GMT-03:00 Stephen Chrzanowski :

I'm afraid is really not possible. Some months ago i searched for a delphi solution and the proposed is the same as yours.

I hoped that would exist some solution in fpc side.

As a workaround, I just implemented pre processing the SQL with a regular expression to detect param binding inside in expression and replace the binding with the string without the quotes before passing to the query.

The remaining issue is that open doors for SQL injection attacks.

Luiz

What about using a stored procedure to do it ? You could pass the list for the in as a string and handle it in the stored procedure. Of course that's no help if using sqlite or other that does not support stored procedures. On Apr 10, 2016 1:39 PM, "Stephen Chrzanowski" wrote:

The problem with that Tony is that the the bind might make :myparam a string, so you'll be doing a 1-integer to 1-string comparison, which won't give you any results. Essentially you'll be asking for a list of results whos field is equal to "1,2,3,4", not a list of results in which the field in question contains the numbers 1, or 2, or 3, or 4.

At OP

Due to the nature of the bind mechanism, you won't be able to do it this way. The only way you'll be able to do that is with your program doing string substitution instead of doing the bind. Since you're dealing with integers only, you'll just need to make sure that every entry you're substituting for is actually an integer.

2016-04-10 16:29 GMT-03:00 Tony Caduto :

I'm working with multiple DB engines, so i try to avoid non standard syntax/features.
My workaround is working fine, so no need to break this rule

Luiz

Well, I guess if there is no user input going into the query it's not a big dea,l of course if there is you should at least sanitize it to prevent injection.
On Apr 10, 2016 2:39 PM, "Luiz Americo Pereira Camara" <