File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Retry Failed Insert Attempt? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Retry Failed Insert Attempt?" Watch "Retry Failed Insert Attempt?" New topic

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
author & internet detective

Joined: May 26, 2003
Posts: 33113

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?

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, 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.

I agree. Here's the link:
subject: Retry Failed Insert Attempt?
It's not a secret anymore!