Catch last inserted value for autoincremented column
lazarus / 16-Databases

----------------------------------------------------------------------
tudi_x · 2014-12-16 17:01

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

----------------------------------------------------------------------
goodname · 2014-12-16 18:13

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 · 2014-12-16 18:41

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?

----------------------------------------------------------------------
goodname · 2014-12-16 18:55

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.

----------------------------------------------------------------------
LacaK · 2014-12-16 19:02

Quote from: tudi_x on December 16, 2014, 07:41:57 pmThe 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 · 2014-12-16 22:01

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 · 2014-12-16 22:39

Quote from: ahiggins on December 16, 2014, 11:01:51 pmI 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 · 2014-12-16 22:58

@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.