Thread: Foreign key constraint & getErrorCode

I'm using PostgreSQL 7.4.2 on Fedora Core 2 (included RPMS).  When a
foreign key constraint fails during an insert, SQLException.getErrorCode
is returning zero.

Here is a code snippet to demonstrate the problem:

import java.io.*;
import java.sql.*;

class ForeignKey
{
     public static void main(String [] args) throws Exception
     {
         Class.forName("org.postgresql.Driver");
    Connection db = DriverManager.getConnection(args[0], args[1],
                                                              args[2]);
    Statement st = db.createStatement();

    st.execute("CREATE TABLE table1 (id INT PRIMARY KEY)");

    st.execute("CREATE TABLE table2 (id INT PRIMARY KEY, " +
                                    "fk INT NOT NULL REFERENCES " +
                                         "table1)");

    try
    {
        st.execute("INSERT INTO table2 (id, fk) VALUES (0, 0)");
         }
    catch (SQLException sqle)
    {
        System.err.println(sqle.getErrorCode() + ": " +
                                sqle.getMessage());
    }
     }
}

And here's what I get when I run it:

[pilcher@home temp]$ echo $CLASSPATH
.:/usr/share/java/pg74.1jdbc3.jar
[pilcher@home temp]$ java ForeignKey jdbc:postgresql:tomcat4 tomcat4 ''
0: ERROR: insert or update on table "table2" violates foreign key
constraint "$1"

Based on my reading of the PostgreSQL docs, getErrorCode should be
returning 23503.  Any idea what's going on?

Thanks!

--
========================================================================
Ian Pilcher                                        i.pilcher@comcast.net
========================================================================

Ian Pilcher wrote:

> Based on my reading of the PostgreSQL docs, getErrorCode should be
> returning 23503.  Any idea what's going on?

Try SQLException.getSQLState() instead. The backend returns (somewhat
standardized) alphanumeric SQL states with errors and the driver exposes
these as via getSQLState(). getErrorCode() is numeric, vendor-specific,
and unused by the current driver.

You may also need to upgrade your JDBC driver; I don't know how recent
the driver in the RPMs you are using is.

-O

Oliver Jowett wrote:
> Try SQLException.getSQLState() instead. The backend returns (somewhat
> standardized) alphanumeric SQL states with errors and the driver exposes
> these as via getSQLState(). getErrorCode() is numeric, vendor-specific,
> and unused by the current driver.

Works like a charm.  Thanks!

(I didn't realize that the PostgreSQL error codes were alphanumeric;
makes it kind of hard to pack into an integer.)

--
========================================================================
Ian Pilcher                                        i.pilcher@comcast.net
========================================================================