Issue while insert and update data to DB tables

[Former Member]

Hello all,

i am having an issue while insert the data to DB table.

my scenario is DB1 to DB2. i had a sender channel with select query which fetches data from DB1 and inserts to DB2.

so the select query will fetch the records that were INSERTED to DB1 and records that were UPDATED to DB1 and needs to insert/update to DB2 table.

Now the issue is i am able to insert the records but not able toupdate the records to DB2 table due to primary key issue.

im message mapping

sender message type is as follows:

<src_message1>

----<row>

-------<fieldA>

-------<filedB>

-------<filedC>

Receiver message type as follows:

<trgt_message1>

----<STATEMENT_1>

----

---

<TABLE_NAME>

----

---

<ACTION> INSERT

----

---

<TABLE>

----

---

<ACCESS>

----

---

<field1> primary key

----

---

<field2>

----

---

<field3>

----

---

<field4>

----

---

<KEY>

----

---

<field1>

----

---

<field2>

----

---

<field3>

----

---

<field4>

my query in sender channel is : select filedA, filedB, filedC from test_table where createdate=sysdate or updatedate=sysdate

so it feteches the data from DB1 and inserting to DB2 but not updating the records to DB2 due to primarykey issue.

please suggest how to solve ....will it solve by using UPDATE_INSERT for action?

Best Regards,SARAN

[baskar_gopalakrishnan2]

>Now the issue is i am able to insert the records but not able toupdate the records to DB2 table due to primary key issue.

Primary key violation issue could be due to many reasons. One of the reasons might be you try to insert a record with primary key which is already exist. In this case , you might have to update or as your business requirement says..

>will it solve by using UPDATE_INSERT for action?

Yes, you can use UPDATE_INSERT

[Former Member]

Hi All,

Thanks for the quick reply.

Please let me know how to use UPDATE_INSERT in my target structure.

Will it be enough if i can change the ACTION from insert to UPDATE_INSERT. or do i need to any Key fields like...i need to map the primary key field under key tab?

Best regards,SARAN

[Former Member]

Refer this -http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Yes..you need to change the action to UPDATE_INSERT and provide the key for update.

You need to map the key field in <KEY> section.

Receiver message type as follows:
<trgt_message1>
-
<STATEMENT_1>
-
<TABLE_NAME>
-
<ACTION> INSERT
-
<TABLE>
-
<ACCESS>
-
<field1> primary key
-
<field2>
-
<field3>
-
<field4>
-
<KEY>
-
<field1>
-
<field2>
-
<field3>
-
<field4>

. Does all the 4 fields consistute the key combination?

[Former Member]

Hi Nagarjuna,

i have done the following changes to target mapping structure;

1. action as UPDATE_INSERT

2. in access tab, i had mapped fieldDate to field4.

3. in Key tab, i assigned the sysdate to field4.

but issue still exist. could you please check my above changes are correct or not. if wrong please provide me the details that needs to be done.

thanks in advance.

i'm providing the error details again:

my query in sender channel is : select filedA, filedB, filedC, FiledDate from TEST_TABLE where fieldDate=sysdate or updatedate=sysdate

it returns 4 records as follows:

fieldA--fieldB-fieldC---fieldDate

1001----EU---- 1----

---

2011-11-10

1002----CN---- 0----

---

2011-11-10

1003----AP---- 1----

---

2008-03-15 (already exist in DB2)

1004----JP---- 1----

---

2007-04-12 (already exist in DB2)

the first two records are created today and remaining 2 records are updated the fieldC from 0 to 1 ( in DB1 )

while inserting these 4 records to DB2, we get the following error "java.sql.SQLException: ORA-00001: unique constraint (data.TEST_TABLE_PK) violated" .

Best Regards,SARAN

[rodrigoalejandro_pertierr]

as i can see the system is executing and insert and not the upgrade for the updated records.

the cause of this error is:an UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

Action: Either remove the unique restriction

also may sure you define properly the structure. try to catch in the DB the query that PI is traying to execute.

also review this

    action=UPDATE_INSERT
The statement has the same format as for the UPDATE action. Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.
The response document has the following format; one of the two values is always 0 because either an UPDATE or an INSERT action is always executed:
<update_count>count</update_count>
<insert_count>count</insert_count>

Rgds

RP

Edited by: Rodrigo Alejandro Pertierra on Nov 10, 2011 12:46 PM

[Former Member]

Saran

if you use only field4 on tab key of the insert_update, this is not a unique key becouse as depicted in you example you have more than one record with the same date. Try to add all fields of the primary key in the tab key of the statement and all field you have to update in the access tab of the statement

[Former Member]

Hi Manmir,

Thanks for info. i was mapped all the fields in <KEY>. still i am not able to update data to DB tables.

now JDBC receiver channel status was green. but not updating the data to DB table.

Regards,SARAN

[Former Member]

Hi saran,

Take the query from the adapter logs ( hope you have enabled logSqlStatement in you Receiver CC).

execute the same query in the SQL editor.

This might help

Regards,

Krishnamoorthy

[Former Member]

Hi Saran

can you detail which is the error raised during the insert on db2?

Past the error description from the communication channel (RWB)

Obviously if the error indicate an error on insert due to duplicated key this means that db2 already contain a record with the same key, so you have to use INSERT_UPDATE statement

Manuel

Edited by: Manmir on Nov 9, 2011 5:35 PM

[rodrigoalejandro_pertierr]

take a look tothe first statements in this link (<dbTableName action=u201DUPDATEu201D | u201CUPDATE_INSERTu201D>)

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

try that an let us know.