• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

catching errors on inserts into multiple tables

 
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 354
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 275
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello...aren't commits and rollbacks good only for EJBs?
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No, transactions can be used wherever JDBC is used.
 
Tom Griffith
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 275
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Sheriff
Posts: 22781
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 275
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic