Thanks for the link Leledumbo but it looks like you were not able to use the INSERT RETURNING in your project either.
INSERT INTO tbl(fid) VALUES ('a','b','c') RETURNING @id=id;
RetId:=parambyname('id').asInteger;
99Percent The Insert statement should fail as the number of fields and values does not match. Using ParamByName will not work as the result set is not captured by SQLDB to work with.
Which RDBMS do you use ?
AFAIK only PostgreSQL, MySQL can support insert with row value constructors and only PostgreSQL support returning clause.
PostgreSQL, Oracle, and Firebird support the INSERT RETURNING clause. I'm using PostgreSQL.
From the responses it looks like people have been able to use INSERT RETURNING clause with Firebird. Have only tried against PostgreSQL. Would be good if it could be done as it is efficient and the most cross database engine method I know of to get the incremented field.
For the PostgreSQL engine it should not be too difficult to add support as the documentation says the returned format is the same as a SELECT. My guess is a change to the sqldb detect statement type to allow the use of SQLQuery.Open.
http://www.postgresql.org/docs/8.4/interactive/sql-insert.htmlThe optional RETURNING clause causes INSERT to compute and return value(s) based on each row actually inserted. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number. However, any expression using the table's columns is allowed. The syntax of the RETURNING list is identical to that of the output list of SELECT.