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?

Snarl

Use QuotedStr function – Tendentious

AFAIK, each parameter can handle a single value. You can try use multiple parameters: ... WHERE Gender IN (:p1,:p2); FDQuery1.Parameters.ParamValues['p1;p2'] := VarArrayOf(['M','W']); – Directional

@Directional oops accidently forgot it. That would work however one would have to adjust the query statement and not only the parameters. – Snarl

Well, what you want can't be done with parameters b/c the IN parameters/expressions are comma separated and that is part of the SQL statement/syntax itself. Devart's UniQuery for example has a concept of "macros" instead of parameters for such case, but I guess the command text is simply being dynamically populatted internally. – Directional

Thanks for the link. As FireDac also supports macros i'll have a look at them. – Snarl

Answer 1

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.

Darondarooge

Answer 2

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)';

Snarl

Anyway firedac is powerful, you can combine different style of macros: 'select * from table !mysel where &col_name = &value' – Crime

Answer 3

If you still want to use one parameter for a list, this works for SQL Server:

SELECT * FROM Customers WHERE charindex(Gender, :Genders) > 0

The value for the parameter should be any number of values separated by a character you know is not in the field (a pipe for example): |

If you pass 'M|F' it will bring back all M's and F's.

You can also do this in Oracle by using the instr function instead of charindex.

I hope this helps.

Jeanejeanelle