Lazarus
Programming => Databases => Topic started by: tudi_x 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
-
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.
-
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:
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?
-
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.
-
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.
-
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.
-
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
-
@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.