Hi

I want use IN condition in parameterized sql query. Here is my sql query:

 Query2.SQL.Text := 'SELECT * From Subject Where Parent IN :IdList ';
 Query2.Prepared := True;

 Query2.Params.ParamByName('IdList').AsString := '29,50,70,78,95,150,260';
 Query2.Open;

But I got following error:

DBISAM Engine Error # 11949 SQL parsing error - Expected NULL, Boolean, SmallInt, Word, AutoInc, Integer, LargeInt, Currency, Float, or BCD
expression but instead found ? in SELECT SQL statement at line 1, column 41.

Is it possible to use IN condition in parameterized query. If not is it a workaround?

Regards

mamouri wrote:
....
> Is it possible to use IN condition in parameterized query.

No. What type of parameter would you use?

> If not is it a workaround?

Yes: Format(). I use parameters only if really needed, means for BLOBs.
Everything else is done with Format(). And never ever forget to use
QuotedStr instead of "... "%s""!

Ralf

mamouri


As Ralf says no, essentially you can only replace single values with a parameter not a list. However, with a bit of creativity you can get a similar effect.

Alter your list to ',29,50,70,78,95,150,260,' (note the leading and trailing comma)

Alter the SQL to something like

SELECT _fkCalls,
POS(','+CAST(_fkCalls AS VARCHAR(100))+',' IN ',34242,3555,1,59,87,'),
','+CAST(_fkCalls AS VARCHAR(100))+','
from perfcalls

WHERE
POS(','+CAST(_fkCalls AS VARCHAR(100))+',' IN ',34242,3555,1,59,87,') > 0

Obviously replacing the number strings with the parameter - only tested in DBSys with the number strings NOT parameters

Roy Lambert

mamouri


Forgot to say - you'll need to check my code for speed Vs unpreparing the query and simply altering the SQL

Roy Lambert