How to use RETURNING in TUniquery

[gn7soft]

Hello,

I am using TUniQuery (UniDac Professional, no source code) for Firebird.
In Firebird database, there are a serial type field (for autoincrement)
and Trigger (this trigger generates serial number to upper autoincrement field, when an insert is performed.)

I tried to use the "insert into ... returning" syntax in a parametrized TUniQuery in order to get the generated key value, but I cannot get proper result. Returned value(ID) has null.

Please help me! (Delphi7, Unidac6.0.2, Firebird 2.1)

Code: Select all

var ID:integer;

Try
   If DM.UniConn.InTransaction=False Then DM.UniConn.StartTransaction;

   with Uniquery do
   begin
    close;
    sql.clear;
    sql.add('Insert Into CONSULTANT (Name,JIKWI,PART) values(:Name,:JIKWI,:PART) Returning ID');
    ParambyName('Name').DataType:=ftString;
    ParambyName('Name').ParamType:=ptInput;
    ParambyName('JIKWI').DataType:=ftString;
    ParambyName('JIKWI').ParamType:=ptInput;
    ParambyName('PART').DataType:=ftString;;
    ParambyName('PART').ParamType:=ptInput;
 
    with TParam(Params.Add) do begin
      Name:='ID';
      DataType:=ftInteger;
      ParamType:=ptOutput;
    end; 

    ParambyName('Name').AsString:=trim(Ed_Name.Text);
    ParambyName('JIKWI').AsString:=trim(Ed_JikWi.text);
    ParambyName('PART').AsString:=trim(Ed_Part.text);

    Execsql;
   
    ID := ParamByName('ID').Value; 
   end;
   DM.UniConn.CommitRetaining;
Except
   DM.UniConn.RollbackRetaining
End;   

[ViktorV]

For the fields, that are specified after RETURNING, there are automatically generated output parameters with the RET_ prefix. This is described in the documentation: http://www.devart.com/ibdac/docs/index. ... ataset.htm
To solve the problem, in you sample:
1) don't create an out-parameter "ID";
2) use

Code: Select all

ID := ParamByName('RET_ID').Value;

instead of

Code: Select all

ID := ParamByName('ID').Value;

.

[gn7soft]

This works perfectly. Thanks.

Regards
Peace

[ViktorV]

Feel free to contact us if you have any further questions about UniDAC.