Strange behavior of JDBC Adapter

[Former Member]

Hello everybody!

I found a pretty strange behaviour of JDBC Receiver Adapter during problems with network (big thanks to our Basis). According to the status in Adapter Engine, the message wasn't processed. In the database I can see these inserts. However, there are more lines in the XML than lines in the database. So, how it is possible that adapter commits only part of inserts. Logs and settings see below.

My XML includes a SQL_DML statement and a few INSERT statements, each contains many inserts.

It looks like that:

<?xml version="1.0" ?>
<MyMessage>
    <statement>
        <table1 action="SQL_DML">
            <access>here I delete old values</access>
        </table1>
    </statement>
    <statement>
        <table2 action="INSERT">
            <access>some data</access>
        </table2>
    </statement>
    <statement>
        <table3 action="INSERT">
            <access>some data</access>
        </table3>
    </statement>
</MyMessage>

Part of adapter log is here:

Information	Message successfully put into the queue
Information	The message was successfully retrieved from the receive queue
Information	Message status set to DLNG
Information	Delivering to channel: Adapter_Name
Information	MP: processing local module localejbs/CallSapAdapter
Information	Receiver JDBC adapter: processing started; QoS required: ExactlyOnce
Information	JDBC Adapter Receiver Channel Adapter_Name: Processing started; party / service BS
Information	Database request processed successfully
Error	MP: exception caught with message Ack handling failed: creation of messageFactory failed: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Connection JDBC_http://sap.com/xi/XI/System is currently not started.
Error	Exception caught by adapter framework: Ack handling failed: creation of messageFactory failed: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Connection JDBC_http://sap.com/xi/XI/System is currently not started.
Error	Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.engine.interfaces.messaging.api.exception.MessagingException: java.lang.Exception: Ack handling failed: creation of messageFactory failed: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Connection JDBC_http://sap.com/xi/XI/System is currently not started.
Information	Message status set to WAIT
Information	The asynchronous message was successfully scheduled to be delivered at ...
Information	Message status set to TBDL
Information	Retrying to deliver message to the application. Retry: 1
Information	The message was successfully retrieved from the receive queue
Information	Message status set to DLNG
Information	Delivering to channel: Adapter_Name
Information	MP: processing local module localejbs/CallSapAdapter
Information	Receiver JDBC adapter: processing started; QoS required: ExactlyOnce
Information	JDBC Adapter Receiver Channel Adapter_Name: Processing started; party / service BS
Error	Unable to execute statement for table or stored procedure. 'Table' (Structure 'statement') due to com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'fields'. Cannot insert duplicate key in object 'Table'. The duplicate key value is (field values).
Error	JDBC message processing failed; reason Error processing request in sax parser: Error when executing statement for table/stored proc. 'Table' (structure 'statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'fields'. Cannot insert duplicate key in object 'Table'. The duplicate key value is (field values).
Error	MP: exception caught with cause com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'Table' (structure 'statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'fields'. Cannot insert duplicate key in object 'Table'. The duplicate key value is (field values).
Error	Exception caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. 'Table' (structure 'statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'fields'. Cannot insert duplicate key in object 'Table'. The duplicate key value is (field values).
Error	Delivery of the message to the application using connection JDBC_http://sap.com/xi/XI/System failed, due to: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'Table' (structure 'statement'): com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'fields'. Cannot insert duplicate key in object 'Table'. The duplicate key value is (field values).

Adapter settings:

Maximum concurrency: 20

Persistence: Local

Conflict Resolution: Redo

Advanced Mode: On

Number of Retries of Database Transaction on SQL Error: 0

Transaction Isolation Level: serializable

[Former Member]

Dear All,

Follow the steps below to resolve the "Connection JDBC_http://sap.com/xi/XI/System is currently not started." issue:

1. Log in to NWA

2. Navigate to Operations -> Systems -> Start & Stop

3. Click on Java Services tab

4. Search  the service Service "XPI Adapter: JDBC"

5. Select the service XPI Adapter: JDBC and click on Restart button

With the restart of JDBC Service in NWA, the channels should start working properly

Regards,

Shreyansh

[former_member193306]

Good day!

We have same problem in our system!

I hope in this question will be answered!

[iaki_vila]

Hi Stanyslav,

Are you getting the exact same exception?, have you tried to check if you are really inserting the same register or the register to be inserted in the DB has any restriction like duplicate key restriction?

Regards,

[former_member193306]

Hi Iñaki,

I don't have any logs, because it happend once.

I remember that the problem was exactly the same.

Best Regards.

Stanislav.

[former_member184720]

Hi Stanyslav - What is your PI version. Can you check if the below note is applicable to you?

1283145 - JDBC message fails after Messaging System restarts.

[former_member193306]

Hello Hareesh!

Thank you for your reply.

But our version of PI is 7.3, and this note not relevant for us.

Best Regards.

Stanislav.