Re: Information in SQLException

Bjorn Abelli wrote: 
"hombre" wrote...

I am using JDBC to connect to a MySQL database. The database has a table 'customer' with a primary key that is automatically generated by my program. There is another unique key (customerID) which has to be entered by the user.

I don't understand why you've chosen to use more than one unique key, as it sound to me as the latter would suffice...

When the user chooes a value for customerID that is already in use, I get an SQLException (SQLState
23000, detailMessage = 'Duplicate entry xy for key z'). 

How do I know that the SLQException is thrown because of
the duplicate customerID and not because of a duplicate
primary key or any other sort of SQL error. Do I have to
parse the detailMessage-String ? Is this string vendor independent ?


It's vendor dependent.

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/SQLException.html

If you don't have any *other* fields that can make that exact error, I guess that you should be able to use the method "getErrorCode()" on the SQLException.

I haven't MySQL installed at the moment, but it *should* give different codes for different errors:

 - 1022 for duplicates that is because of the primary key
 - 1062 for duplicates that *isn't* the primary key

http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

I believe the more "vendor-independent"[1] SQLState (which you can retrieve with "getSQLState()" ), isn't on that detailed level. SQLState 23000 only means "Integrity constraint violation", which is the state for even more errors that the two mentioned.

Try it, and tell us what happened... ;-)

// Bjorn A

[1] It's only enforced by those databases that conform to XOPEN conventions. 



Try some like this.. Could use either state or code checks

try {
      ..
      ..
      int updatedRowCount = sqlStatement.executeUpdate(sqlCmd);

      } catch (SQLException ex) {
       // handle any errors

        if  (ex.getSQLState()) == 23000 | ex.getErrorCode() == 1022){
             dialogToDisplayYourMessage();
       }

--


Thanks in Advance...
IchBin, Pocono Lake, Pa, USA http://weconsultants.servebeer.com/JHackerAppManager
__________________________________________________________________________

'If there is one, Knowledge is the "Fountain of Youth"'
-William E. Taylor,  Regular Guy (1952-)
.