Pass NULL parameter to TFDConnection.ExecSQLScalar

Question

Is there a possibility to pass NULL value to some parameter of this FireDAC query?:

conn: TFDConnection;
fPar1, fPar2, fPar3: OleVariant;

cnt := conn.ExecSQLScalar(
    'SELECT COUNT(*) FROM my_table WHERE par1=:p1 AND par2=:p2 AND par3=:p3',
    [fPar1, fPar2, fPar3]
);

Is it possible without intermediate TFDQuery using TFDConnection object only?

Answer 1

Yes, you can do this, despite the fact that the parameters TFDConnection uses for ExecSQLScalar are not directly accessible from your calling code, but it may not produce the result you are expecting unless you modify your SQL - see below.

Presumably, you have had an error message like "[FireDAC] parameter type [fPar2 ] is unknown ..." if you set fPar2 to Null beforehand.

You can avoid that by using the override of ExecSQLScalar that allows you to specify the field types of the parameters in an open array following the parameter which lists the variants, as in e.g.

cnt := conn.ExecSQLScalar(
    'SELECT COUNT(*) FROM my_table WHERE par1=:p1 AND par2=:p2 AND par3=:p3',
    [fPar1, fPar2, fPar3],
    [ftString, ftString, ftString] //  or whatever
);

See

function TFDCustomConnection.ExecSQLScalar(const ASQL: String;
  const AParams: array of Variant; const ATypes: array of TFieldType): Variant;

in FireDAC.Comp.Client.Pas

BUT, on my data here, this does NOT produce the correct count value (using Seattle and SS2014) presumably because of Uwe Raabe's good point about par1 = Null versus par1 is Null. To get the correct answer, I had to modify the SQL as per Keith Miller's comment to include set ansi_nulls off before SELECT ...