How do I explicitly insert nulls into a parametized query?

Question

I'm using Delphi 7 and Firebird 1.5.

I have a query that I create at runtime where some of the values might be null. I can't work out how to get Firebird to accept explicit nulls for values that I need to leave as null. At this stage I'm building the SQL so that I don't include parameters that are null but this is tedious and error-prone.

var
  Qry: TSQLQuery;
begin
  SetConnection(Query); // sets the TSQLConnection property to a live database connection
  Query.SQL.Text := 'INSERT INTO SomeTable (ThisColumn) VALUES (:ThisValue)';
  Query.ParamByName('ThisValue').IsNull := true; // read only, true by default
  Query.ParamByName('ThisValue').Clear; // does not fix the problem
  Query.ParamByName('ThisValue').IsNull = true; // still true
  Query.ParamByName('ThisValue').Bound := true; // does not fix the problem
  Query.ExecSQL;

Currently an EDatabaseError "No value for parameter 'ThisValue'"' is raised in DB.pas so I suspect this is by design rather than a firebird problem.

Can I set parameters to NULL? If so, how?

(edit: sorry for not being explicit about trying .Clear before. I left it out in favour of mentioning IsNull. Have added declaration and more code)

Sorry, one more thing: there is no "NOT NULL" constraint on the table. I don't think it's getting that far, but thought I should say.

Complete console app that displays the problem at my end:

program InsertNull;

{$APPTYPE CONSOLE}

uses
  DB,
  SQLExpr,
  Variants,
  SysUtils;

var
  SQLConnection1: TSQLConnection;
  Query: TSQLQuery;
begin
  SQLConnection1 := TSQLConnection.Create(nil);

  with SQLConnection1 do
  begin
    Name := 'SQLConnection1';
    DriverName := 'Interbase';
    GetDriverFunc := 'getSQLDriverINTERBASE';
    LibraryName := 'dbexpint.dll';
    LoginPrompt := False;
    Params.clear;
    Params.Add('Database=D:\Database\ZMDDEV12\clinplus');
    Params.Add('RoleName=RoleName');

    //REDACTED Params.Add('User_Name=');
    //REDACTED Params.Add('Password=');

    Params.Add('ServerCharSet=');
    Params.Add('SQLDialect=1');
    Params.Add('BlobSize=-1');
    Params.Add('CommitRetain=False');
    Params.Add('WaitOnLocks=True');
    Params.Add('ErrorResourceFile=');
    Params.Add('LocaleCode=0000');
    Params.Add('Interbase TransIsolation=ReadCommited');
    Params.Add('Trim Char=False');
    VendorLib := 'gds32.dll';
    Connected := True;
  end;
  SQLConnection1.Connected;
  Query := TSQLQuery.Create(nil);
  Query.SQLConnection := SQLConnection1;
  Query.Sql.Text := 'INSERT INTO crs_edocument (EDOC_ID, LINKAGE_TYPE) VALUES (999327, :ThisValue)';
  //Query.ParamByName('ThisValue').IsNull := true; // read only, true by default
//  Query.ParamByName('ThisValue').Value := NULL;
  Query.ParamByName('ThisValue').clear; // does not fix the problem
  Query.ParamByName('ThisValue').Bound := True; // does not fix the problem
//  Query.ParamByName('ThisValue').IsNull; // still true
  Query.ExecSQL;
end.

Answer 1

The reason of the error is 'dbx' does not know the data type of the parameter. Since it is never assigned a value, it's data type is ftUnknown in execute time, hence the error. Same for 'ParamType', but 'ptInput' is assumed by default, so no problem with that.

  Query.ParamByName('ThisValue').DataType := ftString;

You definitely don't need to Clear the parameter because it is already NULL. How do we know it? IsNull is returning true...

From TParam.Clear Method:

Use Clear to assign a NULL value to a parameter.

From TParam.IsNull Property:

Indicates whether the value assigned to the parameter is NULL (blank).

You definitely don't need to Bound the parameter as it is completely irrelevant. When 'Bound' is false, the dataset will attempt to provide a default value from its datasource for the parameter. But your dataset is not even linked to a data source. From the documentation:

[...] Datasets that represent queries and stored procedures use the value of Bound to determine whether to assign a default value for the parameter. If Bound is false, datasets that represent queries attempt to assign a value from the dataset indicated by their DataSource property. [...]

If the documentation is not enough, refer to the code in TCustomSQLDataSet.SetParamsFromCursor in 'sqlexpr.pas'. It is the only place where the 'Bound' of a parameter is referred in dbx framework.