jdbc adapter issue

[sahana_ps]

hi all,

i have a target structure,which has two statement,

First Statement does an INSERT to database table and Second Statement does an UPDATE to database table ...

in source structure, if indicator field has value Y i need to INSERT those records in Database and if indicator has value X i need to UPDATE those records to Database

this is working fine when only insert or only update records are coming ,but if both insert and update records are present in a single run then it is either doing only insert or only update.

any anyone help on this issue....

thanks

[sahana_ps]

thanks all ....

its working now...Made some changes in mapping

[Senthilprakash1]

Hi Sahana,

What DB is used here?

1.Change Maximum concurrency to 1 in receiver channel

2.Change the receiver channel to perform Batch mode processing.

This is more to do with Transaction isolation  in the DB than in  PI.

for testing: use a DB client and create multiple statements (Insert and update) and execute them parallel and check weather the DB is getting inserted/updated properly.

Also you can have single structure and send the Action (Insert or Update) dynamically using mapping for each record.

or try having a SP.

Regards,

senthilprakash.

[rajasekhar_reddy14]

How you created your JDBC receiver atructure only one receiver message type which includes both insert and update?

If you want to perform both operational individually then try with two different mappings and write a condition in Interface determination but still i recommend to achieve this using one mapping.

[justin_santhanam]

Raja Sekhar Reddy wrote:
How you created your JDBC receiver atructure only one receiver message type which includes both insert and update?
If you want to perform both operational individually then try with two different mappings and write a condition in Interface determination but still i recommend to achieve this using one mapping.

Raja,

Why you need two mapping programs? Having two statements in single structure won't be sufficient?

Thanks,

Justin(Raj).

[rajasekhar_reddy14]

Hi Justin,

Performing two operations in single structure always good design to do that, but if it is not stable then going with two individual transactions right idea .

Regards,

Raj

[justin_santhanam]

Sahana,

What kind of structure are you using? Since you are using INSERT/ UPDATE on the action item I'm assuming you are using the XML structure instead of generating the SQL query on your own(UDF).

In the update structure , did you defined any Key attributes( ie - The key attributes will be converted to a where clause).

Thanks,

Justin(Raj)

[Former Member]

Hi,

Are  u doing this opearion on the single DB table??

If yes, then u can use "Update_Insert" action. So by using this u dont have to worry abt the source indicator field and DB will handle insert and update by its own..

From SAP help:

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

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

Thanks

Amit Srivastava

[sahana_ps]

thanks amit.

i tried this out...i am not getting any error when i use update_insert,but every time i execute it does only insert ,its not doing update ....

it is inserting and duplicating the already existing records......

[Former Member]

i doubt that ur update statemnt in not working fine...did u define the structure properly??

use "logSQLStatment" (under advanced mode) to chk the update query getting formed at runtime..

[baskar_gopalakrishnan2]

>>>this is working fine when only insert or only update records are coming ,but if both insert and update records are present in a single run then it is either doing only insert or only update.

Please check mapping logic  where if you have both (x and Y) indicators in source side, you map both the statements in the target structure. I suspect problem in the mapping area. You might be doing one at a time using if or else logic. You can also do a simple test in the mapping test tool and see whether you create two statements in target structure by giving x and y values in source structure.

If you do if /else function, then replace with if function for both

Question: Hopefully you insert or update in the same database of different tables. If so, no issue.

Hope that helps.

[sahana_ps]

hi gopalakrishnan,

i tested in message mapping with two records, passing x in one record and y in another....it does only update or only insert whichever i have passed in first record.i have used if without else function in mapping

my requirement is to do an insert and update to same table in same database...

rfc o/p wil send some data created in last 7 days i need to insert them to one table and the same rfc sends the data changed in last 7 days, i need to update the same database table fields for changed data

[baskar_gopalakrishnan2]

>i tested in message mapping with two records, passing x in one record and y in another....it does only update or only insert whichever i have passed in first record.i have used if without else function in mapping

Just want to make sure whether  you are able to see two statements with values in a target structure if  you get both x and y values from source in message mapping test. If you are able to get both statement in target output structure and only during runtime  it fails to do the second statement  then there is some problem. Otherwise still problem in mapping logic.

Another approach.  You can try creating below structure and use constant function in mapping to write query and map it to access tag. If you get x , write insert query in constant, y means write update query in constant. If you get both then write insert query followed by ; and  update query in constant and see how that helps. You can placeholder for the values passing, if you want.

refer sap help receiver jdbc document

http://help.sap.com/saphelp_nw70ehp1/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

This is just a thought.  Please share with us the results after testing.

      <anyName action=”SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

  <placeholder1>value1</placeholder1>

  <placeholder2>value2<placeholder2>

</key>

</anyName >