I'm running PostgreSQL 8.0 with version 08.01.0003 of the psqlodbclibpq
ODBC driver on Microsoft Windows Server 2003. When performing an insert
statement through ODBC that fails because it would cause a duplicate
primary key, I notice that the SQLSTATE is set to HY000 "General error"
when it should be set to 23000 "Integrity constraint violation" (see
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcodbc_error_codes.as
p). My application needs the correct SQLSTATE here, and I am willing to
submit a patch to correct this behavior.
In stepping through the code in the debugger, it looks like there may be
no way to tell that the SQL statement sent to the PostgreSQL backend
failed because of an integrity constraint violation other than the error
message it returns to the driver which is 'ERROR: duplicate key
violates unique constraint "pk_table1"'. An earlier discussion about
this (see
http://archives.postgresql.org/pgsql-odbc/2003-02/msg00051.php) seems to
result in the same conclusion. I'm tempted to modify the ODBC driver to
parse that error message text to see if it contains the substrings
"violates" and "constraint" (which appear in the foreign key constraint
error message as well), and set the SQLSTATE to 23000 if it does, but
I'm concerned that this might not be language neutral; I don't know if
the PostgreSQL backend might return localized error message text for
different languages. There is code in the ODBC driver that looks at the
first part of the message to see if it is "FATAL" in CC_send_query() in
connection.c.
Is there a better way to determine the cause of the error than parsing
the error message text returned from the backend? Does the backend ever
return localized error messages?
--
Chris Ingram
Software Developer, Intellisync Corporation
cingram@intellisync.com
www.intellisync.com
