catching errors on inserts into multiple tables

[Tom Griffith]

Hello. if anybody has a minute, this is probably more of a SQL conceptual thing rather than JDBC (although that's the vehicle) but I've been kinda messing around with it. If I am inserting sequentially into multiple tables with subsets of data from a single record of origin...say...

Insert A,B,C into Table1;

Insert D,E,F into Table2;

is there a way to try to take the "sequential" out of it...my worry comes from possible data corruption if the first insert (A,B,C) goes through but the second insert (D,E,F) throws an overflow say.

If the data is to remain in the tables, I guess I could always do a SELECT to check if the subset data is already loaded into that particular table if the error is resolved and the original record is reentered into the insert queue...or better yet store a field or something in the original record, say a list of the successfuly inserted table names, and just reference that field on a reload...

...but...if there is a stored procedure or something continually processing/cleaning out Table1 and Table2, there could be problems with partial loads of records on the destination side. Thats sorta my major hitch with this...has anybody dealt with something like this...i don't really see anything that can be done. With a single table, no problem, the insert fails and nothing goes...but this multiple table thing...

welp, thank you for reading this.

[Abhinav Srivastava]

If you want to commit all or rollback all, you should make the two inserts part of one transaction.
[ January 16, 2008: Message edited by: Abhinav Srivastava ]

[Tom Griffith]

Hello...aren't commits and rollbacks good only for EJBs?

[Ulf Dittmer]

No, transactions can be used wherever JDBC is used.

[Tom Griffith]

Hi Ulf...i was just googling and it's looking that way to me...I'll keep digging into it. Thank you guys.
[ January 16, 2008: Message edited by: Tom Griffith ]

[Tom Griffith]

Hello...one of the things I notice is that a set number of prepared statements have to all be lined up within the same scope or whatever before committing them all. The code i'm messing around with dynamically creates a prepared statement inside a loop, executes the insert, then wipes out and rebuilds the same preparedstatement object on the next loop run. Essentially, it's a single preparedstatements/insert within a loop of n iterations but each loop overwrites the single preparedstatement object and inserts.

I was trying to build this app to be as generic and customizable as possible...explaining the n preparedstatements/inserts which works against lumping a preset number of preparedstatements in the code in a transaction. I'll have to mess with it because transactons mean that each loop will need to retain the preparedstatement in memory. Hmm, maybe i can store them in a collection of something...then unbundle the collection of prepared statements in a transaction and commit it. Have to tinker with it...i think the parameters would get hosed. Thank you guys...

[Rob Spoor]

There's an easy way with JDBC:
1) call setAutoCommit(false) on your Connection
2) do whatever you need to do
3) call commit() or rollback() on your Connection
4) close the Connection (or call setAutoCommit(true))

There is just one problem with most drivers I used: you must close each (Prepared)Statement before opening another one.

[Roger Chung-Wee]

I think it will be OK to create a PreparedStatement and reuse it in each loop. You would be doing something like this.

1. Get Connection
2. Create PreparedStatement
3. Disable autocommit <<-- this starts the transaction
4. Do inserts in loops
5. Commit <<-- this ends the transaction
6. Close Connection <<-- this also closes PreparedStatement

You could recreate a PreparedStatement at the start of each loop. If so, you must close the PreparedStatement at the end of the loop or risk a cursor leak.

[Tom Griffith]

Hello...thanks for the input everybody, I'm still messing around with the looping structure but as I fiddle with it, i think I'm getting how the transactions work. It's almost as if setting autocommit to false means hold all the SQL statements in memory, without the coder having to do it (similar to container managed beans). I hpe thats it. Anyways, I've been having another thought...how about if the inserts, say the following...

INSERT(A,B,C)
INSERT(D,E)
INSERT(F,G,H,I,J)

are to three different instances? This would require three seperate Connection object references, right?..and thus they couldn't be set to a single transaction...is that right? Thank you again for all the info.
[ January 18, 2008: Message edited by: Tom Griffith ]

[Roger Chung-Wee]

Unless you are using distributed transactions, a transaction cannot span Connections because a transaction is associated with a single Connection. Just get a single Connection, disable the auto-commit, do the inserts and commit. If SQLException is thrown, catch it and rollback. It's that simple.