Hi Andrei,
I forwarded your email to our internal list, but did you try to ask this
question on the postgresql site?
This reference I got last time:
PostgreSQL community maintains a large number of public mailing lists:
www.postgresql.org/community/lists
thanks,
-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