* * *

Author Topic: Using the in clause with parameter  (Read 969 times)

talorigomat

  • Jr. Member
  • **
  • Posts: 85
Using the in clause with parameter
« on: May 08, 2017, 03:06:49 pm »
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.
Lazarus 1.6.4, Windows 10

valdir.marcos

  • Full Member
  • ***
  • Posts: 245
Re: Using the in clause with parameter
« Reply #1 on: May 08, 2017, 05:10:24 pm »
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;
« Last Edit: May 08, 2017, 05:24:04 pm by valdir.marcos »

GetMem

  • Hero Member
  • *****
  • Posts: 2248
Re: Using the in clause with parameter
« Reply #2 on: May 08, 2017, 05:14:26 pm »
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

  • Hero Member
  • *****
  • Posts: 7649
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Using the in clause with parameter
« Reply #3 on: May 09, 2017, 08:22:12 am »
AFAIR, bind parameters cannot be applied to in clause, at least with MySQL, but perhaps Firebird does the same.

mangakissa

  • Hero Member
  • *****
  • Posts: 707
Re: Using the in clause with parameter
« Reply #4 on: May 09, 2017, 09:01:00 am »
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():
Quote
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);

Lazarus 1.64 (32b) / FPC 3.0
Windows 10

talorigomat

  • Jr. Member
  • **
  • Posts: 85
Re: Using the in clause with parameter
« Reply #5 on: May 09, 2017, 04:38:20 pm »
Thanks everyone :D

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.
Lazarus 1.6.4, Windows 10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus