cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC - Rollback

justin_santhanam
Active Contributor
0 Kudos

Dear Friends,

I hope you are all doing great! I have some issues with the JDBC Adapter Rollback operations. If anyone had any solutions, please share the same. I'll really appreciate it.

Issue:

For example, let say I'm framing/generating 10 INSERT SQL Statements and sending it to Legacy system. Consider first 5 sql statements went thru successfully and the 6th SQL statement has the Duplicate key and it got failed in the adapter engine, which was correct as I expected.

Since it got failed, the data which got inserted has to be rollbacked, is it possible in communication channel settings?

raj.

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

I don't think the previous inserted data can be rollacked by handling through communication channel monitoring. You need to write a procedure to achieve this functionality.

Thanks

amit

justin_santhanam
Active Contributor
0 Kudos

Amit,

I'm not sending each Insert SQL Statements separately. All the 10 SQL Statements or sent to legacy as single message, I mean 10 Sql queries in single message. If anyone of them failed, the rest of the stuff has to be rollbacked. I'm sure it's possible, but I don't know how to handle it in Communication Channel settings.

raj.

Former Member
0 Kudos

Hi Raj,

Can you please test it again. Upto my knowledge if we send multiple statements as single bunch XI will insert all the records and if their is an erro in any of those it will rollback all the data.

I may be wrong.

I had a scenarion when i was updating 5 different tables using a 5 different queries , whenever error was coming in one tbale XI was rollbacking data for all tables. We were using Oracle database.

Kulwinder

Reward if helpful

justin_santhanam
Active Contributor
0 Kudos

Kulwinder,

Thanks for your reply! I'm testing it for so many days, I'm sure the data is not rollbacked.

raj.

Former Member
0 Kudos

Hi Raj,

It might be possible that database autocommit is on because of which the data is commited in your case whereas in Kulwinder case it was off and was handled by XI. Please give a try

thanks

amit

bhavesh_kantilal
Active Contributor
0 Kudos

Hello

When the JDBC adapter converts the JDBC XML to a SQL statement, each of the STATEMENT level tag is treated as a single block of statement which executes as all or none.

In your case if you are inserting into the same table, instead of having multiple STATEMENT level tags, you should have multiple ACCESS level tags and in this case if one of the insertions fails, all of these would be rolled back.

In your current model I guess you have multiple statement level tags and hence this is not rolling the same back.

Regards,

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Hello

When the JDBC adapter converts the JDBC XML to a SQL statement, each of the STATEMENT level tag is treated as a single block of statement which executes as all or none.

In your case if you are inserting into the same table, instead of having multiple STATEMENT level tags, you should have multiple ACCESS level tags and in this case if one of the insertions fails, all of these would be rolled back.

In your current model I guess you have multiple statement level tags and hence this is not rolling the same back.

Regards,

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Hello

When the JDBC adapter converts the JDBC XML to a SQL statement, each of the STATEMENT level tag is treated as a single block of statement which executes as all or none.

In your case if you are inserting into the same table, instead of having multiple STATEMENT level tags, you should have multiple ACCESS level tags and in this case if one of the insertions fails, all of these would be rolled back.

In your current model I guess you have multiple statement level tags and hence this is not rolling the same back.

Regards,

Bhavesh

justin_santhanam
Active Contributor
0 Kudos

Bhavesh,

You are correct, I'm not using multiple Access for each query. Instead I'm using Multiple Statements for each query. I will try to change my structure and will give you an update shortly.

I really appreciate your help!

raj.

VijayKonam
Active Contributor
0 Kudos

Hi,

table, Access and Key all are at the same level under dbTableName Node. I believe it must be this node which should repeat since access node is related to only that table node.

refer: http://help.sap.com/saphelp_nw04/helpdata/en/e2/cb610b230c9c4cb4b800989196b63a/frameset.htm

VJ

justin_santhanam
Active Contributor
0 Kudos

Bhavesh,

It doesn't seems to be working. Now I'm not getting any errors in the adapter engine. But the data is not consitent.

If I issue the following statements

INSERT 'ABC'

INSERT '1234' --> Duplicate Record

DELETE 'ABC'

then in adapter Engine is showing success, it has to show error and it must revert back the inserted 'ABC' right, but it's not!

Am I doing/missing something wrong?

raj.