Recent

Author Topic: SQLDB Insert Returning id;  (Read 11100 times)

goodname

  • Sr. Member
  • ****
  • Posts: 297
SQLDB Insert Returning id;
« on: August 30, 2011, 08:54:29 pm »
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?
« Last Edit: September 07, 2011, 03:35:47 pm by goodname »

Leledumbo

  • Hero Member
  • *****
  • Posts: 8114
  • Programming + Glam Metal + Tae Kwon Do = Me

99Percent

  • Full Member
  • ***
  • Posts: 154
Re: SQLDB Insert Returning id;
« Reply #2 on: August 31, 2011, 05:07:54 am »
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

  • Guest
Re: SQLDB Insert Returning id;
« Reply #3 on: August 31, 2011, 07:54:13 am »
Which RDBMS do you use ?
AFAIK only PostgreSQL, MySQL can support insert with row value constructors and only PostgreSQL support returning clause.

Quote
Running 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
« Last Edit: August 31, 2011, 07:56:03 am by Lacak2 »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: SQLDB Insert Returning id;
« Reply #4 on: August 31, 2011, 08:37:34 am »
Which 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:
Quote
An 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;
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: SQLDB Insert Returning id;
« Reply #5 on: August 31, 2011, 04:19:33 pm »
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.html
Quote
The 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.
« Last Edit: September 01, 2011, 03:14:38 pm by goodname »

Lacak2

  • Guest
Re: SQLDB Insert Returning id;
« Reply #6 on: September 02, 2011, 07:18:52 am »
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
« Last Edit: September 02, 2011, 12:55:11 pm by Lacak2 »

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: SQLDB Insert Returning id;
« Reply #7 on: September 02, 2011, 06:59:23 pm »
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
Quote
PQresultStatus

    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).
« Last Edit: September 02, 2011, 07:10:37 pm by goodname »

Lacak2

  • Guest
Re: SQLDB Insert Returning id;
« Reply #8 on: September 05, 2011, 07:11:48 am »
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

  • Sr. Member
  • ****
  • Posts: 297
Re: SQLDB Insert Returning id;
« Reply #9 on: September 05, 2011, 04:42:29 pm »
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.
« Last Edit: September 05, 2011, 04:58:17 pm by goodname »