Information in SQLException - JDBC JAVA

"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/...Exception.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/...es-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.

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/...Exception.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/...es-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-)

IchBin wrote:
> 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/...Exception.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/...es-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();
> }
>
Sorry..
if (ex.getSQLState()) == 23000 && ex.getErrorCode() == 1022){
--


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-)

> IchBin wrote:
>> Bjorn Abelli wrote:
>>> "hombre" wrote...

>>>> 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 ?

[on getErrorCode]

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

>> 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();
>> }
> Sorry..
> if (ex.getSQLState()) == 23000 && ex.getErrorCode() == 1022){


SQLState 23000 also catches what the OP didn't want to catch, duplicate
primary keys.

ErrorCode 1022 definitely catches what the OP didn't want to catch,
duplicate primary keys.

It should suffice with:

if (ex.getErrorCode() == 1062)

But that will still only work with MySQL, and if the table doesn't have any
more unique fields than the primary key and CustomerID.

// Bjorn A

Bjorn Abelli wrote:
>> IchBin wrote:
>>> Bjorn Abelli wrote:
>>>> "hombre" wrote...
>
>>>>> 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 ?
>
> [on getErrorCode]
>
>>>> - 1022 for duplicates that is because of the primary key
>>>> - 1062 for duplicates that *isn't* the primary key
>
>>> 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();
>>> }
>> Sorry..
>> if (ex.getSQLState()) == 23000 && ex.getErrorCode() == 1022){
>
>
> SQLState 23000 also catches what the OP didn't want to catch, duplicate
> primary keys.
>
> ErrorCode 1022 definitely catches what the OP didn't want to catch,
> duplicate primary keys.
>
> It should suffice with:
>
> if (ex.getErrorCode() == 1062)
>
> But that will still only work with MySQL, and if the table doesn't have any
> more unique fields than the primary key and CustomerID.
>
> // Bjorn A
>
>
Sorry Bjorn, I need to slow down before I reply..

--


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-)

Bjorn Abelli schrieb:
> "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...

I want that the primary key never changes and has no meaning except to
uniquely identify the customer. It is not visible to the user. Changing
a primary key is difficult, because it would be necessary to find all
entities that have dependencies on the primary key.
But I also want to have a customer-ID that is visible to the user. The
format of the customer-ID can be choosen by the user (suppose that a
user wants to import existing customers that have already a customer-ID
assigned). The only resctriction is that the customer-ID is unique. When
the users decides at a later time to change the customer-ID (e.g.
appending the birthdate), then this can be done very easy.

Hombre

