RETURNING Unified Support (FireDAC)

From Appmethod Topics
Jump to: navigation, search

Go Up to Preprocessing Command Text (FireDAC)



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