Informix Inserts and JDBC 
Author Message
 Informix Inserts and JDBC

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/



Wed, 18 Jun 1902 08:00:00 GMT
 Informix Inserts and JDBC

Quote:

> 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.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



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. JDBC and Informix 256 character insert limit...

2. Informix JDBC: inserting/retrieving TEXT columns?

3. Informix JDBC: inserting/retrieving TEXT columns?

4. how to access informix and JDBC type 4 Driver for Informix

5. Java applets / Informix Connect / JDBC / Informix DS 7.22

6. JDBC insert failed

7. Pb to INSERT a record int the table with MS JDBC Beta 2 Drivers

8. Trunc error when inserting image through JDBC-ODBC Bridge in Java


 
Powered by phpBB® Forum Software