How to use TIBCQuery with parameters and "in" keyword

Discussion of open issues, suggestions and bugs regarding IBDAC (InterBase Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
TheFastCoder
Posts: 10
Joined: Mon 18 Jun 2018 06:40

How to use TIBCQuery with parameters and "in" keyword

Post by TheFastCoder » Fri 07 May 2021 14:05

I have a TIBCQuery. I'm using this SQL statement:

Code: Select all

MyQuery.SQL.Text := 'select * from MyTable where MyField in (''A'', ''B'', ''C'')';
This gives me 7 records.

How to use this statement with parameters? I tried this:

Code: Select all

MyQuery.SQL.Text := 'select * from MyTable where MyField in (:MyField)';

MyQuery.ParamByName('MyField').ValueCount := 3;
MyQuery.ParamByName('MyField').Values[0].AsString := 'A';
MyQuery.ParamByName('MyField').Values[1].AsString := 'B';
MyQuery.ParamByName('MyField').Values[2].AsString := 'C';
It's executed, but the resultset is empty. It must return 7 records as the statement above. Is Values[x] the wrong way?

ViktorV
Devart Team
Posts: 3168
Joined: Wed 30 Jul 2014 07:16

Re: How to use TIBCQuery with parameters and "in" keyword

Post by ViktorV » Fri 14 May 2021 13:34

Hey Coder,

You can solve this task by using the solution which suits you the most:
1. Use the code below:

Code: Select all

  MyQuery.SQL.Text := 'SELECT * FROM MyTable WHERE MyField IN (:parameter1, :parameter2, :parameter3)'; 
  MyQuery.ParamByName('parameter1').AsString := 'A'; 
  MyQuery.ParamByName('parameter2').AsString := 'B'; 
  MyQuery.ParamByName('parameter3').AsString := 'C'; 
  MyQuery.Open; 
2. Use the following Macros:

Code: Select all

var 
  InConditione: string; 
... 
  MyQuery.SQL.Text := 'SELECT * FROM MyTable WHERE MyField IN (&InConditione)'; 
  InConditione := '''A'', ''B'', ''C'''; 
  MyQuery.MacroByName('InConditione').Value := InConditione; 
  MyQuery.Open; 
You can find more info about Macros at:
http://www.devart.com/ibdac/docs/work_macros.htm

Regards,
Viktor

Post Reply