Getting last insert id (ZEOS or SQLdb) lazarus / 16-Databases ---------------------------------------------------------------------- Leledumbo · 2010-12-19 15:04 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 · 2010-12-19 16:28 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 · 2010-12-19 17:41 I've never heard of that. How portable it is across dbms? ---------------------------------------------------------------------- clauslack · 2010-12-19 22:15 "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 · 2010-12-20 20:20 Hi. Within Firebird, it's portable. hth - Regards Benny ---------------------------------------------------------------------- Leledumbo · 2010-12-21 02:41 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 · 2010-12-21 06:25 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 · 2010-12-21 14:18 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 · 2010-12-21 14:27 QuoteCheck 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 · 2010-12-21 15:29 For Zeos, use the TZSequence object.  I use it succesfully with Postgresql. (but note that it might not be safe with automatic transactions) ---------------------------------------------------------------------- Marc · 2010-12-22 16:12 set the AutoGenerateValue of the ID field to arAutoInc, then zeos will update this value (for postgre you need to set it to arDefault) ---------------------------------------------------------------------- Leledumbo · 2010-12-22 23:06 QuoteFor Zeos, use the TZSequence object.How to use it? I only see Connection property... Quoteset 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 · 2010-12-22 23:27 Quote from: Leledumbo on December 23, 2010, 12:06:23 amQuoteFor 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 · 2010-12-23 02:45 QuoteIf 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? QuoteThis 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 · 2010-12-23 09:10 Quote from: Leledumbo on December 23, 2010, 12:06:23 amQuoteset 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 ---------------------------------------------------------------------- marcov · 2010-12-23 12:41 Quote from: Leledumbo on December 23, 2010, 03:45:46 amQuoteIf 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? No.Where I put ID, the name of the PK field is placed. Of course the case has to match, but it doesn't even have to be "id" at all afaik, that is just tradition. The PK bit matters, and the pgadmin tool creates the corresponding __seq QuoteThis 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. [/quote] I think you need the _current_ value to get the last emitted value. ---------------------------------------------------------------------- Leledumbo · 2010-12-23 15:01 QuoteIf you create the query in code, then you can access the property by:   yourQuery.FieldByName('ID').AutoGenerateValue := arAutoIncAh... now I see. It's a property of TField. Thanks, I'll try. QuoteI think you need the _current_ value to get the last emitted value.Err... OK. ---------------------------------------------------------------------- SteveF · 2010-12-23 18:45 Quote from: marcov on December 23, 2010, 12:27:48 amQuote from: Leledumbo on December 23, 2010, 12:06:23 amQuoteFor 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. Pretty much the same with Firebird, except it's not so automatic.  In Firebird they're known as "Generators". In Firebird you need to assign the PK constraint to the relevant field, build the Generator for each PK that needs to Auto-increment, and make a Before-Insert Trigger for each table which, if the PK field of the new entry is empty or zero, asks the matching Generator for the next sequential number and puts it in the table with the new data for the other columns. Once this is set up, the insert SQL sent by Lazarus does not need to send a PK value.  Just send all the other data columns, and the Auto- inc PK will be handled automagically by the database engine on the back side. Alternatively, you can also interrogate the Generator directly, obtain the new PK value, and include it in the insert SQL.  If this is done scrupulously, the Trigger is not needed (but maybe still a good thing for redundancy).  Either method will work. Steve ---------------------------------------------------------------------- patyi · 2011-09-21 19:01 Hi all, Lazarus developers ! I try this example on FPC 2.4.4 and Lazarus SVN latest with SQLDB, Firebird 2.5, Ubuntu 11.04. with DBQuery do begin     Close;     SQL.Clear;     SQL.Add( 'INSERT INTO katalog (id, name) VALUES (NULL, :name) RETURNING id');     Params[0].AsString := 'BlaBlaBla';  //  ParamByName('name').AsString := 'BlaBlaBla';     Open;     Result := Fields[0].AsInteger; // FieldByName('id').AsInteger;     Close;  end; After Open I got an error :  -Dynamic SQL error, - SQL error code = -504, - Invalid cursor reference, - Cursor is not open. I also try it with latest FPC SVN 2.7.1, Lazarus latest SVN, with same result ! I'm wondering it is broken functionality on SQLDB or I miss something ?  With ZeosDBO 7.0 latest SVN the result is perfectly what is expected !!!!  (sorry for my English !) Best regards : Patyi ---------------------------------------------------------------------- Zoran · 2011-09-21 21:30 The example application I'm attaching to this post works well on FB 2.1 (tested on Win XP and Ubuntu, Lazarus from svn, fpc 2.4.4). It uses "employee" database which comes with Firebird installation. in windows you should find it under "C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\employee.fdb. In Ubuntu, you should decompress it, as explained in lower section of this document: http://www.firebirdsql.org/manual/ubusetup.html ---------------------------------------------------------------------- kodok_buncit · 2011-09-22 03:06 Quote from: Zoran on September 21, 2011, 11:30:26 pmThe example application I'm attaching to this post works well on FB 2.1 (tested on Win XP and Ubuntu, Lazarus from svn, fpc 2.4.4). It uses "employee" database which comes with Firebird installation. in windows you should find it under "C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\employee.fdb. In Ubuntu, you should decompress it, as explained in lower section of this document: http://www.firebirdsql.org/manual/ubusetup.html not work on SQLDB errod code = 504 invalid cursor reference ---------------------------------------------------------------------- Lacak2 · 2011-09-22 05:53 May be that there is difference between FB 2.1 (works) and FB 2.5 (does not work) ! ---------------------------------------------------------------------- Zoran · 2011-09-22 18:55 Quote from: Lacak2 on September 22, 2011, 07:53:11 amMay be that there is difference between FB 2.1 (works) and FB 2.5 (does not work) ! Probably so. I have FB 2.1 on both OS-s and it works. ---------------------------------------------------------------------- Lacak2 · 2011-09-30 11:42 I can also confirm, that it is FB 2.5 related problem. It seems, that FB 2.5 no more supports "fetching output parameters" from stored procedures or from insert/update/delete ... returning See also related bug report http://bugs.freepascal.org/view.php?id=16236 where is attached patch.