Help Need for UPDATE INSERT on DATABASE

[Former Member]

Hello All,

I am working on file to jdbc interface. First time for transaction i need to insert data into DataBase. If same transaction for next time i need to update database, not append and insert which is having two columns as primary key. I given action item "UPDATE_INSERT" for First time successfully insert data into databse when next time for same transaction(update) i am getting error. can anyone guide me how to resolve this problem.

Thanks & Regards

Venkat K

[Former Member · Accepted solution]

Hi Venkat,

What error message are you getting while updating the db ?

Thanks,

Pooja

[Former Member]

Hi Venkat,

Just wanted to make sure that you are following the following structure for your receiver type :

<StatementName1>

<dbTableName action=u201DUPDATEu201D | u201CUPDATE_INSERTu201D>

    <table>realDbTableName</table>

<access>

   <col1>val1</col1>

   <col2>val2new</col2>

</access>

<key1>

    <col2>val2old</col2>

    <col4>val4</col4>

</key1>

<key2>

    <col2>val2old2</col2>

</key2>

</dbTableName>

  </StatementName1>

Also Keep in mind for UPDATE: If there is no <key> element, or if there is a <key> element but it is empty, then no condition is specified and the entire table is to be updated. This may not be permitted by the configuration of the JDBC adapter for security reasons and will therefore result in an error during message processing and an appropriate error message.

Thanks,

Pooja

[Former Member]

Hi Venkat

Whats the error you got.?

More over check the values you have under processing tab of CC. Set it properly while doing UPDATE_INSERT based on your requirement.

Thanks

Gaurav

[Former Member]

Hi

Adapter updating properly as expected for top table (order header). We are getting error for below table(orderitem).

I got the following Error Message in sxmb_moni:-

com.sap.engine.interfaces.messaging.api.exception.Messaging Exception: Error processing request in sax parser: Error when executing statement for table/stored proc. 'ORDERITEM' (structure 'QuoteItems'): java.sql.SQLException: ORA-00001: unique constraint (SALESORDER.PKORDERITEM) violated.

My Input strucute:

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

- <ns0:GDataStore_MT xmlns:ns0="http://sales.ibm.com/sapsalesorder">

- <Quote>

- <GDS_Quote action="UPDATE_INSERT">

<table>ORDERHEADER</table>

- <access>

<SOURCEQUOTEID>10022</SOURCEQUOTEID>

<SOURCECUSTOMERNAME>PITest21</SOURCECUSTOMERNAME>

<SOURCECUSTOMERID>MIR2146</SOURCECUSTOMERID>

<CREATEDATE>11-Jan-2008</CREATEDATE>

<STATUS>1</STATUS>

<CANCELLEDDATE>11-Jan-2008</CANCELLEDDATE>

<SOURCECOMMENT>none</SOURCECOMMENT>

<SOURCECONTACTID>MIR2146</SOURCECONTACTID>

</access>

- <key>

<SOURCEQUOTEID>10022</SOURCEQUOTEID>

</key>

</GDS_Quote>

</Quote>

- <QuoteItems>

- <GDS_QuoteItem action="UPDATE_INSERT">

<table>ORDERITEM</table>

- <access>

<SOURCEQUOTEID>10022</SOURCEQUOTEID>

<SOURCEQUOTELINEITEM>00010</SOURCEQUOTELINEITEM>

<SOURCEPRODUCT>17</SOURCEPRODUCT>

<SOURCEQUANTITY>5</SOURCEQUANTITY>

<SOURCEUNITOFMEASURE>PC</SOURCEUNITOFMEASURE>

</access>

- <access>

<SOURCEQUOTEID>10022</SOURCEQUOTEID>

<SOURCEQUOTELINEITEM>00020</SOURCEQUOTELINEITEM>

<SOURCEPRODUCT>18</SOURCEPRODUCT>

<SOURCEQUANTITY>6</SOURCEQUANTITY>

<SOURCEUNITOFMEASURE>PC</SOURCEUNITOFMEASURE>

</access>

- <key>

<SOURCEQUOTEID>10022</SOURCEQUOTEID>

<SOURCEQUOTELINEITEM>00010</SOURCEQUOTELINEITEM>

</key>

- <key>

<SOURCEQUOTEID>10022</SOURCEQUOTEID>

<SOURCEQUOTELINEITEM>00020</SOURCEQUOTELINEITEM>

</key>

</GDS_QuoteItem>

</QuoteItems>

</ns0:GDataStore_MT>

Thanks & Regards,

Venkat

[Former Member]

Hi

I think adapter is trying to insert new record and not updating it. or either you got same value in primary key field So unique constraint violation happened.

java.sql.SQLException: ORA-00001: unique constraint (SALESORDER.PKORDERITEM) violated.

Check with the payload. did you got insert update successful for any record?

Thanks

Gaurav

[Former Member]

HI Venkat,

Try to send only one <access> for the line item . When I looked at the structure format, it seems that you can only provide one <access> and multiple <keys> for a table update.

Try this :

<QuoteItems> 
<GDS_QuoteItem action="UPDATE_INSERT"> 
<table>ORDERITEM</table> 

<access> 
<SOURCEQUOTEID>10022</SOURCEQUOTEID> 
<SOURCEQUOTELINEITEM>00010</SOURCEQUOTELINEITEM> 
<SOURCEPRODUCT>17</SOURCEPRODUCT> 
<SOURCEQUANTITY>5</SOURCEQUANTITY> 
<SOURCEUNITOFMEASURE>PC</SOURCEUNITOFMEASURE> 
</access>

<key> 
<SOURCEQUOTEID>10022</SOURCEQUOTEID> 
<SOURCEQUOTELINEITEM>00010</SOURCEQUOTELINEITEM> 
</key>


</GDS_QuoteItem>
</QuoteItems>
</ns0:GDataStore_MT>

Thanks,

Pooja