Re: Informix Inserts and JDBC

Scott Ahten wrote: > 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. > scott@stage22.com > http://www.stage22.com Scott, 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 edwinb@panix.com