Re: Informix function call with JDBC

Scott Ahten <scott@stage22.com> wrote: Scott, The trick with dbinfo is that it returns a value for each row in the table. Thus executeUpdate fails because the server is returning a ResultSet. Also, add 'unique' to your query so you get a single valued ResultSet. Try something like this: String sql = new String(); Statement stmt = conn.createStatement(); int nextSerial; // insert row sql = "insert into table (col1, date) values (0, today)"; stmt.executeUpdate(sql); // get new serial value sql = "select unique dbinfo('sqlca.sqlerrd1') from table"; ResultSet rs = stmt.executeQuery(sql); if (rs.next()) { nextSerial = rs.getInt(1); } else { ...error } Enjoy, --Jeff >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 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 > >