如何在参数化查询中显式插入null值？

Question

我正在使用 Delphi 7 和 Firebird 1.5。

我创建了一个在运行时可能包含某些 null 值的查询。我无法想出如何让 Firebird 接受需要保留为 null 的值的显示 null 值。目前，我正在构建 SQL，以便不包括那些为空的参数，但这很繁琐且容易出错。

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;

目前 在 DB.pas 文件中引发了一个 EDatabaseError "No value for parameter 'ThisValue'" 异常，因此我怀疑这是设计问题而不是 firebird 的问题。

我能将参数设置为 NULL 吗？如果可以，应该如何操作？

(编辑：未明确尝试 .Clear。我选择提到 IsNull 而略去了它。已添加声明和更多代码)

抱歉，还有一件事：表上没有“NOT NULL”约束。我认为不会导致这个问题，但是我想说一下。

完整的控制台应用程序，在我的端口显示了这个问题：

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.

- Мסž

---

@moz，你使用哪些组件来执行SQL语句？ - RRUZ

@moz，你不应该仅仅为了接受而接受答案。它解决了你的问题吗？如果没有，请不要接受：发布更新结果，获取更多答案。这样，社区就能获得知识，最终你也会解决问题。即使是你自己发布最终答案。 - Adriano Carneiro

@Adrian：除了我没有时间去追踪根本问题之外，所以我决定重写查询。因此，接受“这应该可以工作，而且很可能是已知的错误导致它停止工作”似乎是合理的。 - Мסž

@moz - 你看到我发布的答案了吗？如果未定义数据类型参数，无法使参数化查询起作用。 - Sertac Akyuz

不是连接组件或dbExpress中的错误，而是使用了错误的驱动程序信息。 - Sam

Answer 1

出现错误的原因是'dbx'不知道参数的数据类型。由于它从未被赋值，所以在执行时它的数据类型是ftUnknown，因此出现了错误。对于'ParamType'也是如此，但默认情况下假定为'ptInput'，因此没有问题。

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

由于参数已经是NULL，所以你绝对不需要清除参数。我们如何知道呢？IsNull返回true......

来自TParam.Clear方法：

使用Clear将NULL值分配给参数。

来自TParam.IsNull属性：

指示分配给参数的值是否为NULL（空）。

由于它完全无关紧要，所以你绝对不需要绑定参数。当"Bound"为false时，数据集将尝试从其数据源为参数提供默认值。但是你的数据集甚至没有链接到数据源。从文档中可以看到：

  

[...] 表示查询和存储过程的数据集使用Bound的值来确定是否为参数分配默认值。如果Bound是false，则表示查询的数据集会尝试从其DataSource属性指示的数据集中分配一个值。[...]

如果文档还不足够，请参考'sqlexpr.pas'中的TCustomSQLDataSet.SetParamsFromCursor代码。这是在dbx框架中引用参数的"Bound"的唯一位置。

- Sertac Akyuz

---

这实际上引导我找到了一个解决方法 - 当我设置DataType时，我得到了不同的错误，这让我找到了一个建议使用不同数据库驱动程序的页面。但这不是我可以在生产中使用的东西，所以我选择修改空参数的SQL。但还是谢谢你的建议，它帮了我很大的忙。 - Мסž

@moz - 我有点理解你不想接受正确答案的原因，可能是因为你的dbx驱动程序不适合所需的任务，这并没有帮助到你。但是，请不要接受错误的答案，无论它获得了多少票。 - Sertac Akyuz

这适用于字符串和DateTime类型，我现在正在使用它。但对于FireBird枚举类型，不好用。所以我接受了它。 - Мסž

对于Firebird枚举类型（即域），您需要修改约束检查以允许空值。创建域db_enum AS varchar(20) CHECK (value IS NULL or VALUE IN ('Firebird','MySQL','MSSQL'));ALTER DOMAIN mydomain DROP CONSTRAINT; ALTER DOMAIN mydomain ADD CONSTRAINT CHECK (<new constraint>)。 - Sam

Answer 2

使用TParam.Clear方法。

Query.ParamByName('ThisValue').Clear;

"使用 Clear 将 NULL 值分配给参数。" (来自文档)

- awmross

---

@RRUZ：我已经尝试过了。在我调用Clear之前，IsNull为true，之后更加真实。错误仍然发生。 - Мסž

+1，因为这是文档所说的。但你还需要将 Bound=True。 - Cosmin Prund

我使用 TParam.DataType := ...，TParam.Clear 和 TParam.Bound := True（按照这个顺序），它一直对我很有效。 - Remy Lebeau

-1 是没有意义的，因为将 NULL 赋值给已经赋值为 NULL 的参数是没有意义的。该参数的 'FNull' 在创建时被设置为 true，而 SQL 解析时仍然为 NULL，因为在此之后它没有被赋任何值。 - Sertac Akyuz

Sertac 是正确的，您不需要清除（因为已经是 NULL）或将 bound 设置为 True（因为在这种情况下它没有绑定到任何东西），但您需要设置 DataType。 - Sam

Answer 3

Sertac的答案是最正确的，但我也发现驱动程序的选择会有所不同。

为了让其他人受益，这里有一个改进的测试程序，演示了如何使用参数化查询在Firebird 1.5中插入nulls。

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.

- Sam

Answer 4

在TConnection Options上有一个名为 HandlingStringType/Convert empty strings to null 的属性。保持其为 true 并假定 Query.ParamByName('ThisValue').AsString:=''；您可以在其中访问它。

TConnection.FetchOptions.FormatOptions.StrsEmpty2Null:=True

- user6695319

Answer 5

你确定参数是通过设置 SQL 的文本而创建的吗？

尝试一下。

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

不过，为什么不把SQL文本制作成：

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

如果你知道这个值将是空的...

- Despatcher

---

因为如果我要为每个参数变化SQL，那将是一段相当丑陋的代码。这个问题实际上发生在具有约20个参数的插入操作中（尽管其中只有约10个可以为空）。同样，如果paramcount=0，则查询永远不会起作用，而不仅仅是在参数为空时失败。 - Мסž