SQL conversion error / how to insert parameter into select statement?

Question

I'm running an IB database interfacing through FireDAC.

The following dynamic query works :

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

I add a column to RELATIONS A_INDEX(Integer). For this column I want to supply a parameter so I do this:

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

This however does not work. For some reason when I execute the query it complains that there's a conversion error for CATEGORY param.

This is the full code for this query operation:

  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;

I'm still learning about SQL, databases & FireDAC so I really don't understand why it will allow me to input a direct value into the select statement but a param is a no go.

How else could I dynamically insert a parameter into the A_INDEX column using the first query?

Answer 1

You cannot parametrize query like this:

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

Specifically its SELECT expression:

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

The only way you can overcome this situation are preprocessor macros, but they don't work as real query parameters as they modify the SQL command, so DBMS must prepare the command again.

For example:

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;

But if that A_INDEX field value should be an autoincremented value, create it like so. Or if it's a value from another table, join that table in a query. Macros do not work as real command parameters and might be inefficient when inserting many records expecting prepared command.