SQL转换错误/如何将参数插入select语句？

Question

我正在运行通过 FireDAC 连接的 IB 数据库。

以下动态查询有效:

INSERT INTO RELATIONS (C_ID, M_ID, A_ID)
SELECT c.C_ID, 0, a.A_ID
FROM CATEGORIES c, ACTIONS a
WHERE c.NAME = :CATEGORY AND a.NAME = :ACTION

我向 RELATIONS A_INDEX 添加一列(整数)。对于本专栏，我想提供一个参数，因此我这样做:

INSERT INTO RELATIONS (C_ID, M_ID, A_ID, A_INDEX)
SELECT c.C_ID, 0, a.A_ID, :A_INDEX
FROM CATEGORIES c, ACTIONS a
WHERE c.NAME = :CATEGORY AND a.NAME = :ACTION

但这不起作用。由于某种原因，当我执行查询时，它提示 CATEGORY 参数存在转换错误。

这是此查询操作的完整代码:

  procedure Test;
  var
    Query: TFDQuery;
  begin
    Query := TFDQuery.Create(nil);
    try
      Query.Connection := DBDataModule.dbMain;
      Query.ResourceOptions.ParamCreate := False;

      Query.SQL.BeginUpdate;

      Query.SQL.Add('INSERT INTO RELATIONS (C_ID, M_ID, A_ID, A_INDEX)');
      Query.SQL.Add('SELECT c.C_ID, 0, a.A_ID,:A_INDEX');
      Query.SQL.Add('FROM CATEGORIES c, ACTIONS a');
      Query.SQL.Add('WHERE c.NAME = :CATEGORY AND a.NAME = :ACTION');

      Query.SQL.EndUpdate;

      Query.Params.CreateParam(TFieldType.ftInteger, 'A_INDEX', ptInput);
      Query.Params.CreateParam(TFieldType.ftFixedWideChar, 'CATEGORY', ptInput);
      Query.Params.CreateParam(TFieldType.ftFixedWideChar, 'ACTION', ptInput);
      Query.ParamByName('CATEGORY').Size := 255;
      Query.ParamByName('ACTION').Size := 255;

      Query.Prepare;

      Query.ParamByName('A_INDEX').Value := 0;
      Query.ParamByName('CATEGORY').Value := 'Foo';
      Query.ParamByName('ACTION').Value := 'Foo';

      Query.ExecSQL; // <-- Exception
    finally
      Query.Free;
    end;    
  end;

我仍在学习 SQL、数据库和 FireDAC，所以我真的不明白为什么它允许我在 select 语句中输入直接值，但不能使用参数。

我还能如何使用第一个查询将参数动态插入到 A_INDEX 列中？

Answer 1

您不能像这样参数化查询:

INSERT INTO RELATIONS (C_ID, M_ID, A_ID, A_INDEX)
SELECT c.C_ID, 0, a.A_ID, :A_INDEX
FROM CATEGORIES c, ACTIONS a
WHERE c.NAME = :CATEGORY AND a.NAME = :ACTION

特别是它的SELECT表达式:

SELECT c.C_ID, 0, a.A_ID, :A_INDEX

克服这种情况的唯一方法是预处理器宏，但它们在修改 SQL 命令时不能作为真正的查询参数，因此 DBMS 必须重新准备命令。

例如:

procedure Test;
var
  Query: TFDQuery;
begin
  Query := TFDQuery.Create(nil);
  try
    Query.Connection := DBDataModule.dbMain;

    Query.SQL.Add('INSERT INTO RELATIONS (C_ID, M_ID, A_ID, A_INDEX)');
    Query.SQL.Add('SELECT c.C_ID, 0, a.A_ID, &A_INDEX'); // ← & defines macro
    Query.SQL.Add('FROM CATEGORIES c, ACTIONS a');
    Query.SQL.Add('WHERE c.NAME = :CATEGORY AND a.NAME = :ACTION');

    Query.ParamByName('CATEGORY').Value := 'Foo';
    Query.ParamByName('ACTION').Value := 'Foo';
    Query.MacroByName('A_INDEX').AsIdentifier := '0';

    Query.ExecSQL;
  finally
    Query.Free;
  end;    
end;

但是，如果该 A_INDEX 字段值应该是自动增量值，请像这样创建它。或者，如果它是来自另一个表的值，请在查询中联接该表。宏不能作为真正的命令参数，并且在插入许多需要准备命令的记录时可能效率低下。