Using the in clause with parameter
lazarus / 16-Databases

----------------------------------------------------------------------
talorigomat · 2017-05-08 13:06

I've used integer as key columns in my tables.  In the sql statements
sent to the database I use parameters to pass the criteria back to the
tables to search for data.  So for example I'd have
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select *
from sometable where somefield = :param1.  This works fine.  However I
want change the statement to: Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select *
from sometable where somefield in (:param1)  so that I can allow users
to input multiple values (eg: 1, 2, 5, 7).  However when I try to pass
this back to the database I get the following error "EVariantError
with message: Invalid typecast".  How can I solve this?  I'm using
Firebird 2.5 as the database, but I presume the I'd get the same
message with another db.

----------------------------------------------------------------------
valdir.marcos · 2017-05-08 15:10

As an alternative, you may try:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Variable1
:= '1, 2, 5, 7';SQLQuery1.Close;SQLQuery1.SQL.Text := 'Select * From
SomeTable Where SomeField in (' + Variable1 + ')';SQLQuery1.Open;

----------------------------------------------------------------------
balazsszekely · 2017-05-08 15:14

Hi talorigomat,

Try this instead:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select *
from sometable where somefield in (:param1, :param2, :param3,
:param4)You can build the query/parmalist manually when needed. You
can pass as many params you wish. Concatenating the parameters as
string should also work.

----------------------------------------------------------------------
Leledumbo · 2017-05-09 06:22

AFAIR, bind parameters cannot be applied to in clause, at least with
MySQL, but perhaps Firebird does the same.

----------------------------------------------------------------------
mangakissa · 2017-05-09 07:01

I know it's 'a pain in the ass' when using params with like function.
Even in MySQL as Firebird as other databases. My a new feaute in SQLdb

I've created a function for myself for using in():
Quotefunction MyInFunction(const aMyvar : array of string; const
aQuotes : boolean = false) : string;
var slVars : TStrings;
    Value  : string;
begin
  result := '';
  slVars := TStringlist.create;
  try
    for Value in aMyVar do
    begin
      if aQuotes then
        slVars.add(quotedstr(Value))
      else
        slVars.add(Value)
    end;
    result := slVars.commatext;
  finally
    slVars.free;
  end;
end;

format('SELECT a,b,c FROM table WHERE d in
(%s)',[MyInFunction('1,2,3,4,5']));
format('SELECT a,b,c FROM table WHERE d in
(%s)',[MyInFunction('good,bad,ugly']),true);

----------------------------------------------------------------------
talorigomat · 2017-05-09 14:38

Thanks everyone

I have been experimenting with with GetMem's and valdir.marcos
suggestions and did get both to work.  Mangakissa, I've just seen your
function and will try that as well.

Thanks again.

----------------------------------------------------------------------
inforisorse · 2017-12-11 16:00

Some issue with SQLite. Since i use a datamodule, adopted a solution
like this:

in the datamodule class declaration:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i ...private
procedure SetMyQueryParams(params: string);publicproperty
MyQueryParams: string write SetMyQueryParams;... 
in the implementation:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TMyDataModule.SetMyQueryParams(param_list: string);const  SQLTemplate:
string = 'SELECT ... WHERE somefield IN (:param_list)';begin 
MyQuery.SQL.Text :=
ReplaceStr(SQLTemplate,':param_list',param_list);end; 
and finally in the program logic:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
MyDataModule.MyQuery.Active := false;  MyDataModule.MyQueryParams :=
'1,3,4,7,8';  MyDataModule.MyQuery.Active := true; 
Not much different from previously suggested solutions but should be
more readable and reusable.

----------------------------------------------------------------------
mangakissa · 2017-12-11 16:05

Yes, it coul be done, but it's not dynamic.

----------------------------------------------------------------------
KemBill · 2017-12-11 17:37

You can bind variables, if I remember well, not with named parameters
(like :something), but with  '?' (positional param) instead in your
sql query, so variables are queued in the order they appear in the
query, you map first param with first '?', and so on...

SELECT column FROM table WHERE value in (?, ?, ?, ?)