How to avoid loosing data in PI sender JDBC adapter with StoredProcedure?

Hi!

I wonder how to avoid loosing data in PI sender JDBC adapter with StoredProcedure?

In Help.sap.com, there is a guideline for how to set up the JDBC sender adapter to call a StoredProcedure.

Link: http://help.sap.com/saphelp_nwpi71/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

You "Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement."

The "Update SQL Statement" is set to: <TEST> as recommended in the guideline.

Accordingly, the StoredProcedure itself updates the status of collected records as "fetched", so the same records/batch will not be picked again by SAP PI at next poll.

However, we have recently found out that in some situations the adapter runs into problems, and then it will assume it can do another Execute StoredProcedure to fetch the SAME records. Since the StoredProcedure has already updated the records as "fetched" the new "Execute" request will return a new "batch" of records. In these situation the first "batch" get lost...!

Shown below is the log from Adapter Engine where the sender JDBC adapter makes its mistake of asking for another "batch" (while thinking it will get the same batch again). It seems it actually asks for a new batch two times, e.g. three times in total, where to batches get lost...

Is there some other parameters in the sender JDBC adapter that can be set to avoid this error from occurring? I find the guideline linked above to be lacking, when it comes to explaining which parameters to use together with StoredProcedure.

Any help appreciated!

Thanks!

Øystein

Audit Log for Message: e878ce66-50b0-4026-08a9-f58c7a14f029

2013-10-10 08:30:19

Information

Channel JDBCSender_Xtrans: Executed Query successfully, confirmation skipped - data may be send again ("TEST" mode).

2013-10-10 08:30:19

Information

Send query result, size 742599 characters.

2013-10-10 08:30:28

Information

Channel JDBCSender_Xtrans: Executed Query successfully, confirmation skipped - data may be send again ("TEST" mode).

2013-10-10 08:30:28

Information

Send query result, size 800052 characters.

2013-10-10 08:30:28

Information

MP: entering1

2013-10-10 08:30:28

Information

MP: processing local module localejbs/CallSapAdapter

2013-10-10 08:30:28

Error

MP: exception caught with cause com.sap.engine.services.jndi.persistent.exceptions.NamingException: Exception during lookup operation of object with name localejbs/CallSapAdapter, cannot resolve object reference. [Root exception is javax.naming.NamingException: Error occurs while the EJB Object Factory trying to resolve JNDI reference Reference Class Name: Type: clientAppName Content: sap.com/com.sap.aii.af.app Type: interfaceType Content: local Type: ejb-link Content: CallAdapterWithMessageBean Type: jndi-name Content: CallSapAdapter Type: local-home Content: com.sap.aii.af.lib.mp.module.ModuleLocalHome Type: local Content: com.sap.aii.af.lib.mp.module.ModuleLocal java.lang.NullPointerException: at com.sap.engine.services.ejb3.runtime.impl.DefaultRemoteObjectFactory.resolveReference(DefaultRemoteObjectFactory.java:94) at com.sap.engine.services.ejb3.runtime.impl.EJBObjectFactory.getObjectInstance(EJBObjectFactory.java:129) at com.sap.engine.services.ejb3.runtime.impl.EJBObjectFactory.getObjectInstance(EJBObjectFactory.java:58) at com.sap.engine.system.naming.provider.ObjectFactoryBuilderImpl._getObjectInstance(ObjectFactoryBuilderImpl.java:77) at com.sap.engine.system.naming.provider.ObjectFactoryBuilderImpl.access$100(ObjectFactoryBuilderImpl.java:33) at com.sap.engine.system.naming.provider.ObjectFactoryBuilderImpl$DispatchObjectFactory.getObjectInstance(ObjectFactoryBuilderImpl.java:228) at javax.naming.spi.NamingManager.getObjectInstance(NamingManager.java:283) at com.sap.engine.services.jndi.implclient.ClientContext.lookup(ClientContext.java:413) at com.sap.engine.services.jndi.implclient.OffsetClientContext.lookup(OffsetClientContext.java:266) at com.sap.engine.services.jndi.implclient.OffsetClientContext.lookup(OffsetClientContext.java:286) at javax.naming.InitialContext.lookup(InitialContext.java:351) at javax.naming.InitialContext.lookup(InitialContext.java:351) at com.sap.aii.af.lib.util.ejb.FastEjbFactory.createEjbInstance(FastEjbFactory.java:83) at com.sap.aii.af.lib.util.ejb.FastEjbFactory.createEjbInstance(FastEjbFactory.java:51) at com.sap.aii.af.app.mp.ejb.ModuleProcessorBean.getModuleLocal(ModuleProcessorBean.java:403) at com.sap.aii.af.app.mp.ejb.ModuleProcessorBean.process(ModuleProcessorBean.java:272) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at com.sap.engine.services.ejb3.runtime.impl.RequestInvocationContext.proceedFinal(RequestInvocationContext.java:43) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:166) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatesTransition.invoke(Interceptors_StatesTransition.java:19) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Resource.invoke(Interceptors_Resource.java:71) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Transaction.doWorkWithAttribute(Interceptors_Transaction.java:38) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_Transaction.invoke(Interceptors_Transaction.java:22) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:189) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_StatelessInstanceGetter.invoke(Interceptors_StatelessInstanceGetter.java:16) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_SecurityCheck.invoke(Interceptors_SecurityCheck.java:21) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177) at com.sap.engine.services.ejb3.runtime.impl.Interceptors_ExceptionTracer.invoke(Interceptors_ExceptionTracer.java:16) at com.sap.engine.services.ejb3.runtime.impl.AbstractInvocationContext.proceed(AbstractInvocationContext.java:177) at com.sap.engine.services.ejb3.runtime.impl.DefaultInvocationChainsManager.startChain(DefaultInvocationChainsManager.java:133) at com.sap.engine.services.ejb3.runtime.impl.DefaultEJBProxyInvocationHandler.invoke(DefaultEJBProxyInvocationHandler.java:164) at $Proxy59_10000.process(Unknown Source) at com.sap.aii.adapter.jdbc.JDBC2XI.send(JDBC2XI.java:1176) at com.sap.aii.adapter.jdbc.JDBC2XI.invoke(JDBC2XI.java:627) at com.sap.aii.af.lib.scheduler.JobBroker$Worker.run(JobBroker.java:500) at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37) at java.security.AccessController.doPrivileged(Native Method) at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:152) at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:250) ]

2013-10-10 08:30:39

Information

Channel JDBCSender_Xtrans: Executed Query successfully, confirmation skipped - data may be send again ("TEST" mode).

2013-10-10 08:30:39

Information

Send query result, size 585594 characters.