Getting last inserted serial in transaction
Does anyone reading this knows of a way to obtain the serial value that will get assigned a record at the end of a transaction? I have a relational database setup an a set of business objects programmed in .NET that take care of inserting data into DB entities. The data is setup in a way that I need to know the Serial ID of a parent object to be able to insert children objects. I have been using the following code to obtain it after insert: SELECT DBINFO('SQLCA.SQLERRD1') FROM systables WHERE tabname = 'systables' That works if the objects are not sharing a common transaction for all inserts. If, however, I wrapped all objects' inserts in a transaction, the above statement fails to get the id and returns 0 as nothing has been physically inserted yet. My question is, is there a way to get the id or what is the preferred way of doing what I am trying to accomplish?
Hi, May I suggest using a sequence rather than a serial column. That will let you get the value in advance and assure that no other session or transaction will get that same value. This does assume you are using some release of IDS later than 9.40 (10.00 or 11.50). See the Guide to SQL: Syntax manual for details (the CREATE SEQUENCE statement is a good place to start.) Cheers, Dick Dick Snoke Executive IT Specialist IBM Software Group - ChannelWorks Tel: (404) 487-1595 Email: dsnoke@us.ibm.com From: "PETER GEYFMAN" <peterg75@gmail.com> To: ids@iiug.org Date: 07/20/09 11:04 AM Subject: Getting last inserted serial in transaction [16449] Sent by: ids-bounces@iiug.org Does anyone reading this knows of a way to obtain the serial value that will get assigned a record at the end of a transaction? I have a relational database setup an a set of business objects programmed in .NET that take care of inserting data into DB entities. The data is setup in a way that I need to know the Serial ID of a parent object to be able to insert children objects. I have been using the following code to obtain it after insert: SELECT DBINFO('SQLCA.SQLERRD1') FROM systables WHERE tabname = 'systables' That works if the objects are not sharing a common transaction for all inserts. If, however, I wrapped all objects' inserts in a transaction, the above statement fails to get the id and returns 0 as nothing has been physically inserted yet. My question is, is there a way to get the id or what is the preferred way of doing what I am trying to accomplish? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Sorry, forgot to mention that We're currently on IDS v. 9.40... There are plans to upgrade to 11.5, but when that will happen is unknown and the system described in my previous post is ready for production now.
Something is wrong. Your object is not inserting the parent record, it must
be PUTing it to an insert cursor and then not flushing the cursor. Even
within a transaction, the SERIAL coumn value is actually assigned
immediately when the row is inserted into the table, NOT at commit time.
You can verify this in dbaccess. Start two sessions in dbaccess (easiest in
commandline mode - 'dbaccess databasename -'), begin work in both, insert a
row into one session into a serial numbered table inserting a zero into the
serial column. Now in the other session insert a row into the same table
again with a zero for the serial column. Now rollback the first transaction
and commit the second once the rollback completes. If you retrieve the
inserted row from the second session or run the DBINFO('sqlca.sqlerrd1')
query, you will see that the serial number assigned skips the number that
would have been assigned to the row that was rolled back.
Using INSERT Cursors and PUT instead of INSERT is common and efficient when
many rows may be inserted as part of a single transaction, so your library
may be doing that. See if your object library has a member function to
force the insert to flush the cursor.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Jul 20, 2009 at 11:01 AM, PETER GEYFMAN <peterg75@gmail.com> wrote:
> Does anyone reading this knows of a way to obtain the serial value that
> will
> get assigned a record at the end of a transaction? I have a relational
> database setup an a set of business objects programmed in .NET that take
> care
> of inserting data into DB entities. The data is setup in a way that I need
> to
> know the Serial ID of a parent object to be able to insert children
> objects. I
> have been using the following code to obtain it after insert:
> SELECT DBINFO('SQLCA.SQLERRD1') FROM systables WHERE tabname = 'systables'
>
> That works if the objects are not sharing a common transaction for all
> inserts. If, however, I wrapped all objects' inserts in a transaction, the
> above statement fails to get the id and returns 0 as nothing has been
> physically inserted yet.
>
> My question is, is there a way to get the id or what is the preferred way
> of
> doing what I am trying to accomplish?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5bd8c7cb976046f24e77d
Thanks Art!
I was writing a rebuttal to your post and wrote a sample table and stored
procedure to prove my point. However when testing my sample I realized that I
DO get a serial using that sample. So by looking through the actual code of my
software I realized that I was using 2 different connection objects to insert
and get the serial and that's why I wasn't getting the id.
If anyone is interested I am including the sample code I came up with:
create table "peterg".test_serial_with_trans
(
test_id SERIAL,
test_text VarChar(50)
)
create procedure "peterg".proc_test_serial_with_trans()
returning integer as serial_val;
define ser_val integer;
begin work;
insert into test_serial_with_trans values (0, "In Transaction");
SELECT DBINFO('SQLCA.SQLERRD1')
INTO ser_val
FROM systables
WHERE tabname = 'systables';
return ser_val with resume;
commit work;
insert into test_serial_with_trans values (0, "Not In Transaction");
SELECT DBINFO('SQLCA.SQLERRD1')
INTO ser_val
FROM systables
WHERE tabname = 'systables';
return ser_val with resume;
end procedure
execute procedure proc_test_serial_with_trans()