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;

enter image description here

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?

Do you create parameters manually or use "Query.ResourceOptions.ParamCreate := True;" ? — valex
– valex, Commented Aug 27, 2014 at 8:44
@valex, when you asked me that question I had my parameters created automatically, Vitor suggested I do it manually so I did, no change. I've updated my question with full code. — Peter
– Peter, Commented Aug 27, 2014 at 17:50
I can't check this right now but I guess the problem here is that :A_INDEX is placed in the SELECT list. Try to change you query to for example INSERT INTO RELATIONS (C_ID, M_ID, A_ID, A_INDEX) SELECT c.C_ID, 0, a.A_ID, 0 FROM CATEGORIES c, ACTIONS a WHERE c_id=:A_INDEX AND c.NAME = :CATEGORY AND a.NAME = :ACTION and I think it will run fine without any errors. — valex
– valex, Commented Aug 28, 2014 at 12:30
@valex, I don't think you understand my question. I need to change the value of A_INDEX column dynamically, I can't have it be 0. Also c_id = :A_INDEX is wrong. c_ID is foreign key in table Relations and is tied to a primary key c_ID in table Categories. My question is, why can I set a value for a column in the Select statement but I apparently can't put a parameter in there instead? — Peter
– Peter, Commented Aug 28, 2014 at 13:07
I understand your question I think it's a bug and :A_INDEX isn't processed because it is in the SELECT list. So I think you should try to make this statement where all parameters in the WHERE part and I think it will run without error. In this case the same set of parameters and in the same order will be used (:A_INDEX,:CATEGORY,:ACTION) so you will find the root of this issue. — valex
– valex, Commented Aug 29, 2014 at 6:34

Victoria · Accepted Answer · 2017-06-28 23:59:02Z

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.

oPsDCadarn · Accepted Answer · 2014-08-27 13:17:30Z

0

Probably you set the SQL in the DFM (directly in the component), you can see the created params in the Params property of the component. Delete all the params or add the A_INDEX (integer, input) manually.

I strongly recommend that you create your params in the source code and let the property ParamCreate as false. You can control it easily if you know that you need to create manually.

To create params manually use the method CreateParam (in the TParams class).

answered Aug 27, 2014 at 13:17

oPsDCadarn

1063 bronze badges

5 Comments

Peter Over a year ago

All my queries are local variables, the only thing that is a design component is the TFDConnection. I've took up your recommendation and I've created the params manually and the result is the same. I've updated my answer with complete code I use for this query.

oPsDCadarn Over a year ago

The error you're getting is basically saying that the DB can't execute this: "where column = string" I sugest that you change your code to the code below and feedback us: Query.ParamByName('CATEGORY').Value := QuotedStr('Foo'); Query.ParamByName('ACTION').Value := QuotedStr('Foo');

Peter Over a year ago

I thank you for trying to figure out what's wrong with my code but using QuotedStr is wrong here. Params do not expect you to wrap strings in quotes, you can verify this in the documentation.

oPsDCadarn Over a year ago

Yep, i know and it's ulgy too, but i think you should try it.

Peter Over a year ago

I tried it when you first suggested it, it didn't help but I didn't expect it to.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related