Path: csiph.com!x330-a1.tempe.blueboxinc.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!feeder.news-service.com!fu-berlin.de!uni-berlin.de!not-for-mail
From: "M. Strobel" <sorry_no_mail_here@nowhere.dee>
Newsgroups: comp.databases.postgresql
Subject: Re: sqlstate or messages in pltcl
Date: Wed, 04 May 2011 10:11:13 +0200
Lines: 102
Message-ID: <92cg11F3g5U1@mid.uni-berlin.de>
References: <8s6vvqFiv3U1@mid.uni-berlin.de> <8selq5Fv57U1@mid.uni-berlin.de> <1304407248.229356@proxy.dienste.wien.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
X-Trace: news.uni-berlin.de CPXyDG3+6GsS1KCr4QoZdQWyLyuSY78lPAlDo2UN7CfBYWmUE=
User-Agent: Mozilla/5.0 (X11; U; Linux i686 (x86_64); de; rv:1.9.2.12) Gecko/20101027 Lightning/1.0b2 Thunderbird/3.1.6
In-Reply-To: <1304407248.229356@proxy.dienste.wien.at>
Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:115

Am 03.05.2011 09:20, schrieb Laurenz Albe:
> M. Strobel wrote:
>>> I am looking for a way to get the database messages and sqlstate
>>> in pltcl, and I can't find it in the docs.
>>>
>>> Must be something like "pg_result $db -status" in pgtcl,
>>> or $stmt->errorInfo() in PHP.
>>>
>>> Is this information somewhere in the system tables?
>>>
>>> /Str.
>>
>> so nothing in the system catalogs?
>=20
> It cannot be in the system catalogs because it is not persistent.
>=20
> Unfortunately I know nothing about Tcl, but the manual says
> (for spi_exec): "An error in the command causes an error to be raised."=

>=20
> So I guess you should catch that error and examine the information
> therein. It should contain the message, and maybe it also contains the
> SQLSTATE.
>=20
> Yours,
> Laurenz Albe=20
>=20
>=20

Yes, I can catch the error in pltcl, something I forgot about.
And I get the error message, but I can't get the sqlstate. So I
would have to search the message with a regex, something I
avoided so far (and forgot about it).

To be more precise, and for the benefit of message archivers,
here the test session in psql (sorry, the db is set to german):

/Str.
----------------------------------------------------

-- Test table:
trans1=3D> create table test_duplicates (id integer primary key,
val text);
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen
Index =C2=BBtest_duplicates_pkey=C2=AB f=C3=BCr Tabelle =C2=BBtest_duplic=
ates=C2=AB
CREATE TABLE

-- test data:
trans1=3D> insert into test_duplicates  values(1,'Value 1');
INSERT 0 1

-- test proc tries to do insert (wrapped lines on paste)
trans1=3D> create or replace function findsqlstate() returns text
as $_$
trans1$>     set sql "insert into test_duplicates  values
(1,'Value extra')"
trans1$>     if {[catch {spi_exec $sql} catchres]} {
trans1$>         return "CATCH: $catchres\n"
trans1$>     } else {
trans1$>         return $catchres
trans1$>     }
trans1$> $_$ language pltcl;
CREATE FUNCTION

-- execute test proc
trans1=3D> select findsqlstate();
                                   findsqlstate
-------------------------------------------------------------------------=
---------
 CATCH: doppelter Schl=C3=BCsselwert verletzt Unique-Constraint
=C2=BBtest_duplicates_pkey=C2=AB+

(1 Zeile)
--
--	result: error can be catched, with message
--

-- now check global variable with suspicious name
-- (this proc executes the text parameter as code)
-- (source not shown)
trans1=3D> select eval_me('set ::errorInfo');
                                  eval_me
-------------------------------------------------------------------------=
--
 doppelter Schl=C3=BCsselwert verletzt Unique-Constraint
=C2=BBtest_duplicates_pkey=C2=AB+
     while executing
         +
 "spi_exec $sql"
(1 Zeile)

trans1=3D> select eval_me('set ::errorCode');
 eval_me
---------
 NONE
(1 Zeile)

--
--	result: sqlstate or errorCode not available
--	text message available