1

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

kinopio
  • 21
  • 7

0 Answers0