I need to iterate over ScrollableResults, this is actually performed on a cron, this reads the information of a table (Informix DB) around 100000 records and saves some information on another table of another DB (Oracle 11g), but past 30 minutes it throws two exceptions; first "exception clearing maxRows/queryTimeout java.sql.SQLException: 'Statement' already closed" and then "SQL Error: -79878, SQLState: IX000 ResultSet not open, operation 'next' not permitted. Verify that autocommit is OFF".
Jboss : jboss-as-7.1.1 setting up default-timeout
<subsystem xmlns="urn:jboss:domain:transactions:1.1">
<core-environment>
<process-id>
<uuid/>
</process-id>
</core-environment>
<recovery-environment socket-binding="txn-recovery-environment" status-socket-binding="txn-status-manager"/>
<coordinator-environment default-timeout="10800"/>
Java JDK : 1.6
Hibernate 3.6.7
Spring 3.0.7 setting up defaultTimeout
<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager">
<property name="defaultTimeout" value="10800" />
<property name="allowCustomIsolationLevels" value="true" />
</bean>
public void getArticulos(Consumer<Articulos> consumer) throws SQLException {
StatelessSession session = null;
try {
session = getSessionFactory().openStatelessSession();
session.connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
Criteria criteria = session.createCriteria(getPersistentClass());
ScrollableResults results = criteria.scroll(ScrollMode.FORWARD_ONLY);
int cantidad = 0;
while (results.next()) {
Articulo item = (Articulo) results.get()[0];
amount ++;
LOG.debug(amount + " articulos");
consumer.accept(item, cantidad);
}
consumer.finish();
} finally {
if (session != null) {
try {
session.close();
} catch (Exception e) {
LOG.error("Unexpecting Error",
e);
}
}
}
}
I expect it to read and process all rows without issues