Reg JDBC UPDATE statement

[Former Member]

Hi,

As per my requirement i need to update a table in SQL server depending on unique id.

i have created the update structure but face a problem all the records are getting update with the first value of the payload data

like say

eg:

NAME:ABC

ID:3

NAME:GTH

ID:4

even for id 4 i am getting the value of ABC

kindly suggest what need to done i have even compareOperation too

[Former Member · Accepted solution]

Hello,

Did u checked ur mapping output?

In addition to that, set logSQLStatement = true under advanced mode in JDBC receiver adapter and check the query which is formed at runtime.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

All the settings in Advance mode and logSQLStatement is 'true'

here is the sample mapping payload

<access>

            <SAPDocNo>3100039047</SAPDocNo>

            <SAPDocupdtdate>2013-09-30 11:19:29.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

         <key>

            <orderid compareOperation="EQ">29740</orderid>

         </key>

[Former Member · Accepted solution]

Hello,

So did u check the update query in PI which is getting formed at runtime?

Thanks

Amit Srivastava

[iaki_vila · Accepted solution]

Hi Venkata,

You have used in the key statement this:

   <orderid compareOperation="EQ">29740</orderid>

The compareOperation="EQ" attribute is not needed.

Regards.

[Former Member · Accepted solution]

Hi Vila,

That was tried earlier but it failed then it made to use compareOperation="EQ"

[Former Member · Accepted solution]

Hello Venkat,

I think you should post the SQL statement for at least two records here. I also assume this is a mapping issue.

Regards,

Jörg

[Former Member · Accepted solution]

Hi Jorg,

Here is the payload

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

<ns1:MT_AOS_YWEB_DOYC_Ord xmlns:ns1="http://anc.com/mg/tally/YWEB_DOYCID_Order">

   <Statement>

      <YWEB_DOYC action="UPDATE">

         <table>tb_dyoc_order_details</table>

         <access>

            <SAPDocNo>3100039042</SAPDocNo>

            <SAPDocupdtdate>2013-10-30 12:18:29.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

         <access>

            <SAPDocNo>3100039043</SAPDocNo>

            <SAPDocupdtdate>2013-09-30 11:18:42.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

         <access>

            <SAPDocNo>3100039045</SAPDocNo>

            <SAPDocupdtdate>2013-09-30 11:19:06.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

         <access>

            <SAPDocNo>3100039046</SAPDocNo>

            <SAPDocupdtdate>2013-09-30 11:19:18.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

         <access>

            <SAPDocNo>3100039047</SAPDocNo>

            <SAPDocupdtdate>2013-09-30 11:19:29.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

         <key>

            <orderid compareOperation="EQ">29740</orderid>

         </key>

         <key>

            <orderid compareOperation="EQ">29742</orderid>

         </key>

         <key>

            <orderid compareOperation="EQ">29744</orderid>

         </key>

         <key>

            <orderid compareOperation="EQ">29748</orderid>

         </key>

         <key>

            <orderid compareOperation="EQ">297410</orderid>

         </key>

      </YWEB_DOYC>

   </Statement>

</ns1:MT_AOS_YWEB_DOYC_Ord>

[iaki_vila · Accepted solution]

Hi Venkata,

With that XML you are doing an UPDATE like UPDATE SET ... WHERE orderid = 29740 OR orderid = 29742 etc. Then you are updating all the records at same time, therefore the last valuie will be updated in the four registers pointed with the Where clausule. Try tro split the UPDATE xml in several Statements.

Regards.

[Former Member · Accepted solution]

Hi Venkat,

thanks.

You will need a separate <Statement> tag for each order update. That should solve your problem.

Regards,

Jörg

[Former Member · Accepted solution]

Hi Jorg,

In that case each <Statement> will have table and action associated with it and all will be part of single payload like

<Statement>

      <YWEB_DOYC action="UPDATE">

         <table>tb_dyoc_order_details</table>

         <access>

            <SAPDocNo>3100039042</SAPDocNo>

            <SAPDocupdtdate>2013-10-30 12:18:29.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

        <key>

            <orderid compareOperation="EQ">29740</orderid>

         </key>

<Statement>

<Statement>

      <YWEB_DOYC action="UPDATE">

         <table>tb_dyoc_order_details</table>

         <access>

            <SAPDocNo>3100039045</SAPDocNo>

            <SAPDocupdtdate>2013-10-30 11:18:29.0</SAPDocupdtdate>

            <GRNNumber/>

            <GRNDate/>

            <GRNupdtdate/>

            <SAPSalesOrdNo/>

            <SAPSORDupdtdate/>

            <TripsheetNo/>

            <Tripsheetdate/>

            <Tripsheetupdtdate/>

         </access>

        <key>

            <orderid compareOperation="EQ">29748</orderid>

         </key>

<Statement>

Will this not create a problem at the time of DB UPDATE operation

[ambrish_mishra · Accepted solution]

This will not create any problems during DB update. Thats the way it should work.

Ambrish

[Former Member · Accepted solution]

Thanks!  Jorg it worked.

[Former Member · Accepted solution]

Thinking about it you could also try to repeat the YWEB_DOYC tag in your statement. This would make it one database transaction while repeat of statement would create many single database transactions. I haven't worked with JDBC adapter for a while, but you could try both options and see what is better in your scenario.

Regards,

Jörg

[ambrish_mishra]

Hi Venkata,

You need to create multiple Statements based on source payload and create corresponding access tag and field names along with key values.

That should work!

Ambrish