Rollback of multiple SQL statements

[Former Member]

Hello,

is it possible to have a transaction that includes more than one JDBC statement using the JDBC receiver?

Example:

One inbound service interface contains two SQLstatements: Delete and Insert. The delete should be rolled back in case the insert fails. So either all JDBC statements should be executed or none of them.

Can this be achieved?

Edited by: Guppenberger Florian on Feb 21, 2011 6:45 PM

[Former Member]

Best solution for this is to write stored procedure. but If you don't want to write stored procedure, then you can handle this at mapping level. (not best solution but you can do)

You can do insert, delete in database by writing UDF. In UDF, you can write your own logic to connect to the database.

In UDF, you can do whatever you want to do. Lets say , insert failed then you can also rollback delete statement using UDF.

At the receiver side, you can just create temp file to complete the scenario.

[baskar_gopalakrishnan2]

>> is it possible to have a transaction that includes more than one JDBC statement using the JDBC receiver?

Yes, you can do one statement insert and another statement delete and third statement update... Basically you have to create seperate statement tag and set action attribute accordingly in the target data type.

>>>One inbound service interface contains two SQLstatements: Delete and Insert. The delete should be rolled back in case the >>insert fails. So either all JDBC statements should be executed or none of them.Can this be achieved?

Since you relate the insert call with delete, i.e if insert fails then delete should be rolled back and one action rely on other. In this case rather creating separate statement xml tag for each, you can do Stored Procedure.

To do stored procedure refer this link...

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

Also follow this syntax for the jdbc data type creation

<StatementName5>

<storedProcedureName action=u201D EXECUTEu201D>

<table>realStoredProcedureeName</table>

<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName5>

[Former Member]

>

> Hello,
> 
> is it possible to have a transaction that includes more than one JDBC statement using the JDBC receiver?
> 
> Example:
> 
> One inbound service interface contains two SQLstatements: Delete and Insert. The delete should be rolled back in case the insert fails. So either all JDBC statements should be executed or none of them.
> 
> Can this be achieved?
> 
> Edited by: Guppenberger Florian on Feb 21, 2011 6:45 PM

Try with a stored procedure