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

Re: Integrity constraint violation should set SQLSTATE to 23000

From: "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk>
To: "Chris Ingram" <cingram(at)intellisync(dot)com>,<pgsql-odbc(at)postgresql(dot)org>
Subject: Re: Integrity constraint violation should set SQLSTATE to 23000
Date: 2005-09-30 16:14:30
Message-ID: E7F85A1B5FF8D44C8A1AF6885BC9A0E4CC3091@ratbert.vale-housing.co.uk (view raw or flat)
Thread:
Lists: pgsql-odbc
 

> -----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.

pgsql-odbc by date

Next:From: Hajo KirchhoffDate: 2005-10-01 07:45:38
Subject: Re: postgresql-odbc vs. unixODBC
Previous:From: lothar.behrensDate: 2005-09-30 15:57:51
Subject: Re: select count(*) datatype ?

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