JDBC Table Update

[Former Member]

I working with IDoc->XI->JDBC to update a single table on an Oracle db.

The Idoc is a single repeating segment that inserts/updates 1 record per IDoc segment.

When I generate an IDoc with multiple segments, it only updates the record that corresponds with the first segment. I switched to a file receiver to see the format of the XML SQL and this is the result:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:CustomerSalesOrg_SAP_PHOENIX_JDBC_MT xmlns:ns0="urn:antioch-com:xi:Phoenix">

<STATEMENTNAME>

<Test action="UPDATE_INSERT">

<TABLE>CUSTOMERSALESORG</TABLE>

<access>

<DELETIONINDICATOR>X</DELETIONINDICATOR>

<CUSTOMERORDERBLOCK>90</CUSTOMERORDERBLOCK>

<CURRENCY>USD</CURRENCY>

<DELIVERINGPLANT>2000</DELIVERINGPLANT>

</access>

<key1>

<CUSTOMERID>0000200000</CUSTOMERID>

<SALESORG>1000</SALESORG>

<DISTCHNL>01</DISTCHNL>

<DIVISION>99</DIVISION>

</key1>

</Test>

<Test action="UPDATE_INSERT">

<TABLE>CUSTOMERSALESORG</TABLE>

<access>

<DELETIONINDICATOR> </DELETIONINDICATOR>

<CUSTOMERORDERBLOCK>93</CUSTOMERORDERBLOCK>

<CURRENCY>CAD</CURRENCY>

<DELIVERINGPLANT>5000</DELIVERINGPLANT>

</access>

<key1>

<CUSTOMERID>0000200001</CUSTOMERID>

<SALESORG>1000</SALESORG>

<DISTCHNL>01</DISTCHNL>

<DIVISION>99</DIVISION>

</key1>

</Test>

</STATEMENTNAME>

</ns0:CustomerSalesOrg_SAP_PHOENIX_JDBC_MT>

Seems to me this should be a pretty simple process.

[bhavesh_kantilal · Accepted solution]

Mike,

just to add to what I had mentioned earlier, the reason that you have to change occurence of STATEMENT to 0 to UB is that, the data between STATEMENT is connverted into a single SQL statement.

AS you need to perform MULTIPLE Insertions, you need MULTIPLE SQL Statements and so, just change Occurence of STATEMENT, create the STATEMENT tag for every row to be isnerted, and multiple insertions will happen

This is how your Structure should be,

<?xml version="1.0" encoding="UTF-8"?>

<ns0:CustomerSalesOrg_SAP_PHOENIX_JDBC_MT xmlns:ns0="urn:antioch-com:xi:Phoenix">

<b><STATEMENTNAME></b>

<Test action="UPDATE_INSERT">

<TABLE>CUSTOMERSALESORG</TABLE>

<access>

<DELETIONINDICATOR>X</DELETIONINDICATOR>

<CUSTOMERORDERBLOCK>90</CUSTOMERORDERBLOCK>

<CURRENCY>USD</CURRENCY>

<DELIVERINGPLANT>2000</DELIVERINGPLANT>

</access>

<key1>

<CUSTOMERID>0000200000</CUSTOMERID>

<SALESORG>1000</SALESORG>

<DISTCHNL>01</DISTCHNL>

<DIVISION>99</DIVISION>

</key1>

</Test>

<b></STATEMENTNAME>

<STATEMENTNAME></b>

<Test action="UPDATE_INSERT">

<TABLE>CUSTOMERSALESORG</TABLE>

<access>

<DELETIONINDICATOR> </DELETIONINDICATOR>

<CUSTOMERORDERBLOCK>93</CUSTOMERORDERBLOCK>

<CURRENCY>CAD</CURRENCY>

<DELIVERINGPLANT>5000</DELIVERINGPLANT>

</access>

<key1>

<CUSTOMERID>0000200001</CUSTOMERID>

<SALESORG>1000</SALESORG>

<DISTCHNL>01</DISTCHNL>

<DIVISION>99</DIVISION>

</key1>

</Test>

<b></STATEMENTNAME></b>

</ns0:CustomerSalesOrg_SAP_PHOENIX_JDBC_MT>

Regards,

Bhavesh

[Former Member]

That was it. Thanks for the assistance!

[bhavesh_kantilal]

hi mike ,

do the following change and it will solve your problem.

<b>Change the occurence of STATEMENT to 0 to UB,and access inside STATEMENT TO 1..1</b>

Next, STATEMENT should occur for every insertion.

In your case, you have given multiple access inside a single statement, create multiple statement with each statement that contains a single access.

Regards,

Bhavesh