Fw: postgresql experts please help

Dear PostgreSQL jdbc experts,

I created a table with identity:
CREATE TABLE MAN (ID SERIAL, NAME VARCHAR(40), PRIMARY KEY (ID))
then through jdbc connection inserted a row into the table,
then attempted to select pk value back through the same jdbc connection
using currval - that failed with "not yet defined in this session"
exception.

What's wrong here?

Please take a look at the test case and results below.

Thanks a lot,

Andrei

----- Original Message -----
From: "Marina Vatkina" <Marina(dot)Vatkina(at)Sun(dot)COM>
To: <persistence(at)glassfish(dot)dev(dot)java(dot)net>
Sent: Wednesday, October 17, 2007 6:57 PM
Subject: Re: postgresql experts please help

> Andrei,
>
> This is what I got so far:
>> I actually don't see the problem here. All of what they're getting is
>> the behavior I would expect.
>
> They suggested to contact pgsql-jdbc(at)postgresql(dot)org to get more people to
> look at the problem.
>
> Regards,
> -marina
>
> Andrei Ilitchev wrote:
>> I am trying to implement support for both Identity and sequenceObjects on
>> the same database platform.
>> For some reason currval doesn't work for me: called right after insert it
>> throws exception indicating that nextval hasn't yet been called in this
>> session -
>> and yet insert is successfull: the pk value has been inserted.
>> I am using
>> Database: PostgreSQL Version: 8.2.5
>> Driver: PostgreSQL Native Driver Version: PostgreSQL 8.2 JDBC3 with SSL
>> (build 505)
>> Here's the JDBC test:
>> public void testJdbcSequencing() throws java.sql.SQLException {
>> Accessor accessor =
>> this.getServerSession().getConnectionPool("default").acquireConnection();
>> java.sql.Connection conn = accessor.getConnection();
>> try {
>> java.sql.PreparedStatement pstmt0 =
>> conn.prepareStatement("select ID FROM MAN");
>> java.sql.ResultSet resultSet = pstmt0.executeQuery();
>> System.out.println("MAN before insert: ");
>> while(resultSet.next()) {
>> System.out.print(resultSet.getInt(1) + "; ");
>> }
>> resultSet.close();
>> System.out.println();
>> java.sql.PreparedStatement pstmt1 = conn.prepareStatement("INSERT INTO
>> MAN (NAME) VALUES (null)");
>> int nInserted = pstmt1.executeUpdate();
>> pstmt1.close();
>> System.out.println("Inserted rows: " + nInserted);
>> resultSet = pstmt0.executeQuery();
>> System.out.println("MAN after insert: ");
>> while(resultSet.next()) {
>> System.out.print(resultSet.getInt(1) + "; ");
>> }
>> resultSet.close();
>> pstmt0.close();
>> System.out.println();
>> System.out.println("Attempt: select currval(\'MAN_ID_seq\')");
>> resultSet = null;
>> java.sql.PreparedStatement pstmt2 =
>> conn.prepareStatement("select currval(\'MAN_ID_seq\')");
>> try {
>> resultSet = pstmt2.executeQuery();
>> } catch (java.sql.SQLException ex) {
>> ex.printStackTrace();
>> pstmt2.close();
>> // throw ex;
>> }
>> if(resultSet != null) {
>> System.out.print("select currval: ");
>> while(resultSet.next()) {
>> System.out.println(resultSet.getString(1));
>> }
>> resultSet.close();
>> pstmt2.close();
>> }
>> System.out.println("Now attempt: select
>> nextval(\'MAN_ID_seq\')");
>> resultSet = null;
>> java.sql.PreparedStatement pstmt3 =
>> conn.prepareStatement("select nextval(\'MAN_ID_seq\')");
>> try {
>> resultSet = pstmt3.executeQuery();
>> } catch (java.sql.SQLException ex3) {
>> ex3.printStackTrace();
>> pstmt3.close();
>> // throw ex;
>> }
>> if(resultSet != null) {
>> System.out.print("select nextval: ");
>> while(resultSet.next()) {
>> System.out.println(resultSet.getString(1));
>> }
>> resultSet.close();
>> pstmt3.close();
>> } System.out.println("Now again attempt: select
>> currval(\'MAN_ID_seq\')");
>> resultSet = null;
>> java.sql.PreparedStatement pstmt4 =
>> conn.prepareStatement("select currval(\'MAN_ID_seq\')");
>> try {
>> resultSet = pstmt4.executeQuery();
>> } catch (java.sql.SQLException ex4) {
>> ex4.printStackTrace();
>> pstmt4.close();
>> // throw ex;
>> }
>> if(resultSet != null) {
>> System.out.print("select currval: ");
>> while(resultSet.next()) {
>> System.out.println(resultSet.getString(1));
>> }
>> resultSet.close();
>> pstmt4.close();
>> } } finally {
>>
>> this.getServerSession().getConnectionPool("default").releaseConnection(accessor);
>> }
>> }
>> And here's result:
>> MAN before insert:
>> Inserted rows: 1
>> MAN after insert:
>> 2;
>> Attempt: select currval('MAN_ID_seq')
>> org.postgresql.util.PSQLException: ERROR: currval of sequence
>> "man_id_seq" is not yet defined in this session
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
>> at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)
>> at
>> oracle.toplink.testing.tests.cmp3.advanced.AdvancedJunitTest.testJdbcSequencing(AdvancedJunitTest.java:246)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
>> at java.lang.reflect.Method.invoke(Method.java:585)
>> at junit.framework.TestCase.runTest(TestCase.java:154)
>> at junit.framework.TestCase.runBare(TestCase.java:127)
>> at junit.framework.TestResult$1.protect(TestResult.java:106)
>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>> at junit.framework.TestResult.run(TestResult.java:109)
>> at junit.framework.TestCase.run(TestCase.java:118)
>> at junit.framework.TestSuite.runTest(TestSuite.java:208)
>> at junit.framework.TestSuite.run(TestSuite.java:203)
>> at junit.extensions.TestDecorator.basicRun(TestDecorator.java:22)
>> at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
>> at junit.framework.TestResult.runProtected(TestResult.java:124)
>> at junit.extensions.TestSetup.run(TestSetup.java:23)
>> at junit.swingui.TestRunner$16.run(TestRunner.java:623)
>> Now attempt: select nextval('MAN_ID_seq')
>> select nextval: 216
>> Now again attempt: select currval('MAN_ID_seq')
>> select currval: 216
>> Thanks a lot!
>> Andrei
>