Recent

Author Topic: Getting last insert id (ZEOS or SQLdb)  (Read 37325 times)

Leledumbo

  • Hero Member
  • *****
  • Posts: 8530
  • Programming + Glam Metal + Tae Kwon Do = Me
Getting last insert id (ZEOS or SQLdb)
« on: December 19, 2010, 04:04:02 pm »
I can't find this implemented in ZEOS or SQLdb. I wonder why, isn't this functionality is easy to implement? Currently I create my own:
Code: [Select]
function LastInsertID(const TableName: String): Integer;
begin
  with DBQuery do begin
    SQL.Text := 'SELECT MAX(id) AS last_insert_id FROM :TableName';
    Params.ParamByName('TableName').AsString := TableName;
    Open;
    Result := FieldByName('last_insert_id').AsInteger;
  end;
end;
but I really wish to have this implemented because I almost always use MV(C|P|???) design pattern and this is required for newly created table entry.

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #1 on: December 19, 2010, 05:28:25 pm »
For Firebird (and others)
you can use insert an return primary key in one step, with RETURNING clause.

with DBQuery do begin
    SQL.Text := 'INSERT INTO t1 (...) values (...) returning pk';
    Open;
    Result := FieldByName('pk').AsInteger;
  end;


Tested with Firebird 2.1.4 and fpc  2.3.1 (SQLDB)

Regards


Leledumbo

  • Hero Member
  • *****
  • Posts: 8530
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #2 on: December 19, 2010, 06:41:12 pm »
I've never heard of that. How portable it is across dbms?

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #3 on: December 19, 2010, 11:15:20 pm »
"Firebird 2 introduces RETURNING clause for INSERT statements...."
http://www.firebirdfaq.org/faq243/

Postgre
http://www.postgresql.org/docs/current/interactive/sql-insert.html

Oracle
http://psoug.org/snippet/INSERT-With-Returning-Clause_595.htm
(Stored procedure)
http://www.adp-gmbh.ch/ora/sql/insert_into_x_returning_y.html

I don't know about other DBMS.

I only test with Firebird and SQLDb.

cdbc

  • Sr. Member
  • ****
  • Posts: 414
    • http://www.cdbc.dk
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #4 on: December 20, 2010, 09:20:07 pm »
Hi.
Within Firebird, it's portable.

hth - Regards Benny
If it ain't broke, don't fix it ;)

Leledumbo

  • Hero Member
  • *****
  • Posts: 8530
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #5 on: December 21, 2010, 03:41:21 am »
Weird, Firebird states that it's supported so we can get it like MySQL did. But MySQL doesn't support this clause... so, for safety (because I still don't know the environment of deployment platform), I still use my LastInsertID above.

Lacak2

  • Guest
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #6 on: December 21, 2010, 07:25:49 am »
Some of APIs (for example SQLite, MySQL) provides functions to obtain last value (currently inserted) of identity (autoincrement) columns.

There is it internaly in dbSQL also implemented (search for example for "insertid" in sources in fcl-db directory), but not all SQL connectors provide such information.

General solution will be IMHO fetch this "last identity value" after Append...Post and put into local dataset. See also http://bugs.freepascal.org/view.php?id=16076

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #7 on: December 21, 2010, 03:18:12 pm »
Check this,
 In firebird (maybe other DBMS)
 the primary key has ascending order, and the statement
 select max(id) don't use this index.  So in tables of millions of records,
 this query has a high resources consuming, unless you create a descending index in primary key field.

Regards

Leledumbo

  • Hero Member
  • *****
  • Posts: 8530
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #8 on: December 21, 2010, 03:27:57 pm »
Quote
Check this,
 In firebird (maybe other DBMS)
 the primary key has ascending order, and the statement
 select max(id) don't use this index.  So in tables of millions of records,
 this query has a high resources consuming, unless you create a descending index in primary key field.
Hmm... do you have any other solution? Should I differ the query based on TZConnection.Protocol?

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 10492
  • FPC developer.
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #9 on: December 21, 2010, 04:29:34 pm »
For Zeos, use the TZSequence object.  I use it succesfully with Postgresql.

(but note that it might not be safe with automatic transactions)


Marc

  • Administrator
  • Hero Member
  • *
  • Posts: 2565
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #10 on: December 22, 2010, 05:12:18 pm »
set the AutoGenerateValue of the ID field to arAutoInc, then zeos will update this value
(for postgre you need to set it to arDefault)
//--
{$I stdsig.inc}
//-I still can't read someones mind
//-Bugs reported here will be forgotten. Use the bug tracker

Leledumbo

  • Hero Member
  • *****
  • Posts: 8530
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #11 on: December 23, 2010, 12:06:23 am »
Quote
For Zeos, use the TZSequence object.
How to use it? I only see Connection property...
Quote
set the AutoGenerateValue of the ID field to arAutoInc, then zeos will update this value
(for postgre you need to set it to arDefault)
of what object?

FYI, since I use fpWeb to create this application, I must create the code by hand (dropping the component from palette causes the app to end up in runtime errors).

marcov

  • Administrator
  • Hero Member
  • *
  • Posts: 10492
  • FPC developer.
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #12 on: December 23, 2010, 12:27:48 am »
Quote
For Zeos, use the TZSequence object.
How to use it? I only see Connection property...

If a table is named xxx and there is a field "ID" that been declared PK and autonumber etc in postgres, then there is a sequence counter in postgresql named "xxx_ID_seq"

Enter this into the tzsequence object, and also connect it to connection. You can then put this sequence object in the relevant query, and connect the navigator to it.

This should be doable in code too, but these are mainly interesting if you use e.g. dbnavigators. I don't know how fpweb solves that.

Leledumbo

  • Hero Member
  • *****
  • Posts: 8530
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #13 on: December 23, 2010, 03:45:46 am »
Quote
If a table is named xxx and there is a field "ID" that been declared PK and autonumber etc in postgres, then there is a sequence counter in postgresql named "xxx_ID_seq"
Is "ID" name hardcoded? I've used that, but in lowercase (all my table fields do). Would that be a problem?
Quote
This should be doable in code too, but these are mainly interesting if you use e.g. dbnavigators. I don't know how fpweb solves that.
I see TZSequence has GetNextValue, so probably I can call that from code.

Marc

  • Administrator
  • Hero Member
  • *
  • Posts: 2565
Re: Getting last insert id (ZEOS or SQLdb)
« Reply #14 on: December 23, 2010, 10:10:33 am »
Quote
set the AutoGenerateValue of the ID field to arAutoInc, then zeos will update this value
(for postgre you need to set it to arDefault)
of what object?

The ID field. (Now speaking delphi, haven't used this in lazarus) If you have a TZQuery, and you make the fields of it Persistent, you can change the properties of the ID field.

If you create the query in code, then you can access the property by:
  yourQuery.FieldByName('ID').AutoGenerateValue := arAutoInc
//--
{$I stdsig.inc}
//-I still can't read someones mind
//-Bugs reported here will be forgotten. Use the bug tracker

 

TinyPortal © 2005-2018