RETURNING Unified Support (FireDAC)

Go Up to Preprocessing Command Text (FireDAC)

Description

FireDAC offers the {INTO } escape sequence to simplify and unify RETURNING handling for Firebird, Oracle, and PostgreSQL:

FDQuery1.SQL.Text := 'insert into MyTab (f2, f3) values (:f2, :f3) returning f1 {into :f1}';
FDQuery1.Params[0].AsString := 'qwe';
FDQuery1.Params[1].AsInteger := 100;
FDQuery1.ExecSQL;
FDQuery1.Params[2].Value; // Value of a first parameter, listed in INTO

The parameters inside of {INTO } are defined as ptInputOutput. FireDAC uses RETURNING {INTO} as part of the INSERT/UPDATE SQL commands, automatically generated to post updates to a database.

Note: FireDAC cannot support the Firebird INTO phrase directly, because it is supported only by PSQL, but not by DSQL, which is used by FireDAC. However, you can use the {INTO } escape clause. Other option with Firebird is to use the Open method instead of ExecSQL, as seen in the following example.

FDQuery1.SQL.Text := 'insert into MyTab (f2, f3) values (:f2, :f3) returning f1';
FDQuery1.Params[0].AsString := 'qwe';
FDQuery1.Params[1].AsInteger := 100;
FDQuery1.Open;
FDQuery1.Fields[0].Value; // Value of a first field, listed in RETURNING

Note: FireDAC supports the {INTO } escape sequence for Microsoft SQL Server, but with a slightly different syntax:

insert into MyTab (f1) output inserted.f2 {into :p2} values (:p1)