What serial idnt of record just inserted
Question: inside an SPL procedure, after inserting a row with 0 into a SERIAL column, how do you get the generated serial value? Resolved quickly: use SELECT DBINFO('sqlca.sqlerrd1') FROM systables WHERE tabid = 1 (or FROM dual in newer versions), which returns the last serial inserted in the current session. Art Kagel added DBINFO('SERIAL8') and DBINFO('BIGSERIAL') for those types, and noted the same sqlerrd1 query applies for SE 7.2; links to the official ESQL/C docs were also given.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am using SPL to insert a record with a serial data type as key. I insert with a 0 in that field. Record is inserted successfully. How can that SPL procedure find out the value assigned to that serial field for the row I just inserted. Thanks
OK I found an answer sorry for the bother
dbinfo("sqlca.sqlerrd1")
returns the value I wanted
Hi, use sqlerrd [1] in select: SELECT DBINFO('SQLCA.SQLERRD1') into h_serialid FROM systables WHERE tabid = 1; (or select ... from dual; in newer versions) Marcus ----- Ursprüngliche Mail ----- Von: "BILL MARSHALL" <cwm@stober.com> An: ids@iiug.org Gesendet: Montag, 9. Juli 2012 15:59:17 Betreff: What serial idnt of record just inserted [27567] I am using SPL to insert a record with a serial data type as key. I insert with a 0 in that field. Record is inserted successfully. How can that SPL procedure find out the value assigned to that serial field for the row I just inserted. Thanks ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
According to oficial docs (do you know Information Center website)? You should check it out... http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.esqlc.doc/id s_esqlc_0407.htm?resultof=%22%73%71%6c%63%61%22%20%22%73%71%6c%63%22%20 Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator - Cleartech Ltda BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: cwm@stober.com > Subject: What serial idnt of record just inserted [27567] > Date: Mon, 9 Jul 2012 09:59:17 -0400 > > I am using SPL to insert a record with a serial data type as key. > > I insert with a 0 in that field. Record is inserted successfully. > > How can that SPL procedure find out the value assigned to that serial field > for the row I just inserted. > > Thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Version information would help. You can, however, always retrieve the last serial column value inserted within the current session with: SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 1; For SERIAL8 or BIGSERIAL use: SELECT DBINFO( 'SERIAL8' ) FROM systables WHERE tabid = 1; SELECT DBINFO( 'BIGSERIAL' ) FROM systables WHERE tabid = 1; As appropriate. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 9, 2012 at 9:59 AM, BILL MARSHALL <cwm@stober.com> wrote: > I am using SPL to insert a record with a serial data type as key. > > I insert with a 0 in that field. Record is inserted successfully. > > How can that SPL procedure find out the value assigned to that serial field > for the row I just inserted. > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340563589eaf04c4675c39
Hi Art!.. What would the equivalent query be for SE 7.2?
Please include enough context for people to be able to understand your question without having to look at other messages! On Mon, Jul 9, 2012 at 6:17 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Hi Art!.. What would the equivalent query be for SE 7.2? > If anything is going to work, it will be: SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM systables WHERE tabid = 1; Only experimentation (or manual bashing) will reveal whether it does work. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --e89a8f22c34f69f9fe04c46f8abd
It's the same: SELECT DBINFO( 'sqlca.sqlerrd1' ) from systables where tabid = 1; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 9, 2012 at 9:17 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > Hi Art!.. What would the equivalent query be for SE 7.2? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d0972922504c46fb880