aspose file tools*
The moose likes JDBC and the fly likes Handling SQL Exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Handling SQL Exception" Watch "Handling SQL Exception" New topic
Author

Handling SQL Exception

jeff rusty
Ranch Hand

Joined: Nov 07, 2006
Posts: 109
hi,

I am currently doing a bulk upload in my aplication where i have to upload atleast 30,000 records with 88 data fields in to several tables of my application. The data is uploaded through an excel file and now i need to validate the data for datatype,maxlength,Foreignkey relationship. if had to do this for all the 30,000 records before i insert or update them in to my database,it will take huge amount of time so i thought of inserting the records and show the user what is the error when an sql exceptionoccurs.
So is it possible to identitfy which column is causing the SQLEception so that i can alert the user with a meaningful error messgage...
Is there is a different to handle this or should i do the validation through my javacode and once all the validation is passed then insert it which is time consuming when record count increases.
Please help me in identify a best way to address this issue.

thanks
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

It is not always a good idea to pre-validate Data Model constrains, because there is no 100% guarentee that you will catch them in your app. all before they happen. FOr example, if you check a field is unique then insert data something else may have added data after your check but before your insert.


So is it possible to identitfy which column is causing the SQLEception so that i can alert the user with a meaningful error messgage...


If your indices have understandable names then yes. The SQLException will just return the error message the database generates. So if for example you have a field called name, and it have a unique index called name_unique_index it should be fairly obvious what the error is if the contraint is violated. Of course if you index is called ghg343h4_ux or something this is less likely to be helpful.

If your data model is not immediately obvious to the user, for example if you have an Excel column called "First Name" and this goes in a field called person_n1 you might need to add some sort of mapping layer. You can parse the error message the SQLException includes and use this to provide something more meaningful.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
jeff rusty
Ranch Hand

Joined: Nov 07, 2006
Posts: 109
hi ,

Thanks for your reply. How can i identify the column name incase of an dataType error or in overflow(data exceeding max length specified in database) cases.


Thanks,
Premanth
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Most databases will specify the column when a data truncation error occurs. If they don't, you will have to pre-validate. This is a safer check, since DDL changes will be far less likely than DML changes.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Handling SQL Exception