This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Retry Failed Insert Attempt? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Retry Failed Insert Attempt?" Watch "Retry Failed Insert Attempt?" New topic
Author

Retry Failed Insert Attempt?

Aryeh Golob
Ranch Hand

Joined: Nov 12, 2006
Posts: 37
I am working on an application that sends large quantities of data over the Internet to a remote Mysql database. When I wrote the application and tested locally, everything worked fine & dandy, but when I ran it on remote database I began getting lots of "Communications link failure" errors.

I have managed to compensate for the vast majority of these errors by performing a validity check on the Connection before each insert is made.

Code ran before each update is run ...



Code to get connection in MysqlDatabaseProxy class ...



Validity check ...




Connection validation checks work 99% of the time, but I am still getting isolated link failure errors.

The reason for this is that even though the connection is validated before an insert is made, the connection can still fail on any particular insert.

Is there a way to perform a check on the success on an insert based on the occurrence of a link failure exception and then have the program try again?


Example exception thrown


Any advice will be greatly appreciated.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30136
    
150

Aryeh,
Why not check the SQL state on the actual insert as opposed to the connection? Then if you get the error one time - try again. For more than once give up so you don't have an infinite loop.

Also, have you considered batching the inserts so you have less trips to the remote database?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Aryeh Golob
Ranch Hand

Joined: Nov 12, 2006
Posts: 37
Thanks for the tips Jeanne,

I cannot check the result of the insert statement, because Sql link state error exception is thrown before I can get the status of the insert.

I am looking into re-architecting to accommodate batch inserts, but am nevertheless concerned about what happens if the link goes down in the middle, so I will take things step by step.

In the interim, I am now having all Connection's accessed through a globally accessible static method and am performing my validity/reconnect checks there.

I put the insert statements as well as exception handling inside a loop and reattempt a failed insert a set number of times before aborting.



Thanks again for the help.

-Aryeh
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retry Failed Insert Attempt?
 
Similar Threads
another JDBC driver question
Communications link failure with MySQL
My SQL connectionproblem
JDBC to connect to MYSQL
Trying again: SQLException: No suitable driver