SQL conversion error / how to insert parameter into select statement?
I am running an IB interface with a FireDAC interface.
The following dynamic query is executed:
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 am adding a column to RELATIONS A_INDEX
(Integer). For this column, I want to provide a parameter to 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 does not work, however. For some reason, when I execute the query, it complains about a conversion error for the parameter CATEGORY
.
This is the complete code for this request 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 SQL, Databases and FireDAC, so I really don't understand why it will let me enter a direct value into a select statement, but the parameter does not.
How else can I dynamically insert a parameter into a column A_INDEX
using the first query?
source to share
You cannot parameterize a 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
In particular, the SELECT expression :
SELECT c.C_ID, 0, a.A_ID, :A_INDEX
The only way to overcome this situation is with preprocessor macros, but they do not work like real query parameters, as they modify the SQL command, so the 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 the value of the A_INDEX field should be an auto-incrementing value, create it like this. Or, if the value is from another table, join that table in the query. Macros do not work as real command parameters and can be ineffective when inserting many records waiting for a prepared command.
source to share
Perhaps you set the SQL in DFM (directly in the component), you can see the generated parameters in the Params property of the component. Remove all options or add A_INDEX (integer, enter) manually.
I highly recommend that you create your parameters in the source code and leave the ParamCreate property as false. You can easily manage it if you know what you need to create manually.
To create parameters manually, use the CreateParam method (in the TParams class).
source to share