Insert Returning problem

[PapayaYCocos]

Hi,

Using devart's Firebird driver 3.1.2, Delphi XE5 and Firebird 2.5.1, I would like to insert a new record into a database, record which splits on 2 tables sharing an autoinc primary key. To be able to do that, I tried to use the "insert into ... returning" syntax in a parametrized TSQLQuery in order to get the generated primary key value from the first table, to then use it for the insertion in the second table. Unfortunately, I cannot find the right way to write it. The behavior I see is a TDBXError with message "Dynamic SQL Error SQL error code = -804 Data type unknown" when calling ExecSQL.

This is the code which should make the first insert:

Code: Select all

var
  ID: Integer;
begin
  FQuery := TSQLQuery.Create(nil);
  with FQuery do begin
    SQLConnection := myConnection;
    SQL.Clear;
    SQL.Add('insert into Event (OperationID, SomeValue)');
    SQL.Add('values(:OperationID, :SomeValue) returning :EventID');
    ParamByName('OperationID').DataType := ftInteger;
    ParamByName('OperationID').ParamType := ptInput;
    ParamByName('SomeValue').DataType := ftInteger;
    ParamByName('SomeValue').ParamType := ptInput;
    ParamByName('EventID').DataType := ftInteger;
    ParamByName('EventID').ParamType := ptOutput;

    ParamByName('OperationID').AsInteger := 1;
    ParamByName('SomeValue').AsInteger := 2;
    ExecSQL;
    ID := ParamByName('EventID').Value;
  end;
end;

Here is a partial DDL for the Event table:

Code: Select all

CREATE TABLE "EVENT" 
(
  EVENTID                    INTEGER         NOT NULL,
  OPERATIONID                INTEGER         NOT NULL,
  SomeValue                  INTEGER         NOT NULL,
 CONSTRAINT PKEVENT655 PRIMARY KEY (EVENTID)
);
SET TERM ^^ ;
CREATE TRIGGER TRBIEVENT FOR "EVENT" ACTIVE BEFORE INSERT POSITION 0 AS
BEGIN
NEW.EventID = GEN_ID (IdEventID514, 1);
END ^^
SET TERM ; ^^

Did I make something wrong? And is there another, better, approach to solving this problem (without doing extra queries)?
NB: a simple insertion without the "returning" part is working fine.

Thanks in advance

[ZEuS]

To solve the problem, you should manually add an output parameter to TSQLQuery and provide the correct SQL statement. So, modify your code like the following:

Code: Select all

var
  ID: Integer;
begin
  FQuery := TSQLQuery.Create(nil);
  with FQuery do begin
    SQLConnection := con1;
    SQL.Clear;
    SQL.Add('insert into Event (OperationID, SomeValue)');
    SQL.Add('values(:OperationID, :SomeValue) returning EventID');
    ParamByName('OperationID').DataType := ftInteger;
    ParamByName('OperationID').ParamType := ptInput;
    ParamByName('SomeValue').DataType := ftInteger;
    ParamByName('SomeValue').ParamType := ptInput;
    with TParam(Params.Add) do begin
      Name := 'EventID';
      DataType := ftInteger;
      ParamType := ptOutput;
    end;
    ParamByName('OperationID').AsInteger := 1;
    ParamByName('SomeValue').AsInteger := 2;
    ExecSQL;
    ID := ParamByName('EventID').Value;
  end;
end;

[PapayaYCocos]

Thank you very much ZEuS, it worked as expected.