JDBC Sender Adapter : java.sql.SQLException: Cursor state not valid.

[Former Member]

Hello all,

We have configured JDBC Sender Adapter which fetches around 10K records with poll interval 1hr from DB2 System .

It was working fine,suddenly it started throwing an exception in Adapter Monitoring :

Error during conversion of query result to XML: java.sql.SQLException: Cursor state not valid.

It is not fetching any records.

Without changing any configurations when we tried to fetch to around 1000 records it's working fine.

For 10K records same exception persists

What could be the reason ?How to resolve this issue?

regards

GangaPrasad

[Former Member]

Hi,

"invalid cursor state" often occurs when trying to access data from a resultset whereas you're not correctly positioned within this set (like missing row.hasNext()) ...

If you are using native XI adapter (no custom module or lookup in msg mapping), I'd suggest checking adapter log and traces (either from CC monitor or VA) for error messages.

Rgds

Chris

[Former Member]

Hello Christophe ,

Trace in VA :::

Date : 05/09/2008

Time : 11:45:57:750

Message : Unexpected error converting database resultset to XML, reason: java.sql.SQLException: Cursor state not valid.

at java.lang.Throwable.<init>(Throwable.java:194)

at java.lang.Exception.<init>(Exception.java:41)

at java.sql.SQLException.<init>(SQLException.java:40)

at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)

at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)

at com.ibm.as400.access.AS400JDBCResultSet.getValue(AS400JDBCResultSet.java:3580)

at com.ibm.as400.access.AS400JDBCResultSet.getString(AS400JDBCResultSet.java:3223)

at sun.reflect.GeneratedMethodAccessor459222074.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:309)

at com.sap.aii.adapter.jdbc.sql.jdbctrace.TraceInvocationHandler.invoke(TraceInvocationHandler.java:45)

at com.sap.aii.adapter.jdbc.sql.jdbctrace.$Proxy254.getString(Unknown Source)

at com.sap.aii.adapter.jdbc.JDBC2XI.convert2XML(JDBC2XI.java:954)

at com.sap.aii.adapter.jdbc.JDBC2XI.invoke(JDBC2XI.java:492)

at com.sap.aii.af.service.scheduler.JobBroker$Worker.run(JobBroker.java:475)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:99)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:119)

Severity : Error

Category : /Applications/ExchangeInfrastructure/AdapterFramework/Services/ADAPTER/ADMIN/JDBC

Location : com.sap.aii.adapter.jdbc.JDBC2XI.convert2XML(ResultSet, ResultSetMetaData)

Application :

Thread : XI JDBC2XI[JDBC_SND_DB2_VehicleReceiving/DB2PRD00/]_170

Datasource : 12428950:/usr/sap/PXI/DVEBMGS01/j2ee/cluster/server0/log/applications/com.sap.xi/xi.log

Message ID : 00145E742794005E0014980B000000BE00044CC763766C4F

Source Name : /Applications/ExchangeInfrastructure/AdapterFramework/Services/ADAPTER/ADMIN/JDBC

Argument Objs : java.sql.SQLException: Cursor state not valid.

at java.lang.Throwable.<init>(Throwable.java:194)

at java.lang.Exception.<init>(Exception.java:41)

at java.sql.SQLException.<init>(SQLException.java:40)

at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)

at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)

at com.ibm.as400.access.AS400JDBCResultSet.getValue(AS400JDBCResultSet.java:3580)

at com.ibm.as400.access.AS400JDBCResultSet.getString(AS400JDBCResultSet.java:3223)

at sun.reflect.GeneratedMethodAccessor459222074.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:309)

at com.sap.aii.adapter.jdbc.sql.jdbctrace.TraceInvocationHandler.invoke(TraceInvocationHandler.java:45)

at com.sap.aii.adapter.jdbc.sql.jdbctrace.$Proxy254.getString(Unknown Source)

at com.sap.aii.adapter.jdbc.JDBC2XI.convert2XML(JDBC2XI.java:954)

at com.sap.aii.adapter.jdbc.JDBC2XI.invoke(JDBC2XI.java:492)

at com.sap.aii.af.service.scheduler.JobBroker$Worker.run(JobBroker.java:475)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:99)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:119)

,

Arguments : java.sql.SQLException: Cursor state not valid.

at java.lang.Throwable.<init>(Throwable.java:194)

at java.lang.Exception.<init>(Exception.java:41)

at java.sql.SQLException.<init>(SQLException.java:40)

at com.ibm.as400.access.JDError.throwSQLException(JDError.java:389)

at com.ibm.as400.access.JDError.throwSQLException(JDError.java:366)

at com.ibm.as400.access.AS400JDBCResultSet.getValue(AS400JDBCResultSet.java:3580)

at com.ibm.as400.access.AS400JDBCResultSet.getString(AS400JDBCResultSet.java:3223)

at sun.reflect.GeneratedMethodAccessor459222074.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:309)

at com.sap.aii.adapter.jdbc.sql.jdbctrace.TraceInvocationHandler.invoke(TraceInvocationHandler.java:45)

at com.sap.aii.adapter.jdbc.sql.jdbctrace.$Proxy254.getString(Unknown Source)

at com.sap.aii.adapter.jdbc.JDBC2XI.convert2XML(JDBC2XI.java:954)

at com.sap.aii.adapter.jdbc.JDBC2XI.invoke(JDBC2XI.java:492)

at com.sap.aii.af.service.scheduler.JobBroker$Worker.run(JobBroker.java:475)

at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)

at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:99)

at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:119)

,

Dsr Component :

Dsr Transaction : d1f629d01d9b11dd984200145e742794

Dsr User :

Indent : 0

Level : 0

Message Code :

Message Type : 1

Relatives : com.sap.aii.adapter.jdbc.JDBC2XI

Resource Bundlename :

Session : 0

Source : /Applications/ExchangeInfrastructure/AdapterFramework/Services/ADAPTER/ADMIN/JDBC

ThreadObject : XI JDBC2XI[JDBC_SND_DB2_VehicleReceiving/DB2PRD00/]_170

Transaction : SAP J2EE Engine JTA Transaction : [0ffffffbdffffffa6ffffff960086]

User : J2EE_GUEST

Regards

Ganga Prasad

[prateek]

This error occurs when multiple threads are simultaneously java.sql.Connection object. When no of records is large in ur case, then due to multiple threads, this error starts occurring.

Try advance option "Disconnect from Database After Processing Each Message"

Regards,

Prateek

[Former Member]

Hi Prateek,

Advance Option is already set .Even after that error exists.

Regards

Gangaprasad

[Former Member]

Dear Prasad,

Does it happen "only" when processing multiple messages at the same time or only when amount of records returned is big (no matter how many msgs are being processed concurrently) ?

Though there are some articles regarding multi threading and java.sql.Connection, I think SAP XI JDBC adapter is thread safe ...

What is sql statement you're trying to process ?

Rgds

Chris

[Former Member]

Hi Chris,

Yes, this is happening only when amount of records are more.

When i process around 2000 records it is working fine.

Its a simple Select Statement which iam using for fetching records :

SELECT * FROM library.SEFVHRC where vhrstts = ' '

regards

Prasad

[Former Member]

Prasad,

Any other issues or errors found during this exception (question is whether it is triggered by some other technical pb, like connectivity issue or does it come from adapter logic itself) ? I now there is a way to activate debug mode for SQL adapter (so you will be able to see the internal process whithin the adapter) but I can't remember the OSS note describing this ...

I'm not used to DB2/AS400 JDBC technology, but there seems to be no notes related to your exception

Rgds

Chris