IchBin schrieb:
> Bjorn Abelli wrote:
>
>>> IchBin wrote:
>>>
>>>> Bjorn Abelli wrote:
>>>>
>>>>> "hombre" wrote...
>>
>>
>>>>>> 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 ?
>>
>>
>> [on getErrorCode]
>>
>>>>> - 1022 for duplicates that is because of the primary key
>>>>> - 1062 for duplicates that *isn't* the primary key
>>
>>
>>>> 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();
>>>> }
>>>
>>> Sorry..
>>> if (ex.getSQLState()) == 23000 && ex.getErrorCode() == 1022){
>>
>>
>>
>> SQLState 23000 also catches what the OP didn't want to catch,
>> duplicate primary keys.
>>
>> ErrorCode 1022 definitely catches what the OP didn't want to catch,
>> duplicate primary keys.
>>
>> It should suffice with:
>>
>> if (ex.getErrorCode() == 1062)
>>
>> But that will still only work with MySQL, and if the table doesn't
>> have any more unique fields than the primary key and CustomerID.
>>
>> // Bjorn A
>>
>>
> Sorry Bjorn, I need to slow down before I reply..
>
The following lines catch what I want:
catch( SQLException e ) {
if (e.getSQLState() == "23000" && e.getErrorCode() == 1062)

But I am not happy with this solution because I have to make code
modifications when I switch to another database. I wonder why there is a
SQL99 or XOPEN standard that can't be used in practice.

Thanks,
Hombre

hombre wrote:
> IchBin schrieb:
>> Bjorn Abelli wrote:
>>
>>>> IchBin wrote:
>>>>
>>>>> Bjorn Abelli wrote:
>>>>>
>>>>>> "hombre" wrote...
>>>
>>>
>>>>>>> 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 ?
>>>
>>>
>>> [on getErrorCode]
>>>
>>>>>> - 1022 for duplicates that is because of the primary key
>>>>>> - 1062 for duplicates that *isn't* the primary key
>>>
>>>
>>>>> 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();
>>>>> }
>>>>
>>>> Sorry..
>>>> if (ex.getSQLState()) == 23000 && ex.getErrorCode() == 1022){
>>>
>>>
>>>
>>> SQLState 23000 also catches what the OP didn't want to catch,
>>> duplicate primary keys.
>>>
>>> ErrorCode 1022 definitely catches what the OP didn't want to catch,
>>> duplicate primary keys.
>>>
>>> It should suffice with:
>>>
>>> if (ex.getErrorCode() == 1062)
>>>
>>> But that will still only work with MySQL, and if the table doesn't
>>> have any more unique fields than the primary key and CustomerID.
>>>
>>> // Bjorn A
>>>
>>>
>> Sorry Bjorn, I need to slow down before I reply..
>>
> The following lines catch what I want:
> catch( SQLException e ) {
> if (e.getSQLState() == "23000" && e.getErrorCode() == 1062)
>
> But I am not happy with this solution because I have to make code
> modifications when I switch to another database. I wonder why there is a
> SQL99 or XOPEN standard that can't be used in practice.
>
> Thanks,
> Hombre

Well the standard applies to the language and not vendor generated
messages based on there DBMS implementation. As a DBMS vendor they can
have different feature that they need to add their own nomenclatures of
messages. This will never be standardized. At least it has not been in
my 28 years of computer field. If you have worked with Oracle and than
most any other database you would understand.

There is a way to fix your error message problem. Have an external
errorcode.properties file that you can map\change to what you want based
on a different database. This way you do not have to change code. On you
DB.open perform method\function to fetch these changeable error codes.
But then you could just change the If statement for the hard-coded
codes.


--


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-)

"IchBin" wrote...
> hombre wrote:

>> The following lines catch what I want:
>> catch( SQLException e ) {
>> if (e.getSQLState() == "23000" && e.getErrorCode() == 1062)
>>
>> But I am not happy with this solution because I have to
>> make code modifications when I switch to another database.
>> I wonder why there is a SQL99 or XOPEN standard that can't
>> be used in practice.

It can, if the vendors and developers follow the standards "by the letter".

Fortunately (or unfortunately, depending on the view), they don't.

SQL is to a large degree built upon Codd's relational algebra, and to a
large degree formed by the use of the "normal forms" (even though they could
be discussed much as such).

I believe the SQLState 23000 never was thought of as anything else than to
enforce a proper use of "integrity constraints", and with the "normal forms"
in mind, there actually shouldn't be more than one unique key in one single
table.

E.g. if you wan't to change the value of a primary key, AFAIK it's possible
to do so, and still keep the integrity towards child tables, through the use
of cascading foreign keys.

But all that just serves as a possible explanation to why the SQLStates are
too "broad" for explicit situations.

> Well the standard applies to the language and not vendor
> generated messages based on there DBMS implementation.
> As a DBMS vendor they can have different feature that
> they need to add their own nomenclatures of messages.
> This will never be standardized. At least it has not
> been in my 28 years of computer field. If you have
> worked with Oracle and than most any other database
> you would understand.

I would say that Oracle has improved in that respect in the last years, as
it now conforms a bit better to the standard than before. I was pretty
excited when they finally decided to implement outer joins in the standard
way...

Unfortunately, their eagerness to conform to standards has also broken a lot
of Java code. When they decided to implement TIME and TIMESTAMP according to
the SQL standard, they also decided to map DATE to java.sql.Date, instead of
java.sql.TimeStamp as they did before...

// Bjorn A