Recent

Author Topic: Catch last inserted value for autoincremented column  (Read 3597 times)

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Catch last inserted value for autoincremented column
« on: December 16, 2014, 06:01:00 pm »
Hi All,
I am trying to catch the ID from an auto incremented ID column.
Without the start transaction and commit I can catch the value as Query.FieldValues['ID'] .
Table definition is columns ID, a and b for example.

Please advise how would the value of the incremented ID column would be caught in a transaction :
(If sent I get - field ID unknown)

start transaction;
insert into tableA(a, b) values(1,2) returning id;
commit;

Currently I am using Zeos but sqldb option should also catch the last inserted value.

Thank you

Lazarus 1.2.6, 32 bit on Win 7 64 bit

Lazarus 2.0.2 64b on Debian LXDE 10

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Catch last inserted value for autoincremented column
« Reply #1 on: December 16, 2014, 07:13:40 pm »
This is a database specific function. Generally it involves calling a database specific sequence function or including a Returning clause in your insert, update, or delete query then Opening the query to read the returned data.

tudi_x

  • Hero Member
  • *****
  • Posts: 538
Re: Catch last inserted value for autoincremented column
« Reply #2 on: December 16, 2014, 07:41:57 pm »
Sorry, I did not understand your answer.

Basically the returning of any field in the query is specific to Oracle and Postgres SQL implementations.

Currently if I do a:

Code: [Select]
var
   s : string

begin
      Query.SQL.Add('insert into tableA(a, b) values(1,2) returning id;');
      Query.Open;

      s := Query.FieldValues['ID'];  //it even works with Query.Fields[0].AsString;

it is working - I get for s the value of the auto increment column.

The issue arises when I am wrapping my insert into a transaction.

My question is how to write the code so the returned value is caught - is this possible - as the database returns the value as per the above?
Lazarus 2.0.2 64b on Debian LXDE 10

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Catch last inserted value for autoincremented column
« Reply #3 on: December 16, 2014, 07:55:34 pm »
Not entirely sure what your trying to do but I think you need the sequence functions currval/nextval. They are handled differently between PostgreSQL and Oracle so you will have to look up the documentation for each database engine.
« Last Edit: December 16, 2014, 07:59:27 pm by goodname »

LacaK

  • Hero Member
  • *****
  • Posts: 600
Re: Catch last inserted value for autoincremented column
« Reply #4 on: December 16, 2014, 08:02:40 pm »
The issue arises when I am wrapping my insert into a transaction.
It is because in currrent implementation of sqlDB, Commit closes dataset, so you get error about unknown field "ID"
You can use instead of Commit , CommitRetaining or wait , when FPC 2.8.0 will be released (or build own from SVN TRUNK), there will be more improvements in this area.

ahiggins

  • Jr. Member
  • **
  • Posts: 92
Re: Catch last inserted value for autoincremented column
« Reply #5 on: December 16, 2014, 11:01:51 pm »
I had a Master/Detail setup using Firebird. i needed the ID (autoinc) value from the Master table after inserting a record so i could use the value in the Detail table. Like LacaK points out Commit closes the table so i used CommitRetaining, but it's worth noting i had to refesh the Master table before i could get the valid ID(autoinc) value.   

goodname

  • Sr. Member
  • ****
  • Posts: 297
Re: Catch last inserted value for autoincremented column
« Reply #6 on: December 16, 2014, 11:39:22 pm »
I had a Master/Detail setup using Firebird. i needed the ID (autoinc) value from the Master table after inserting a record so i could use the value in the Detail table.
The following thread covers (autoinc) for Firebird.
http://forum.lazarus.freepascal.org/index.php?topic=21240.0

ahiggins

  • Jr. Member
  • **
  • Posts: 92
Re: Catch last inserted value for autoincremented column
« Reply #7 on: December 16, 2014, 11:58:32 pm »
@goodname,  many thanks, makes a little more sense to me now.
on a seperate note: it's hard reading BigChimp's posts in that thread knowing there will be no more.

 

TinyPortal © 2005-2018