Thread: Integrity constraint violation should set SQLSTATE to 23000

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

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Chris Ingram
> Sent: 30 September 2005 16:30
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Integrity constraint violation should set
> SQLSTATE to 23000
>
> 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_erro
> r_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?

Yes.

With the newer servers you should be able to use the PQresultErrorField
libpq function to look at PG_DIAG_SQLSTATE. I'm not sure that was an
option back in 02/2003.

I look forward to seeing your patch :-)

Regards, Dave.