I am testing a Java servlet that accesses a MySQL database (MyISAM engine,
MySQL 4.0.15, running on Win XP). My program tries to insert a row into a
table but I am wary about a possible duplicate of the primary key so I check
for that; if it happens, the user is supposed to get a second chance to
enter a different key.
When I run this program and deliberately supply a duplicate key, I found
that I am getting an SQLState() of "S1009" and a vendor code of "1062". When
I look up the vendor code, the manual says
(http://dev.mysql.com/doc/mysql/en/Error-handling.html):
a.. Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry '%s'
for key %d
The message makes perfect sense for the condition but the SQLState that I'm
supposed to get is "23000", not "S1009". Furthermore, the manual doesn't
list SQLState "S1009" anywhere on that page. In fact, the only occurrence of
"S1009" in the entire manual is on an entirely different page, one
containing ODBC error codes. There, the ODBC error code "S1009" refers to a
null pointer, not a duplicate key value.
I'm using Java (JDBC Driver: mysql-connector-java-3.0.8-stable-bin.jar), not
ODBC so I'm baffled about why I'm getting an SQLState of "S1009". It would
make a lot more sense to me to get SQLState "23505" since it specifically
refers to a duplicate key (at least in DB2's implementation of it). Even
SQLState "23000" would make more sense than "S1009"; at least "23000" is the
right class of errors (all codes starting with "23" indicate integrity
errors of some kind), even if it doesn't specifically identify the exact
error in my case.
According to one manual I have:
SQLSTATE provides application programs with common codes for common error
conditions. Furthermore, SQLSTATE is designed so that application programs
can test for specific errors or classes of errors. The scheme IS THE SAME
FOR ALL DATABASE MANAGERS and is based on the proposed ISO/ANSI standard.
[emphasis added]
In other words, if I made the same mistake writing to a DB2 database as to a
MySQL database, I should expect to get the same SQLState for both DBMSes,
even though they are made by different vendors. When I attempt to insert a
duplicate in DB2, I get SQLState "23505": why aren't I getting "23505" when
I do the same thing in MySQL??
Issuing "S1009" for a duplicate key error seems to defeat the whole purpose
of having an SQLState, which was a vendor-neutral error code for database
access. Unless I'm completing missing the boat here, MySQL is not using
SQLState as the participating vendors intended. This certainly complicates
my life because now I have to have different error checking in programs that
run on MySQL than programs which run on other databases.
Can someone explain what is going on here? Have I just got a buggy JDBC
driver or has MySQL consciously chosen not to implement SQLStates the way
the ANSI SQL standard envisions? If the latter, why does the manual say that
Error 1062 corresponds to SQLState "23000" then?
Rhino
---
rhino1 AT sympatico DOT ca
"There are two ways of constructing a software design. One way is to make it
so simple that there are obviously no deficiencies. And the other way is to
make it so complicated that there are no obvious deficiencies." - C.A.R.
Hoare
