Hi all,

I'm trying to call a Informix function in my Java Application using
JDBC. The function gets the last autogenerated value for a serial column

when you insert a record

Here is the details on the function as found in the PDF documentation

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. You can use this DBINFO function to

retrieve
the last serial value that is inserted into a table or to determine the
number of
rows that a query processes anywhere within SQL statements and within
stored procedures.
This option applies to all SQL APIs.
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, partnum 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 select dbinfo ('sqlca.sqlerrd1')
from sec_tab where partnum = 6;
.
.

as you can see most of the docs are not geared tward the JDBC API

when I asked someone on the Informix news group about this they gave me
this advice

"select dbinfo('sqlca.sqlerrd1') from <someTableWithOneRow>"

Between the docs and the advice this I came up with a few ideas.

// get the serial Id column value
Statement navStmt = con.createStatement ();
return_id = navStmt.executeUpdate("select dbinfo('sqlca.sqlerrd1') from
properties");

This always returns -1 no mater what.

I've tried myStatement.executeQuery() SQL call and got the first
returned column from the ResultSet as a integer but this returned the
same thing

I've also tried myStatement.execute() then insertID
=myStatement.getUpdateCount() but this also returns -1.

has anyone tried something like this before? My simple (very simple)
object relational mapping system requires I know what a tables serial ID

field get set to everytime I do an insert!

Thanks

~Scott

--
________________________________________
Scott Ahten
Code Wrangler
Stage 22 Imaging, Inc.

http://www.***.com/

Here's a section of code that I've got working:

st.executeUpdate( "INSERT INTO " + table + " (" + cols + ") VALUES (" + vals
+ ")");
rs = st.executeQuery("SELECT DBINFO('sqlca.sqlerrd1') FROM " + table);
if (rs.next())
    newKey = rs.getInt(1);
rs.close();

Here's my configuration:

Server:
    INFORMIX-OnLine Version 7.23.UC1
    SCO SQL-Retriever 4.15.900; SCO JDBC Driver 4.15.10
    SCO OpenServer 5.0.4

Client:
    Netscape Communicator 4.03
    Windows NT 4.0/95

Edwin Babadaglian