Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #108 > unrolled thread

Re: sqlstate or messages in pltcl

Back to article view | Back to comp.databases.postgresql

This discussion starts older than the indexed window; earlier articles aren't shown. The article labeled Started by below is the oldest one visible, not the original post.

Contents

  Re: sqlstate or messages in pltcl "Laurenz Albe" <invite@spam.to.invalid> - 2011-05-03 09:20 +0200
    Re: sqlstate or messages in pltcl "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-05-04 10:11 +0200

#108 — Re: sqlstate or messages in pltcl

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?

It cannot be in the system catalogs because it is not persistent.

Unfortunately I know nothing about Tcl, but the manual says
(for spi_exec): "An error in the command causes an error to be raised."

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.

Yours,
Laurenz Albe 

[toc] | [next] | [standalone]

#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?
> 
> It cannot be in the system catalogs because it is not persistent.
> 
> Unfortunately I know nothing about Tcl, but the manual says
> (for spi_exec): "An error in the command causes an error to be raised."
> 
> 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.
> 
> Yours,
> Laurenz Albe 
> 
> 

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=> create table test_duplicates (id integer primary key,
val text);
HINWEIS:  CREATE TABLE / PRIMARY KEY erstellt implizit einen
Index »test_duplicates_pkey« für Tabelle »test_duplicates«
CREATE TABLE

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

-- test proc tries to do insert (wrapped lines on paste)
trans1=> 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=> select findsqlstate();
                                   findsqlstate
----------------------------------------------------------------------------------
 CATCH: doppelter Schlüsselwert verletzt Unique-Constraint
»test_duplicates_pkey«+

(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=> select eval_me('set ::errorInfo');
                                  eval_me
---------------------------------------------------------------------------
 doppelter Schlüsselwert verletzt Unique-Constraint
»test_duplicates_pkey«+
     while executing
         +
 "spi_exec $sql"
(1 Zeile)

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

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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web