Fw: postgresql experts please help - Mailing list pgsql-jdbc

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.Vatkina@Sun.COM>
To: <persistence@glassfish.dev.java.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@postgresql.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
>