multiple table commit in one transaction

[Former Member]

Hi ,

I have a issue with JDBC. I have about 5 tables to update which are related. So i am updating them as one set with multiple tables.

what i want is to commit to database once the first three tables are updated and then move on to the next 2 tables.

Is this possible

Regards

Nikhil

[bhavesh_kantilal]

Nikhil,

Possible with multiple statement level Tags.

1. Create one statement tag for the first set of commit. This will contain the Access level different structures - one for each table

2. Likewise create another statement level tag for the next 2 tables and so on.

Each set of queries in each statement are treated as one single transaction.

Regards

Bhavesh

[Former Member]

Hi Bhavesh ,

I did do this but what is happening is only the first table mentioned in the statement is getting inserted with a record and the other tables are not inserted with any record.

I remember reading it somewhere that we have to make some additional changes in the mode to accomodate these things.

Can you let me know if you have any idea about it

Thanks

Nikhil

[Former Member]

NIkhil,

Can you give the XML (may be with dummy values) after the mapping?

Regards,

Jai Shankar

[Former Member]

hi

the xml looks like below

<root>

<stmt1>

<table1 action = "update_insert">

<table> AAA</table>

<access>

-

---

-

---

</access>

<key>

-

---

</key>

</table1>

<table2 action = "update_insert">

<table> BBB</table>

<access>

-

---

-

---

</access>

<key>

-

---

</key>

</table2>

</stmt1>

I have to use some thing like this because to mentain it as one transaction so that if one table update fails the whole transaction is roled back.

If you have any idea please do let me know

Regards

Nikhil

[Former Member]

If it is different tables, then you got to have multiple statements. This will not be considered as separate transactions. If update fails in the first table, the update will fail in table 2 also and vice versa..

Eg:

<root>

<stmt1>

<table1 action = "update_insert">

<table> AAA</table>

<access>

-

---

-

---

</access>

<key>

-

---

</key>

</table1>

<b></stmt1>

<stmt2></b>

<table2 action = "update_insert">

<table> BBB</table>

<access>

-

---

-

---

</access>

<key>

-

---

</key>

</table2>

<b></stmt2></b>

</root>

Regards,

Jai Shankar

[Former Member]

Hi

This is where I am a bit confused.

Is a transaction at a messsage type level i.e multiple statements within a message type are considered as one transaction or is it at a statement level. because for me the data consistency is very imp. and also sequencing.

will this take care of it

Regards

Nikhil

[Former Member]

>>>because for me the data consistency is very imp. and also sequencing.

S. It will be taken care of.

Have you given it a try?

Regards,

Jaishankar

[Former Member]

Nikhil, I would suggest writing a stored proc and handle the commit logic you have described in your stored proc. XI will now call this stored proc rather than performing direct DML on the database. The only overhead i see is having to write a stored proc and maintain it.

I don't think we can be absolutely sure that the 3 tables have been successfully updated cos we cannot expect an Application ack from the database. So even if you split your mappings into two(one with 3 statement types and other with 2 statement types) and call them in Sequence from a ccBPM it maynot help.

Give a thought on these lines as well.

Regards

Saravana