Problem after switch from 1.1 to 1.3.1

[mistrz_7]

Hello,

After switch in jboss eap 7.0 from jberet-core and jberet-suport from 1.1 to 1.3.1 I got error for simple etl (oracle to sqlserver):

JBERET000007: Failed to run job my_job, org.jberet.job.model.Chunk@4005bb0b: java.sql.SQLException: Invalid operation for forward only resultset : absolute

Before that every thing works well. I would like to use 1.3.1 version becuse it include jdbcBatch.

Regards

sw

[cfang]

If you have paid subscription for JBoss EAP, please go through Red Hat support channel.  Otherwise, you may want to open a JBeret JIRA with reproducer test app, and detailed exception/stacktrace.

This may be related to changes in JBERET-364 JdbcItemReader restart from the wrong checkpoint position … · jberet/jsr352@1edf9b1 · GitHub 

This related discussion may also be helpful: This method should only be called on ResultSet objects that are scrollable

[mistrz_7]

Hello,

Red hat have support only to jberet-core module.

I have made some tests and find out that problem is in jberet-suppot module.

If core=1.1 and support=1.1 it's ok.

if core=1.2 and support=1.2 it's ok.

if core=1.2 and suport=1.3.3 there is error.

Regards

sw

[cfang]

thanks for the investigation.  Please file a jberet JIRA issue with details (stacktrace, etc).  Have you tried setting the following property in jdbcItemReader?

<property name="resultSetProperties" value="resultSetType=TYPE_SCROLL_INSENSITIVE"/>

[mistrz_7]

Yes, I did. But no changes.

Regars

sw

[cfang]

That is odd.  This type of issues tend to depend on the jdbc driver.  I usually test and use with opensource databases and their jdbc drivers such as H2, MySQL, PostgreSQL, and haven't noticed this issue.  The workaround mentioned above has worked with db2.  I'll try some batch jobs with Oracle and mssql.

[cfang]

I created the following JBeret JIRA issue to track it:

1. JBERET-473

Invalid operation for forward only resultset when running jdbcItemReader with Oracle

[cfang]

However, after I added the jdbcItemReader property to specify the resultSetType to TYPE_SCROLL_INSENSITIVE, the test passed.

This is the complete section of my jdbcItemReader in job xml:

<reader ref="jdbcItemReader">

    <properties>

        <property name="sql" value="#{jobParameters['sql']}"/>

        <property name="url" value="#{jobParameters['url']}"/>

        <property name="user" value="#{jobParameters['user']}"/>

        <property name="password" value="#{jobParameters['password']}"/>

        <property name="beanType" value="java.util.Map"/>

        <property name="columnMapping" value="date, time, open, high, low, close, volumn"/>

        <property name="columnTypes" value="Date, String, Double, Double, Double, Double, Double"/>

        <property name="start" value="#{jobParameters['start']}"/>

        <property name="end" value="#{jobParameters['end']}"/>

        <property name="resultSetProperties" value="resultSetType=TYPE_SCROLL_INSENSITIVE"/>

    </properties>

</reader>

I was using jberet-support 1.3.3, Oracle 12c R1, Oracle thin driver ojdbc7_g.jar.  Can you check your job xml to make sure the resultSetType is set properly?

[mistrz_7]

Hello,

I get connection directly from servler via jndi like:

<property name="dataSourceLookup" value="java:/myOraDb" />

So probably this is reason that resultSetType setting doesn't help.

Regards

sw

[cfang]

the resultSetProperties batch property works regardless how you get the db connection.  It should work the same whether in jberet standalone or in WildFly / EAP.

I just ran an WAR app that uses jdbcItemReader connecting to Oracle.  Without this batch property, I was able to reproduce the same error.  After adding this property to job xml, everything works.

If you still have errors, please share your datasource configuration.  FYI, I configured it as follows:

<datasource jta="false" jndi-name="java:jboss/OracleDS" pool-name="OracleDS">

                    <connection-url>jdbc:oracle:thin:@xxx.redhat.com:1521:xxx</connection-url>

                    <driver>ojdbc</driver>

                    <security>

                        <user-name>xxx</user-name>

                        <password>xxx</password>

                    </security>

                </datasource>

                <drivers>

                    <driver name="ojdbc" module="com.oracle.ojdbc">

                        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

                    </driver>

                </drivers>

When inspecting the ResultSet, it shows it is scrollable, just as configured in job xml: