ORA-01002: fetch out of sequence error

[springuser]

I am getting a java.sql.SQLException: ORA-01002: fetch out of sequence while trying to do a refresh, basically select for update? How do I set autocommit to false? Any help is appreciated.

byte[] buffer = new byte[1];
buffer[0] = 1;
email.setEmailattachment(Hibernate.createBlob(buff er));
Long id = (Long)session.save(email);
System.out.println("id " + id);

getHibernateTemplate().refresh(email, LockMode.UPGRADE); //error here..

[Rod Johnson]

You must set autcommit to false on your connection pool/datasource.

[springuser]

Thanks for your reply. How do I set autocommit to false in Spring/Hibernate?

[Colin Sampaleanu]

It depends on the datasource or pool. For example, with dbcp, there is an option described here:

http://jakarta.apache.org/commons/db...iguration.html

[springuser]

Thanks for your reply. I dont want to make any global settings to set the autocommit to false. I would like to do something within the method where I would be making a call to "getHibernateTemplate().refresh(email, LockMode.UPGRADE);".

Like would like to something like this:
setAutoCommit(false);
getHibernateTemplate().refresh(email, LockMode.UPGRADE);
setAutoCommit(true);

How do I accomplish this using spring/hibernate?

Thanks for your help.

[Colin Sampaleanu]

I should mention that for DataSourceTrnasactionManager, Spring automatically sets autocommit to false, when you enter a transaction, and for HibernateTransactionManager, Hibernate iteself does it for the same net effect. For JTA situations, most JTA Datasource providers do the same thing.

Are you sure you're even in a transaction?

[springuser]

we do use HibernateTransactionManager for transaction.

<!-- Transaction manager for a single Hibernate SessionFactory
<bean id="myTransactionManager" class="org.springframework.orm.hibernate.Hibernate TransactionManager">
<property name="sessionFactory"><ref local="mySessionFactory"/></property>
</bean>

[Colin Sampaleanu]

If you turn up your logging so you see debug messages, you should see the logging from Hibernate that it is actually turning off the auto-commit.

You might want to look at the log to verify that you are in fact in a transaction, and then Hibernate is actually turning off the auto-commit. If both of these are true, then I don't think you're getting the out-of-sequence error because of the auto-commit setting.

[springuser]

Below is the stacktrace in the log file.

313 [26 Aug 2004 10:32:37,156] [main] WARN org.springframework.jdbc.support.SQLErrorCodeSQLEx ceptionTranslator - Unable to translate SQLException with errorCode '6502', will now try the fallback translator
0 [26 Aug 2004 10:58:28,890] [main] DEBUG com.xxx.util.SpringBeanLocator - bean factory created
16 [26 Aug 2004 10:58:28,906] [main] DEBUG com.xxx.persistence.DAOLocator - DAO locator created
31 [26 Aug 2004 10:58:28,921] [main] DEBUG com.xxx.dao.hibernate.EmailDAOHibernateImpl - save email
188 [26 Aug 2004 10:58:29,078] [main] DEBUG net.sf.hibernate.SQL - select hibernate_sequence.nextval from dual
297 [26 Aug 2004 10:58:29,187] [main] DEBUG net.sf.hibernate.SQL - insert into EMAILS (EMAILFROM, EMAILTO, EMAILBODY, EMAILSUBJECT, EMAILATTACHMENT, EMAIL_ID) values (?, ?, ?, ?, ?, ?)
328 [26 Aug 2004 10:58:29,218] [main] DEBUG net.sf.hibernate.SQL - select EMAIL_ID, EMAILFROM, EMAILTO, EMAILBODY, EMAILSENT, EMAILCC, EMAILBCC, EMAILSUBJECT as EMAILSUB8_, SMTPHOST, EMAILATTACHMENT as EMAILAT10_, EMAILATTACHMENTTYPE as EMAILAT11_, EMAILATTACHMENTFILENAME as EMAILAT12_ from EMAILS where EMAIL_ID =? for update
360 [26 Aug 2004 10:58:29,250] [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 1002, SQLState: 72000
360 [26 Aug 2004 10:58:29,250] [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-01002: fetch out of sequence

375 [26 Aug 2004 10:58:29,265] [main] WARN net.sf.hibernate.util.JDBCExceptionReporter - SQL Error: 1002, SQLState: 72000
375 [26 Aug 2004 10:58:29,265] [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - ORA-01002: fetch out of sequence

375 [26 Aug 2004 10:58:29,265] [main] ERROR net.sf.hibernate.util.JDBCExceptionReporter - could not load: [com.xxx.bean.Email#127]
java.sql.SQLException: ORA-01002: fetch out of sequence

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.j ava:1198)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery( OracleStatement.java:2400)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.execute Query(OraclePreparedStatement.java:527)
at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeQuery(DelegatingPreparedStatement.java:91 )
at net.sf.hibernate.impl.BatcherImpl.getResultSet(Bat cherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader .java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java :269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializ eNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.j ava:911)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.j ava:931)
at net.sf.hibernate.loader.SimpleEntityLoader.load(Si mpleEntityLoader.java:58)
at net.sf.hibernate.persister.EntityPersister.load(En tityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.refresh(SessionI mpl.java:2183)
at org.springframework.orm.hibernate.HibernateTemplat e$7.doInHibernate(HibernateTemplate.java:259)
at org.springframework.orm.hibernate.HibernateTemplat e.execute(HibernateTemplate.java:176)
at org.springframework.orm.hibernate.HibernateTemplat e.refresh(HibernateTemplate.java:257)
at com.csc.exes.dao.hibernate.EmailDAOHibernateImpl.s ave(EmailDAOHibernateImpl.java:82)
at com.csc.exes.dao.hibernate.EmailDAOHibernateImplTe st.testSave(EmailDAOHibernateImplTest.java:52)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154 )
at junit.framework.TestCase.runBare(TestCase.java:127 )
at junit.framework.TestResult$1.protect(TestResult.ja va: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:2 08)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at com.intellij.rt.execution.junit2.IdeaJUnitAgent.do Run(IdeaJUnitAgent.java:57)
at junit.textui.TestRunner.start(TestRunner.java:172)
at com.intellij.rt.execution.junit.TextTestRunner2.st artRunnerWithArgs(TextTestRunner2.java:23)
at com.intellij.rt.execution.junit2.JUnitStarter.prep areStreamsAndStart(JUnitStarter.java:97)
at com.intellij.rt.execution.junit2.JUnitStarter.main (JUnitStarter.java:31)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main (AppMain.java:78)

[Colin Sampaleanu]

You haven't included enough of the log to see any of the transaction stuff from Spring, or the autocommit modification, from Hibernate...