getting the serial value

All, Informix 11.50 I have a colleague who is inserting into a table with a serial column. He uses the value 0 for the serial column. He wants to know what value has been assigned. He is using C++. Can anyone tell me how this can be done? Is it using something like sqlca.sqlerrd or is there another method? Thanks Andy Grantham.

Hi If using Embedded SQL/C, he should check sqlca.sqlerrd[1] to know the last inserted serial value. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.es qlc.doc/esqlc214.htm | sqlerrd | array of 6 int4s | [0] | After a successful PREPARE statementfor a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursoris opened, this field contains the estimated number of rows affected. | | | | [1] | When SQLCODE contains an error code, this field contains either zero or an additionalerror code, called the ISAM error code, that explains the cause of themain error. After a successfulinsert operation of a single row, this field contains the value of any SERIALvalue generated for t hat row. | Regards Ignacio From: Andrew Grantham <agrantha@hotmail.com> To: ids@iiug.org Sent: Tuesday, December 2, 2014 12:42 PM Subject: getting the serial value [34256] All, Informix 11.50 I have a colleague who is inserting into a table with a serial column. He uses the value 0 for the serial column. He wants to know what value has been assigned. He is using C++. Can anyone tell me how this can be done? Is it using something like sqlca.sqlerrd or is there another method? Thanks Andy Grantham. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hello. You can use dbinfo(), according to the manual: Using the 'sqlca.sqlerrd1' Option The 'sqlca.sqlerrd1' option returns a single integer that provides the last serial value that is inserted into a table. To ensure valid results, use this option immediately following a singleton INSERT statement that inserts a single row with a serial value into a table. Take note that this is valid only right after the insert stament execution. Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: agrantha@hotmail.com > Subject: getting the serial value [34256] > Date: Tue, 2 Dec 2014 10:42:49 -0500 > > All, > > Informix 11.50 > > I have a colleague who is inserting into a table with a serial column. He uses > the value 0 for the serial column. He wants to know what value has been > assigned. He is using C++. Can anyone tell me how this can be done? Is it > using something like sqlca.sqlerrd or is there another method? > > Thanks > > Andy Grantham. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Using the sqlca.sqlerrd[1], which contains the last inserted SERIAL type value for the current session immediately after an insert, is the normative and preferred method. You can also use: exec sql select dbinfo('sqlca.sqlerrd1') into :last_serial from sysmaster:sysdual; exec sql select dbinfo('bigserial') into :last_bigserial from sysmaster:sysdual; exec sql select dbinfo('serial8') into :last_serial8 from sysmaster:sysdual; To get the inserted value for SERIAL, BIGSERIAL, or SERIAL8 type inserted values. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Tue, Dec 2, 2014 at 10:42 AM, Andrew Grantham <agrantha@hotmail.com> wrote: > All, > > Informix 11.50 > > I have a colleague who is inserting into a table with a serial column. He > uses > the value 0 for the serial column. He wants to know what value has been > assigned. He is using C++. Can anyone tell me how this can be done? Is it > using something like sqlca.sqlerrd or is there another method? > > Thanks > > Andy Grantham. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01175e7d08ade705093de993

You will need dbinfo if it is a serial8 Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 > On Dec 2, 2014, at 09:52, Ignacio Bisso <ignacio_bisso@yahoo.com> wrote: > > Hi > > If using Embedded SQL/C, he should check sqlca.sqlerrd[1] to know the last > inserted serial value. > > http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.es qlc.doc/esqlc214.htm > > | sqlerrd | array of 6 > int4s | [0] | After a successful PREPARE statementfor a SELECT, UPDATE, > INSERT, or DELETE statement, or after a select cursoris opened, this field > contains the estimated number of rows affected. | > | > | > | [1] | When SQLCODE contains an error code, this field contains either zero > or an additionalerror code, called the ISAM error code, that explains the > cause of themain error. After a successfulinsert operation of a single row, > this field contains the value of any SERIALvalue generated for t hat row. | > > Regards > Ignacio > > From: Andrew Grantham <agrantha@hotmail.com> > To: ids@iiug.org > Sent: Tuesday, December 2, 2014 12:42 PM > Subject: getting the serial value [34256] > > All, > > Informix 11.50 > > I have a colleague who is inserting into a table with a serial column. He uses > the value 0 for the serial column. He wants to know what value has been > assigned. He is using C++. Can anyone tell me how this can be done? Is it > using something like sqlca.sqlerrd or is there another method? > > Thanks > > Andy Grantham. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

Hello, we have created a small function for that purpose on all of our DBs: CREATE FUNCTION get_last_id() RETURNING INT;RETURN DBINFO('sqlca.sqlerrd1'); END FUNCTION; Regards, Jens