SQL Prepared Parameter FireDac using "in"-clause

Question

I am struggling using the in operator with FireDac.

I am using an oracle-database and cannot get a simple query working.

FDQuery1.SQL.Text := 'SELECT * FROM Customers WHERE Gender in (:Genders)';

FDQuery1.ParamByName('Genders').AsString:= 'M';

It works with one parameter but thats obviously not the purpose of the in operator.

Multiple values do not work however:

FDQuery1.ParamByName('Genders').AsString:= 'M, F';
FDQuery1.ParamByName('Genders').AsString := '''M'', ''W''';

Could it be that the in operator is not supported? And if so what would it be replaced with?

Answer 1

I just found this thread on the Embarcadero forums where User Serge Girard posted this solution:

use macro instead and perhaps in a little different way

SELECT * FROM `table` !mysel
and
Query.macrobyname('mysel').asraw:='WHERE Field in (1,2,3)';

or

SELECT * FROM table WHERE !mysel

no selection Query.MacroByName('mysel').asRaw:'1=1';
a selection Query.MacroByName('mysel').asRaw:'Field in (1,2,3)';

Answer 2

If you're asking about MS Sql Server or a back-end with similar support for "IN", then you can't specify a series of values as a single parameter to an IN clause of a query. However, a general (not FireDAC-specific) way to do this without using ad-hoc Sql (which carries the risk of Sql-Injection unless you are careful) is

• Use a series of Inserts (or .InsertRecords on a TDataSet) to insert the "IN" values in to a single-column temporary table. The advantage of this is that you're not restricted by your parameterization to a specific number of values in the IN list.

• Then do a "Select * from MyTable where SomeColumn in (Select ColumnValue from #MyTempTable)

It's a bit of a performance compared with just specifying the IN list values in ad hoc Sql, of course, but not much as can be seen from the example below. Using TDFQuerys, the creation of the INList table requires two steps, one to define it and the other to do a Select * from it. Using TAdoQuerys instead, these two steps can be combined.

TForm1 = class(TForm)
  FDQuery1: TFDQuery;
  qInlist: TFDQuery;
  [etc]

 const
  scCreateINListTable =
  'if object_id(''tempdb.dbo.#countrycodes'', ''U'') is not null drop table #countrycodes'#13#10
   + 'create table #countrycodes (ACode char(2))';

   scOpenINList = 'select * from #countrycodes';

   scGetClients = 'select * from client where country in (select ACode from #countrycodes)';

procedure TForm1.GetClients;
begin
  qINList.SQL.Text := scCreateINListTable;
  qINList.ExecSQL;

  qINList.SQL.Text := scOpenINList;
  qINList.Open;

  qINList.InsertRecord(['US']);
  qINList.InsertRecord(['GB']);
  qINList.InsertRecord(['IT']);

  FDQuery1.SQL.Text := scGetClients;
  FDQuery1.Open;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  GetClients;
end;

Notice that this implementation doesn't require any parameterized or ad hoc Sql queries at all.