Re: Integrity constraint violation should set SQLSTATE to 23000

> -----Original Message-----
> From: pgsql-odbc-owner(at)postgresql(dot)org
> [mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Chris Ingram
> Sent: 30 September 2005 16:30
> To: pgsql-odbc(at)postgresql(dot)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.