[SQL] error codes

[Marcin Krawczyk]

Hi guys. Does anyone know the error code for '*currval of sequence * is not
yet defined in this session*' error ? Is there one at all? I am aware
of *others
*code but I'd like to avoid using that.
Thanks in advance.

Regards
mk

Re: [SQL] error codes

[Craig Ringer]

Marcin Krawczyk wrote:
 Hi guys. Does anyone know the error code for '/currval of sequence * is
 not yet defined in this session/' error ? Is there one at all?

A quick JDBC test program shows:

ERROR: currval of sequence customer_id_seq is not yet defined in this
session (SQLState: 55000)

which, looking up the error code in the docs:

http://www.postgresql.org/docs/current/static/errcodes-appendix.html

turns out to be:

55000   OBJECT NOT IN PREREQUISITE STATE

... which makes sense, but I wouldn't call blindingly and immediately
obvious.



Here's a trivial little utility for running a statement, catching an
error, and reporting the error along with the Pg error code. Sorry for
the horrible formatting (short lines for email).

--- Put in file `JdbcTest.java' ---

import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

class JdbcTest  {

public static void main(String[] args)
  throws ClassNotFoundException, InterruptedException, SQLException
{
Class.forName(org.postgresql.Driver);
Connection c =
  DriverManager.getConnection(jdbc://YOUR_PARAMS_HERE);
try {
CallableStatement st =
c.prepareCall(SELECT currval('customer_id_seq'));
st.execute();
} catch (SQLException ex) {
System.out.println(
DB error string:  + ex.getMessage()
 +  (SQLState:  + ex.getSQLState() + ));
}
}
}

 end 

You'll need the PostgreSQL JDBC driver.

To compile (assuming you don't use a Java IDE of some sort, but have the
JDK installed) run:

   javac -d /out/dir JdbcTest.java

and to run (all on one line):

java -classpath /path/to/postgresql-8.3-603.jdbc4.jar:/out/dir JdbcTest

where /out/dir is wherever you want the generated .class file to be put.

--
Craig Ringer

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] error codes

[Craig Ringer]

Craig Ringer wrote:
 Marcin Krawczyk wrote:
 Hi guys. Does anyone know the error code for '/currval of sequence * is
 not yet defined in this session/' error ? Is there one at all?
 
 A quick JDBC test program shows:
 
 ERROR: currval of sequence customer_id_seq is not yet defined in this
 session (SQLState: 55000)

I've just realised I've been doing this a stupid way without thinking
about it. Unsurprisingly it turns out that no Java/JDBC snippets are
required. The much, much easier way to find the error code:

$ psql

... blah blah blah ...

test=# \set VERBOSITY verbose
test=# select currval('test_seq');
ERROR:  55000: currval of sequence test_seq is not yet defined in this
session
LOCATION:  currval_oid, sequence.c:644

In retrospect it seems kind of obvious - surely psql must have way to
show this information, maybe I should look at the manual.

--
Craig Ringer

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Error codes as numbers or in other languages etc.

[Aasmund Midttun Godal]

I have seen several people asking about getting error numbers or similar things out of 
postgres. The way I have done this is as follows:

CREATE TABLE errors (
errorstring TEXT PRIMARY KEY,
message TEXT
);

INSERT INTO errors VALUES ('^FATAL 1:  Database ([^\]+) does not exist in the 
system catalog.$', 'FATALE 1: Le database $1 n''existe pas dans le catalogue 
systeme');

Now in the app if we use perl and DBI we set RaiseError = 1 and catch the error via 
$dbh-errstr or something

pass it into a query like:

prepare('SELECT * FROM errors WHERE ? ~ errorstring'); 
#Isn't postgres great? reverse regular expression searches :)
my $s = execute($dbh-errstr);

then we exploit perl's ability:

$dbh-errstr =~ /$s-{'errorstring'}/$s-{'message'}/e;

Hopes someone else find it usefull,

Getting out numbers is offcourse even easier.

Aasmund Midttun Godal

[EMAIL PROTECTED] - http://www.godal.com/
+47 40 45 20 46

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html