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

Groups > comp.databases.postgresql > #115

Re: sqlstate or messages in pltcl

Show all headers | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

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

csiph-web