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...
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 ]
Joined: Aug 06, 2004
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...
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.
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
Joined: Aug 06, 2004
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 ]
Joined: Sep 29, 2002
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.