Using the in clause with parameter

[talorigomat]

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][+][-]

1. select * from sometable where somefield = :param1

.  This works fine.  However I want change the statement to:

Code: Pascal  [Select][+][-]

1. 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]

As an alternative, you may try:

Code: Pascal  [Select][+][-]

1. Variable1 := '1, 2, 5, 7';
2. SQLQuery1.Close;
3. SQLQuery1.SQL.Text := 'Select * From SomeTable Where SomeField in (' + Variable1 + ')';
4. SQLQuery1.Open;

[balazsszekely]

Hi talorigomat,

Try this instead:

Code: Pascal  [Select][+][-]

1. 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]

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

[mangakissa]

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():

function 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]

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]

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

in the datamodule class declaration:

Code: Pascal  [Select][+][-]

1. .
2. .
3. .
4. private
5. procedure SetMyQueryParams(params: string);
6. public
7. property MyQueryParams: string write SetMyQueryParams;
8. .
9. .
10. .
11.  

in the implementation:

Code: Pascal  [Select][+][-]

1. procedure TMyDataModule.SetMyQueryParams(param_list: string);
2. const
3.   SQLTemplate: string = 'SELECT ... WHERE somefield IN (:param_list)';
4. begin
5.   MyQuery.SQL.Text := ReplaceStr(SQLTemplate,':param_list',param_list);
6. end;
7.  

and finally in the program logic:

Code: Pascal  [Select][+][-]

1.   MyDataModule.MyQuery.Active := false;
2.   MyDataModule.MyQueryParams := '1,3,4,7,8';
3.   MyDataModule.MyQuery.Active := true;
4.  

Not much different from previously suggested solutions but should be more readable and reusable.

[mangakissa]

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

[KemBill]

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 (?, ?, ?, ?)