Retrieving Serial Values from ODBC and SPL

Hello, I use the serial datatype extensively to generate unique keys. When building master/detail records, I need--in a programmatic fashion--the generated serial ID from the master insert to key the details. All of this is of course wrapped in a transaction. How? This is easy in Informix 4GL and ESQL/C, both products I've used in past lives. But these days, I'm working with ODBC and JDBC. In JDBC, I can retrieve the returned serial number by casting the prepared statement to a special "IfxStatement," and calling its getSerial() method. This is great for a prepared statement. What about a stored procedure? How do I return a serial value from a stored procedure? Is there a nice clean way by, say, returning the value in sqlerrd[2]? Is this array accessible, in some form, from within the SPL? Similarly, how do I retrieve this value from an ODBC call? No, I'm not using ODBC *with* JDBC, so the two aren't related, but the problem is the same. Am I missing something? Is this not the best way to generate a unique key? Why is it so hard to get its value back while I'm still in a transaction, so I can build the detail records? Trebor Fenstermaker BTG, Inc. Fairfax VA USA

"Trebor C. Fenstermaker" wrote: > I use the serial datatype extensively to generate unique keys. When > building master/detail records, I need--in a programmatic fashion--the > generated serial ID from the master insert to key the details. All of this > is of course wrapped in a transaction. > > How? This is easy in Informix 4GL and ESQL/C, both products I've used in > past lives. But these days, I'm working with ODBC and JDBC. In JDBC, I > can retrieve the returned serial number by casting the prepared statement to > a special "IfxStatement," and calling its getSerial() method. This is great > for a prepared statement. > > What about a stored procedure? How do I return a serial value from a stored > procedure? Is there a nice clean way by, say, returning the value in > sqlerrd[2]? Is this array accessible, in some form, from within the SPL? > > Similarly, how do I retrieve this value from an ODBC call? No, I'm not > using ODBC *with* JDBC, so the two aren't related, but the problem is the > same. > > Am I missing something? Is this not the best way to generate a unique key? > Why is it so hard to get its value back while I'm still in a transaction, so > I can build the detail records? Perhaps it is the dbinfo() - function you're searching for! Here are a few lines copied form answers online. 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 an INSERT statement that inserts a serial value. The following example uses the 'sqlca.sqlerrd1' option: . . EXEC SQL create table fst_tab (ordernum serial, part_num int); EXEC SQL create table sec_tab (ordernum serial); EXEC SQL insert into fst_tab VALUES (0,1); EXEC SQL insert into fst_tab VALUES (0,4); EXEC SQL insert into fst_tab VALUES (0,6); EXEC SQL insert into sec_tab values (dbinfo('sqlca.sqlerrd1')); . . This example inserts a row that contains a primary-key serial value into the fst_tab table, and then uses the DBINFO() function to insert the same serial value into the sec_tab table. The value that the DBINFO() function returns is the serial value of the last row that is inserted into fst_tab. Regards Bernhard Donaubauer

You cannot access DBInfo directly from ODBC. However, you can call a stored procedure that returns DBInfo. -- Bashar Chalabi CTL, London Trebor C. Fenstermaker <tcfenstermaker@acm.org> wrote in message news:7qekdj$htv$1@apollo.nyed.uscourts.gov... > Hello, > > I use the serial datatype extensively to generate unique keys. When > building master/detail records, I need--in a programmatic fashion--the > generated serial ID from the master insert to key the details. All of this > is of course wrapped in a transaction. > > How? This is easy in Informix 4GL and ESQL/C, both products I've used in > past lives. But these days, I'm working with ODBC and JDBC. In JDBC, I > can retrieve the returned serial number by casting the prepared statement to > a special "IfxStatement," and calling its getSerial() method. This is great > for a prepared statement. > > What about a stored procedure? How do I return a serial value from a stored > procedure? Is there a nice clean way by, say, returning the value in > sqlerrd[2]? Is this array accessible, in some form, from within the SPL? > > Similarly, how do I retrieve this value from an ODBC call? No, I'm not > using ODBC *with* JDBC, so the two aren't related, but the problem is the > same. > > Am I missing something? Is this not the best way to generate a unique key? > Why is it so hard to get its value back while I'm still in a transaction, so > I can build the detail records? > > Trebor Fenstermaker > BTG, Inc. > Fairfax VA USA > >

> What about a stored procedure? How do I return a serial value from a > stored procedure? Is there a nice clean way by, say, returning the value > in sqlerrd[2]? Is this array accessible, in some form, from within the > SPL? DBINFO does seem to be the answer. I've been using Informix for ten years, and never heard of it, though I guess that's partly because I've never really used SPL or ISQL for programming until now. DBINFO is also not featured prominently in the documentation--no cross-reference in the index under Serial, and no reference under any of the discussions of Serial in the Reference, Syntax, or Tutorial manuals. I do remember DBINFO being discussed in this newsgroup earlier this month, but didn't make the connection. Anyway, I'm humbled :-) Thank you all for your help! Trebor Fenstermaker BTG, Inc. Fairfax VA USA