[SQL] error codes
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
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 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.
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