Exception when trying to execute a query from pascal

Question

The following function takes the cuits (the cuit is like social security number) from a grid and inserts them into a temporary table. I'm using Delphi XE7 and Firebird 2.5.

function TfImportFileARBARetenPercep.fxListClientFromGrid(
      pboClient: Boolean): WideString;
var
   wsAux : WideString;
   stTable, stCuit: String;
   qCliProv, qCuitsExcl, qCuit : TFXQuery;
   niRow : Integer;
begin
  wsAux := '';

  qCuitsExcl.SQL.Text := 'Create global temporary table TempExcl (cuitExcl varchar(13)) on commit delete rows';
  qCuitsExcl.ExecSQL;

  if pboClient then
  begin
    stTable := 'Client'
  end
  else
  begin
    stTable := 'Prov';

  end;
  for niRow := 1 to gDetails.RowCount - 1 do
    if Trim(gDetails.Cells[3,niRow]) <> '' then
    Begin
      stCuit := QuotedStr(Trim(gDetails.Cells[3,niRow]));
      qCuit.SQL.Text := 'Insert into TempExcl(:cuitExcl)';
      qCuit.ParamByName('cuitExcl').AsString := stCuit;
      qCuit.ExecSQL;//←←←←←←←←←←←←←←←←this line throws the exception
    End;

  qCuitsExcl.SQL.Text :='';
  qCuitsExcl.SQL.Text := 'commit;';//to commit previous inserts
  qCuitsExcl.SQL.Add('drop table TempExcl;');//in case the table is not deleted when the connection is closed
  qCuitsExcl.SQL.Add('commit;');//commit previous drop
  qCuitsExcl.ExecSQL;

//the rest of the code only returns the list of cuits that were loaded 
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓

  try
    qCliProv.SQL.Text :=
      ' Select Code' +
      '   From ' + stTable +
      '  Where Active = 1 ';

    if gDetails.RowCount  > 0 then
      begin
        qCliProv.SQL.Add('And Cuit Not In (Select cuitExcl from TempExcl)');
      end;

    qCliProv.Open;

    wsAux := '';

    while not qCliProv.Eof do
    begin
      wsAux := wsAux + iif((wsAux = ''), '', ',') + qCliProv.FieldByName('Code').AsString;
      qCliProv.Next;
    end;

    Result := wsAux;
  finally
    FreeAndNil(qCliProv);
    FreeAndNil(qCuit);
    FreeAndNil(qCuitsExcluidos);
  end;
end;

When trying to execute the line qCuit.ExecSQL; the following exception is thrown:

Project ERP.exe raised exception class EIBNativeException with message '[FireDAC][Phys][FB] Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 27 ?'.

I don't know why it throws this exception

Answer 1

The problem is that this is wrong:

qCuit.SQL.Text := 'Insert into TempExcl(:cuitExcl)';

This will result in a generated query that is:

Insert into TempExcl(?)

Which is a syntax error (the "Token unknown"), because Firebird doesn't expect a question mark (parameter placeholder) in this position, it expects an identifier (column name).

A valid query would be either:

Insert into TempExcl (cuitExcl) values (?)

or:

Insert into TempExcl values (?)

The first form is preferred, as you explicitly specify the columns, though in this case not really necessary as the table has one column anyway.

In other words, you need to use:

qCuit.SQL.Text := 'Insert into TempExcl (cuitExcl) values (:cuitExcl)';

As an aside, I'm not familiar with Delphi or how and when queries are prepared, but it might be better to move this statement out of the loop, so it is prepared only once (in case Delphi prepares the statement again any time the qCuit.SQL.Text is assigned a value).

The comment "// in case the table is not deleted when the connection is closed" seems to indicate a lack of understanding of Global Temporary Tables. They are intended to be permanent objects, available for re-use by your application. That is on commit delete rows makes the content available only to your current transaction, while on commit preserve rows will make the content available for the remainder of your connection (and only your connection), deleting the data when the connection is closed. Creating and dropping a GTT in something that is executed more than once is an indication something is wrong in your design.