Skip site navigation (1) Skip section navigation (2)

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

From: "Chris Ingram" <cingram(at)intellisync(dot)com>
To: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Subject: Re: PATCH: Integrity constraint violation should set SQLSTATE to 23000
Date: 2005-10-03 22:04:17
Message-ID: FC98F2A5677766428EF728087FB0259193D1BA@mail-atl01.pumatech.com (view raw or flat)
Thread:
Lists: pgsql-odbc
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.

Attachment: sqlstate-from-backend.diff
Description: application/octet-stream (7.1 KB)

pgsql-odbc by date

Next:From: Greg CampbellDate: 2005-10-04 13:34:14
Subject: Re: [ODBC] Unbound text box, Text > 255 characters, MSAccess/PostgreSQL
Previous:From: AndrusDate: 2005-10-03 18:44:50
Subject: Get error message details

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group