Re: PATCH: Integrity constraint violation should set SQLSTATE to 23000

Thanks for pointing out a much better way to address the problem, Dave.

I've attached a patch that uses the SQLSTATE from the backend any time
the error condition after executing a query is
STMT_ERROR_TAKEN_FROM_BACKEND. Besides solving the problem I reported,
this seems to make much more sense than setting the SQLSTATE arbitrarily
to HY000 "General Error" in this situation. I look forward to your
feedback.

--
Chris Ingram
Software Developer, Intellisync Corporation
cingram(at)intellisync(dot)com
www.intellisync.com

-----Original Message-----
From: Dave Page [mailto:dpage(at)vale-housing(dot)co(dot)uk]
Sent: Friday, September 30, 2005 12:15 PM
To: Chris Ingram; pgsql-odbc(at)postgresql(dot)org
Subject: RE: [ODBC] 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.
>
[...]
>
> 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.