Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Luiz Americo Pereira Camara-3
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

 

 

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

leledumbo
Administrator
> 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.
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Snorkl e

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.

On Apr 10, 2016 7:40 AM, "leledumbo" <[hidden email]> wrote:
> 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.




--
View this message in context: http://free-pascal-general.1045716.n5.nabble.com/Sqldb-How-to-pass-an-array-of-values-as-a-param-to-be-used-with-SQL-IN-operator-tp5724873p5724874.html
Sent from the Free Pascal - General mailing list archive at Nabble.com.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Stephen Chrzanowski
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.


On Sun, Apr 10, 2016 at 10:01 AM, Tony Caduto <[hidden email]> wrote:

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.

On Apr 10, 2016 7:40 AM, "leledumbo" <[hidden email]> wrote:
> 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.




--
View this message in context: http://free-pascal-general.1045716.n5.nabble.com/Sqldb-How-to-pass-an-array-of-values-as-a-param-to-be-used-with-SQL-IN-operator-tp5724873p5724874.html
Sent from the Free Pascal - General mailing list archive at Nabble.com.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Luiz Americo Pereira Camara-3
In reply to this post by Snorkl e


2016-04-10 11:01 GMT-03:00 Tony Caduto <[hidden email]>:

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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

project1.lpr (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Luiz Americo Pereira Camara-3
In reply to this post by Stephen Chrzanowski

2016-04-10 15:39 GMT-03:00 Stephen Chrzanowski <[hidden email]>:
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.


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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Snorkl e
In reply to this post by Stephen Chrzanowski

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" <[hidden email]> 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.


On Sun, Apr 10, 2016 at 10:01 AM, Tony Caduto <[hidden email]> wrote:

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.

On Apr 10, 2016 7:40 AM, "leledumbo" <[hidden email]> wrote:
> 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.




--
View this message in context: http://free-pascal-general.1045716.n5.nabble.com/Sqldb-How-to-pass-an-array-of-values-as-a-param-to-be-used-with-SQL-IN-operator-tp5724873p5724874.html
Sent from the Free Pascal - General mailing list archive at Nabble.com.
_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal


_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Luiz Americo Pereira Camara-3


2016-04-10 16:29 GMT-03:00 Tony Caduto <[hidden email]>:

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.


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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Reply | Threaded
Open this post in threaded view
|

Re: Sqldb - How to pass an array of values as a param to be used with SQL IN operator?

Snorkl e

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" <[hidden email]> wrote:


2016-04-10 16:29 GMT-03:00 Tony Caduto <[hidden email]>:

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.


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

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

_______________________________________________
fpc-pascal maillist  -  [hidden email]
http://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal