SQLDB Insert Returning id;
lazarus / 16-Databases

----------------------------------------------------------------------
goodname · 2011-08-30 18:54

For some database engines it is possible for an INSERT statement to
return values for the auto incrementing fields.

INSERT INTO tbl(fld)VALUES('a'),('b'),('c'),('d')RETURNING id;

The above INSERT should create four records and return the id's for
the new records.

Setting the SQLQuery.SQL.Text is straight forward.
Running SQLQuery.ExecSQL will run the query but will not have a result
set returned.
Running a SQLQuery.Open will throw an error stating that it is not a
SELECT query.

Is it possible using SQLDB to do an INSERT that accepts a result set?

----------------------------------------------------------------------
Leledumbo · 2011-08-31 03:01

http://www.lazarus.freepascal.org/index.php?topic=11486.0

----------------------------------------------------------------------
99Percent · 2011-08-31 03:07

use output parameters. Not sure what sql you using but it would go
something like this:

INSERT INTO tbl(fid) VALUES ('a','b','c') RETURNING @id=id;

RetId:=parambyname('id').asInteger;

----------------------------------------------------------------------
Lacak2 · 2011-08-31 05:54

Which RDBMS do you use ?
AFAIK only PostgreSQL, MySQL can support insert with row value
constructors and only PostgreSQL support returning clause.

QuoteRunning a SQLQuery.Open will throw an error stating that it is
not a SELECT query.Yes it is a limitation of how sqldb detect
statement type

----------------------------------------------------------------------
BigChimp · 2011-08-31 06:37

Quote from: Lacak2 on August 31, 2011, 07:54:13 amWhich RDBMS do you
use ?
AFAIK only PostgreSQL, MySQL can support insert with
Firebird (2.5. at least, Firebird 2.5 language reference page 78
mentions it was implemented in version 2.0) supports this:
QuoteAn INSERT statement adding at most one row may optionally include
a RETURNING clause in
order to return values from the inserted row. The clause, if present,
need not contain all of the insert columns
and may also contain other columns or expressions.
This works for a table CUSTOMER with a primary key CUST_NO with
trigger and generator/sequence (aka Firebird's solution for
autonumbers):
Code: [Select]INSERT INTO CUSTOMER (CUSTOMER)
 VALUES (
'Silly Customer'
) RETURNING CUST_NO;
A version with parameters is also mentioned in the docs:
Code: [Select]insert into Dumbbells (firstname, lastname, iq)
  select fname, lname, iq from Friends order by iq rows 1
  returning id, firstname, iq into :id, :fname, :iq;

----------------------------------------------------------------------
goodname · 2011-08-31 14:19

Thanks for the link Leledumbo but it looks like you were not able to
use the INSERT RETURNING in your project either.

Quote from: 99Percent on August 31, 2011, 05:07:54 amINSERT 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.

Quote from: Lacak2 on August 31, 2011, 07:54:13 amWhich 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.html
QuoteThe 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.

----------------------------------------------------------------------
Lacak2 · 2011-09-02 05:18

Interbase/Firebird uses this code to change on the fly statement type:

Code: [Select]    // If the statementtype is
isc_info_sql_stmt_exec_procedure then
    // override the statement type derrived by parsing the query.
    // This to recognize statements like 'insert into .. returning'
correctly
    if IBStatementType = isc_info_sql_stmt_exec_procedure then
      FStatementType := stExecProcedure;
It is based on API, that is able to return statement type of prepared
statement.
In case of PostgreSQL I am not sure if there is such information
available...May be, that we can use:
PQdescribePrepared and test PGresult ...

I reported it as a bug: http://bugs.freepascal.org/view.php?id=20133
There is also attached patch, with solution

----------------------------------------------------------------------
goodname · 2011-09-02 16:59

Hi Lacak2

Had look at your patch. See that your using PGRES_COMMAND_OK but not
PGRES_TUPLES_OK.

Found documentation about the libpq library. The documentation on this
seams to be unchanged between versions 8.2 and the current 9.0. It
does not specifically mention INSERT, UPDATE, or DELETE RETURNING so
it will have to be tested.

If PGRES_TUPLES_OK then there is a result set which could be zero or
more records.
If PGRES_COMMAND_OK then there is no result set.

http://www.postgresql.org/docs/8.2/static/libpq-exec.html#LIBPQ-EXEC-
MAIN
http://www.postgresql.org/docs/9.0/static/libpq-exec.html#LIBPQ-EXEC-
MAIN
QuotePQresultStatus

    Returns the result status of the command.

    ExecStatusType PQresultStatus(const PGresult *res);

    PQresultStatus can return one of the following values:

    PGRES_EMPTY_QUERY
        The string sent to the server was empty.

    PGRES_COMMAND_OK
        Successful completion of a command returning no data.

    PGRES_TUPLES_OK
        Successful completion of a command returning data (such as a
SELECT or SHOW).

----------------------------------------------------------------------
Lacak2 · 2011-09-05 05:11

Hi goodname,
pleasy try my patch if you can.
For me it works ok.

I use PGRES_COMMAND_OK because I use PQdescribePrepared (to obtain
some information (if there is column count > 0) about prepared
statement), not PQexec (to actualy execute it and read result set)

----------------------------------------------------------------------
goodname · 2011-09-05 14:42

Should have taken a little more time to review the patch. I'm trying
to catch up to where you already are. Was getting confused as more
than one libpq function returns PGRES_COMMAND_OK and it's meaning
depends on the function called.

Have not had a chance to test the patch yet. It looks good. Looking
forward to being able to use it regularly. Thank you for creating it.