Update or Insert statement in JDBC receiver

[Former Member]

Hello experts,

we are using Oracle 10g and now we got a JDBC scenario with simple XML-SQL with action "INSERT".

But I need to INSERT or UPDATE the data in table, is it possible and how?

[MichalKrawczyk · Accepted solution]

hi,

>>>But I need to INSERT or UPDATE the data in table, is it possible and how?

this is a standard function of the JDBC adapter

you just need to create a correct data type format (have a look at the first example on this page)

http://help.sap.com/saphelp_nw70/Helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

and then use action - UPDATE_INSERT

which will either insert or, if already there, update - your records

so exactly what you need

Regards,

Michal Krawczyk

[baskar_gopalakrishnan2]

You don't need to create stored procedure for the simple insert or update requirement. Create jdbc receiver structure wih action accordingly and do the insert and updates. If you have SQL statement that does both insert and update then you might want to use the below structure too.

<StatementName>
<anyName action=u201D SQL_DMLu201D>
<access>SQL-String with optional placeholder(s)</access>
<key>
  <placeholder1>value1</placeholder1>
  <placeholder2>value2<placeholder2>    
</key>
 </anyName > 
  </StatementName>

[former_member184681]

Hi Andrey,

In general, the best way to build some logic at the target database server is to create a stored procedure there. From your PI, you will call this procedure, passing the data that you want to insert or update in the database. And the stored procedure itself can perform some checks to determine whether the data should be inserted or updated. Most importantly, you will not need multiple DB calls from PI thanks to this.

You can learn more about benefits of stored procedures for instance here, if required:

http://databases.about.com/od/sqlserver/a/storedprocedure.htm

And a simple tutorial to stored procedures in Oracle:

http://docs.oracle.com/html/A95261_01/jdgtut1.htm

What's important, you can use Java code to implement stored procedures in Oracle, so you do not need to implement the logic in pure SQL.

Hope this helps,

Greg