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.

Answer 2

Use TParam.Clear

Query.ParamByName('ThisValue').Clear;

"Use Clear to assign a NULL value to a parameter." (from the Docs)

Answer 3

Have some property on TConnection Options named HandlingStringType/Convert empty strings to null. Keep it true and assume Query.ParamByName('ThisValue').AsString:=''; You can access it in

TConnection.FetchOptions.FormatOptions.StrsEmpty2Null:=True

Answer 4

Sertac's answer is most correct, but I also found the choice of driver makes a difference.

For the benefit of others, here's an improved test program that demonstrates how you could insert nulls with a parameterised query with Firebird 1.5.

program InsertNull;

{$APPTYPE CONSOLE}

uses
  DB,
  SQLExpr,
  Variants,
  SysUtils;

var
  SQLConnection1: TSQLConnection;
  Query: TSQLQuery;
  A, B, C: variant;
begin
  SQLConnection1 := TSQLConnection.Create(nil);
  Query := TSQLQuery.Create(nil);

  try
    try
      with SQLConnection1 do
      begin
        Name := 'SQLConnection1';
        DriverName := 'InterXpress for Firebird';
        LibraryName := 'dbxup_fb.dll';
        VendorLib := 'fbclient.dll';
        GetDriverFunc := 'getSQLDriverFB';
        //DriverName := 'Interbase';
        //GetDriverFunc := 'getSQLDriverINTERBASE';
        //LibraryName := 'dbexpint.dll';
        LoginPrompt := False;
        Params.clear;
        Params.Add('Database=127.0.0.1:D:\Database\testdb');
        Params.Add('RoleName=RoleName');
        Params.Add('User_Name=SYSDBA');
        Params.Add('Password=XXXXXXXXXXXX');
        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;

      Query.SQLConnection := SQLConnection1;
      Query.SQL.Clear;
      Query.Params.Clear;
      // FYI
      // A is Firebird Varchar
      // B is Firebird Integer
      // C is Firebird Date
      Query.Sql.Add('INSERT INTO tableX (A, B, C) VALUES (:A, :B, :C)');
      Query.ParamByName('A').DataType := ftString;
      Query.ParamByName('B').DataType := ftInteger;
      Query.ParamByName('C').DataType := ftDateTime;

      A := Null;
      B := Null;
      C := Null;

      Query.ParamByName('A').AsString := A;
      Query.ParamByName('B').AsInteger := B;
      Query.ParamByName('C').AsDateTime := C;

      Query.ExecSQL;
      writeln('done');
      readln;
    except
      on E: Exception do
      begin
        writeln(E.Message);
        readln;
      end;
    end;
  finally
    Query.Free;
    SQLConnection1.Free;
  end;
end.

Answer 5

Are you sure the params have been created by just setting the text of the SQL?

try

if Query.Params.count <> 0 then
// set params
.
.

Anyway why not make the SQL text:

'INSERT INTO crs_edocument (EDOC_ID, LINKAGE_TYPE) VALUES (999327, NULL)';

if you know the value is going to be null...