Re: error 23000 is not treated in psqlODBC

"For example. you could have MS SQL Server and PostgreSQL servers
that have exactly the same data. Using ODBC, your Windows application
would make exactly the same calls and the back-end data source would
look the same (to the Windows application)." - PostgreSQL 7.2
Documentation - chap. 7. ODBC Interface

This should happen when I use my system. It works with MySQL and MS SQL
Server, so it would work with PostgreSQL also, without any change. The
problem I found is that psqlODBC do not validate all the error codes
specified by ODBC. The error I related should return "23000/S1010" acording
to ODBC.

Seeking psqlODBC sources, I found some of this informations/errors were
treated at environ.c, under a 'case' where most of the psqlODBC errors were
treated. Following this logic, this is where I should insert my code:

case ER_DUP_KEY:
pg_sqlstate_set(env, szSqlState, "23000", "S1010");
// key already exists
break;

This would return the error code I need to treat qhe the user tries to
insert a duplicated key.

But there is also another file where some other errors are defined
(statement.h)
where I should put:

#define ER_DUP_KEY 35 //35 is a sequence where all error that need to be
treated
//have (just add 1 to the sequence).

I found that info.c and statement.c also treat som errors, maybe I have to
code
something here?

Can anyone help me? I have also a program to test the error. If anyone
wants...

TIA,
Ricardo.

Support wrote:
>
> "For example. you could have MS SQL Server and PostgreSQL servers
> that have exactly the same data. Using ODBC, your Windows application
> would make exactly the same calls and the back-end data source would
> look the same (to the Windows application)." - PostgreSQL 7.2
> Documentation - chap. 7. ODBC Interface
>
> This should happen when I use my system. It works with MySQL and MS SQL
> Server, so it would work with PostgreSQL also, without any change. The
> problem I found is that psqlODBC do not validate all the error codes
> specified by ODBC. The error I related should return "23000/S1010"
> acording to ODBC.
>
> Seeking psqlODBC sources, I found some of this informations/errors were
> treated at environ.c, under a 'case' where most of the psqlODBC errors
> were treated. Following this logic, this is where I should insert
> my code:
>
> case ER_DUP_KEY:
> pg_sqlstate_set(env, szSqlState, "23000", "S1010");
> // key already exists
> break;
>
> This would return the error code I need to treat qhe the user tries to
> insert a duplicated key.

Unfortunately PostgreSQL doesn't support error code at
backend side and it's very hard for clients to detect
error details such as *key already exists*.
There may be a way where we analyze the error message
but I won't do it.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Dear Gurus,

As of v7.4, PostgreSQL supports error codes. I'd like to know if we may hope
for a version of psqlODBC that handles error codes.

I tried with PostgreSQL 7.4.1, ODBCExpress 6.0 (dated 2003 Oct) and psqlodbc
7.3.1.0 (dated 2003 May 15) and got the following (I assume useless) answer.

Also tried psqlodbc 7.3.2.4 (dated 2003 Nov 12) and the SQLGetDiagRec
function, with the same result.

Message = "ERROR: update or delete on \"cikk\" violates foreign key
constraint \"$1\" on \"szallitolevel_tetele\""
Native = 7
SQLState = "HY000"

AFAIK these error codes say something like "general error" in ODBC terms :)
This so-called state, "HY000" is not even an SQL state, unless I'm fatally
mistaken. It's some kind of ODBC state, isn't it?

Awaiting your kind response,

G.
%----------------------- cut here -----------------------%
\end

----- Original Message -----
From: "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp>
To: "Support" <support(at)insyde(dot)com(dot)br>
Cc: <pgsql-odbc(at)postgresql(dot)org>
Sent: Tuesday, February 11, 2003 2:29 AM
Subject: Re: [ODBC] error 23000 is not treated in psqlODBC

> Support wrote:
> >
> > "For example. you could have MS SQL Server and PostgreSQL servers
> > that have exactly the same data. Using ODBC, your Windows application
> > would make exactly the same calls and the back-end data source would
> > look the same (to the Windows application)." - PostgreSQL 7.2
> > Documentation - chap. 7. ODBC Interface
> >
> > This should happen when I use my system. It works with MySQL and MS SQL
> > Server, so it would work with PostgreSQL also, without any change. The
> > problem I found is that psqlODBC do not validate all the error codes
> > specified by ODBC. The error I related should return "23000/S1010"
> > acording to ODBC.
> >
> > Seeking psqlODBC sources, I found some of this informations/errors were
> > treated at environ.c, under a 'case' where most of the psqlODBC errors
> > were treated. Following this logic, this is where I should insert
> > my code:
> >
> > case ER_DUP_KEY:
> > pg_sqlstate_set(env, szSqlState, "23000", "S1010");
> > // key already exists
> > break;
> >
> > This would return the error code I need to treat qhe the user tries to
> > insert a duplicated key.
>
> Unfortunately PostgreSQL doesn't support error code at
> backend side and it's very hard for clients to detect
> error details such as *key already exists*.
> There may be a way where we analyze the error message
> but I won't do it.
>
> regards,
> Hiroshi Inoue
> http://www.geocities.jp/inocchichichi/psqlodbc/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Hi Gabor,

I'm afraid the answer is, most likely, no.

The ODBC driver works with version 2 of the client-server protocol. This
version does not have error codes support.

Shachar

SZ?CS Gabor wrote:

>Dear Gurus,
>
>As of v7.4, PostgreSQL supports error codes. I'd like to know if we may hope
>for a version of psqlODBC that handles error codes.
>
>I tried with PostgreSQL 7.4.1, ODBCExpress 6.0 (dated 2003 Oct) and psqlodbc
>7.3.1.0 (dated 2003 May 15) and got the following (I assume useless) answer.
>
>Also tried psqlodbc 7.3.2.4 (dated 2003 Nov 12) and the SQLGetDiagRec
>function, with the same result.
>
> Message = "ERROR: update or delete on \"cikk\" violates foreign key
>constraint \"$1\" on \"szallitolevel_tetele\""
> Native = 7
> SQLState = "HY000"
>
>AFAIK these error codes say something like "general error" in ODBC terms :)
>This so-called state, "HY000" is not even an SQL state, unless I'm fatally
>mistaken. It's some kind of ODBC state, isn't it?
>
>Awaiting your kind response,
>
>G.
>
>

--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/

Dear Shachar,

Bad news. Thanks anyway.

Do you know any workarounds?
A different thing, maybe offtopic:
Do you know how to access error codes in psql (the default backend)? Or does
it use v2, too? Whom should I ask? pgsql-novice maybe?

G.
%----------------------- cut here -----------------------%
\end

----- Original Message -----
From: "Shachar Shemesh" <psql(at)shemesh(dot)biz>
Sent: Monday, March 29, 2004 6:00 PM

> Hi Gabor,
>
> I'm afraid the answer is, most likely, no.
>
> The ODBC driver works with version 2 of the client-server protocol. This
> version does not have error codes support.
>
> Shachar