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