This week's book giveaway is in the OCAJP 8 forum.
We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes catching errors on inserts into multiple tables Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of OCA Java SE 8 Programmer I Study Guide this week in the OCAJP 8 forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "catching errors on inserts into multiple tables" Watch "catching errors on inserts into multiple tables" New topic

catching errors on inserts into multiple tables

Tom Griffith
Ranch Hand

Joined: Aug 06, 2004
Posts: 275
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 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
Ranch Hand

Joined: Nov 19, 2002
Posts: 354

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
Ranch Hand

Joined: Aug 06, 2004
Posts: 275
Hello...aren't commits and rollbacks good only for EJBs?
Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42958
No, transactions can be used wherever JDBC is used.
Tom Griffith
Ranch Hand

Joined: Aug 06, 2004
Posts: 275
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
Ranch Hand

Joined: Aug 06, 2004
Posts: 275 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

Joined: Oct 27, 2005
Posts: 20136

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.

How To Ask Questions How To Answer Questions
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
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.

SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Tom Griffith
Ranch Hand

Joined: Aug 06, 2004
Posts: 275
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 about if the inserts, say the following...


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

Joined: Sep 29, 2002
Posts: 1683
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.
I agree. Here's the link:
subject: catching errors on inserts into multiple tables
It's not a secret anymore!