Serial Column Informix 10.x

Hi All, I have a table with a primary key column defined as serial. and I also have a child table which refers this. In my application, While making an entry in to the primary table , i also need to make an entry in to the child table. How do i get the generated ID so that i can insert into the child table with this id as a foreign key. I am using IFXJDBC 3.0.JC3. Thanks, Ganesh

This may help. Frank IBM Informix JDBC Driver provides support for the Informix SERIAL and SERIAL8 data types through the methods *getSerial()* and *getSerial8()*, which are part of the implementation of the *java.sql.Statement* interface. Because the SERIAL and SERIAL8 data types do not have an obvious mapping to any JDBC API data types from the *java.sql.Types* class, you must import Informix-specific classes into your Java program to handle SERIAL and SERIAL8 columns. To do this, add the following import line to your Java program: import com.informix.jdbc.*; Use the *getSerial()* and *getSerial8()* methods after an INSERT statement to return the serial value that was automatically inserted into the SERIAL or SERIAL8 column of a table, respectively. The methods return 0 if any of the following conditions are true: · The last statement was not an INSERT statement. · The table being inserted into does not contain a SERIAL or SERIAL8 column. · The INSERT statement has not executed yet. If you execute the *getSerial()* or *getSerial8()* method after a CREATE TABLE statement, the method returns 1 by default (assuming the new table includes a SERIAL or SERIAL8 column). If the table does not contain a SERIAL or SERIAL8 column, the method returns 0. If you assign a new serial starting number, the method returns that number. If you want to use the *getSerial()* and *getSerial8()* methods, you must cast the *Statement* or *PreparedStatement* object to *IfmxStatement*, the Informix-specific implementation of the *Statement* interface. The following example shows how to perform the cast: cmd = "insert into serialTable(i) values (100)"; stmt.executeUpdate(cmd); System.out.println(cmd+"...okay"); int serialValue = ((IfmxStatement)stmt).getSerial(); System.out.println("serial value: " + serialValue); If you want to insert consecutive serial values into a column of data type SERIAL or SERIAL8, specify a value of 0 for the SERIAL or SERIAL8 column in the INSERT statement. When the column is set to 0, the database server assigns the next-highest value. On 2/16/07, VIJAYAKUMAR GANESH KUMAR <ganesh17jan@gmail.com> wrote: > > Hi All, > > I have a table with a primary key column defined as serial. > and I also have a child table which refers this. > > In my application, While making an entry in to the primary table , i also > need > to make an entry in to the child table. > > How do i get the generated ID so that i can insert into the child table > with > this id as a foreign key. > > I am using IFXJDBC 3.0.JC3. > > Thanks, > Ganesh > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Hi Frank, Thanks for replying.Your suggestion works only when i get a connection using DriverManager.getConnection. When i try to get a connection from a datasource (Weblogic 8.1 connection pool) i get ClassCastException . I need to use Connection pool only. Can someone tell me if the following will work in all scenario. stmt.executeUpdate("insert into serialtable (serialcol1,dummycol2) values(0,'test')"); ResultSet rs = stmt.executeQuery("SELECT dbinfo('sqlca.sqlerrd1') FROM systables); int serialNumbergenerated = rs.getInt(1); dbinfo('sqlca.sqlerrd1') - Is this user session specific .So that with a single connection if i insert a value into serialtable and retrieve it using dbinfo() will always return the correct value Thanks, Ganesh

Yes dbinfo('sqlca.sqlerrd1') will always work and is session specific. It returns the last serial number assigned to any table inserted in that specific session, so it's best to call it immediately after the insert, but not strictly neccessary. Also note that if you insert multiple rows you MUST call the dbinfo after each one as other rollbacks and other users' inserts to that table will effect the actual value inserted so you cannot depend on backing out previous inserted values from the last one. Art S. Kagel ----- Original Message ----- From: Vijayakumar Ganesh Kumar <ids@iiug.org> At: 2/16 18:59:51 Hi Frank, Thanks for replying.Your suggestion works only when i get a connection using DriverManager.getConnection. When i try to get a connection from a datasource (Weblogic 8.1 connection pool) i get ClassCastException . I need to use Connection pool only. Can someone tell me if the following will work in all scenario. stmt.executeUpdate("insert into serialtable (serialcol1,dummycol2) values(0,'test')"); ResultSet rs = stmt.executeQuery("SELECT dbinfo('sqlca.sqlerrd1') FROM systables); int serialNumbergenerated = rs.getInt(1); dbinfo('sqlca.sqlerrd1') - Is this user session specific .So that with a single connection if i insert a value into serialtable and retrieve it using dbinfo() will always return the correct value Thanks, Ganesh ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.