• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Working with getNextException method of SQLException

 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First of all I'm using MySQL db and its corresponding driver for interacting with it.

I'm using batch inserts with auto commit. In case of many error records, the insertion continues even after it encounters an error row. So I have come across -3 static value for error records and 1 static value for records that are pass on to the db without any problem. (I have not encountered -2 yet, does MYSQL JDBC driver never returns -2 in the array?).

My questions are,

If there are two or more errors will invoking getNextException() give error information of all the errors? I noticed I have got only one. Will I get the stacktrace trace for only one culprit row? Am I missing some fundamentals of exception handling?

Ideally, I would want all the information so I can send everything to the caller.



My catch block code looks like below:



 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Handling exceptions in JDBC batches can be pretty tricky. Oracle, for example, recommends you to rollback the entire transaction if you encounter an error in JDBC batch.

MySQL Connector/J Developer Guide seems not to say anything specific about handling batch update errors, so the best documentation you have is JDBC javadoc (since the MySQL JDBC behavior isn't documented to any greater detail, you cannot assume that your findings in your current environment will be valid in different environments and/or versions of the driver or the database).

I, personally, would assume the worst after any error encountered and rollback the entire transaction. You should design your transactions so that the error handling only kick in in situations that truly cannot be anticipated.
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I feel your last paragraph did not sink in wholly. Would you rollback only when you run into RuntimeException or any of its subclasses?

What would you do in case an SQLException is thrown? Am I right in thinking any Error/Exception should be rollback transaction?
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Firing multiple insert statements with PreparedStatement#execeuteUpdate() did not exhibit behavior I wanted - it came out of the program on first exception. However, all statements should be sent to the db in view of my requirements.

Any thoughts?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Generally speaking, the transaction should be rolled back on any error encountered. When there is an error, you don't know what happened and in which state the transaction ended, so you can't commit it. There might be some very rare exceptions to this rule, but in any case you always need to be absolutely sure what you're doing (and what exactly happened) if you're committing after an error.

Ahsan Bagwan wrote:Firing multiple insert statements with PreparedStatement#execeuteUpdate() did not exhibit behavior I wanted - it came out of the program on first exception. However, all statements should be sent to the db in view of my requirements.

What are the requirement then?

It looks like you're trying to insert new records and silently skip those that already exist in the database. There are better ways to accomplish this, though. I'd suggest doing some search first, for example mysql ignore duplicate keys on insert.
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been vague in my responses, apologies! My use case dictates that, if for instance I get a Integrity violation exception on 2nd and 3rd statement of 5 overall statements then the code should carry on with the insertion of 4th and 5th statement. And also mandates I should collect the exception messages of 2nd and 3rd statements and propagate all 2 messages to the client.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What you should do is to check the integrity constrains upfront. This obviously leads to duplicate code, as the constraints are defined in the database and then checked again in the code, but this would allow you to do the checks closer to the user (in the application) and let him fix it when he typed it in, and not when he pressed a Submit button. And the duplication can be avoided if you write a code (or use some library) which would read the constraint definitions from the database and perform the checks generically. Not sure whether MySQL provides complete access to constraint definitions, but I'd be a bit surprised it if didn't. This will obviously pay off only where there are a lot of constraints to handle.

In a multiuser environments, some constraints might still fail in the database. For example, two users might try to insert a records with the same value of some unique column. The integrity check prior to insertion sees that the value is not yet in the database, but then both of them try to insert it and one of them fails. So you still need to handle collisions in the database; in a multiuser environment, if you don't want to use pessimistic locking, it is inevitable. What I would do would probably be to process every record in it's own transaction. If there was an SQL exception, I'd extract the message to show it to the user. If there was another exception, then I'd probably abort the rest of the processing, as you don't know what's happening really. You might still want to avoid SQL exceptions using MySQL's INSERT IGNORE, for example.
 
Ahsan Bagwan
Ranch Hand
Posts: 254
1
Java MySQL Database Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using Insert Ignore to suppress SQL errors seems like a sensible thing to do. Already the code feels way too convoluted.

Offtopic, ignoring SQL Exceptions would require convincing the team.

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ahsan Bagwan wrote:Using Insert Ignore to suppress SQL errors seems like a sensible thing to do. Already the code feels way too convoluted.

Please, make sure you understand what INSERT IGNORE does in MySQL. I haven't used it myself, but from what I've read it looks like it might be too broad for your requirements - you won't know why the insert failed (and subsequently won't be able to report the proper reason to the user).

Offtopic, ignoring SQL Exceptions would require convincing the team.

I certainly hope your team wouldn't budge on that issue. It really isn't a sensible option.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic