RETURNING Unified Support (FireDAC)
Go Up to Preprocessing Command Text (FireDAC)
Contents
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)