SQLExceptionから取得できるSQLSTATEとエラーコード

ふとjava.sql.SQLExceptionのJavaDocを見ていたら、SQLSTATEとベンダー固有のエラーコードを取得できることに(いまさら)気づいた。
ベンダー固有のエラーコードはログに吐き出すくらいしか用途を思いつかないが、SQLSTATEはある程度規格化されているはずなので、どの程度役に立つものなのか調べてみた。
Javaプログラムから、以下のSQL文を順に発行してみる。(各SQL文の後ろに書いてあるのは、PreparedStatementに対して指定するパラメータ)

CREATE TABLE hoge(idx INTEGER NOT NULL PRIMARY KEY, str VARCHAR(8) NOT NULL);

-- OK
INSERT INTO hoge(idx, str) VALUES(?, ?);                    -- 0, "hoge"
-- (1) NG: 主キー制約に反する
INSERT INTO hoge(idx, str) VALUES(?, ?);                    -- 0, "hoge"
-- OK
INSERT INTO hoge(idx, str) VALUES(?, ?);                    -- 1, "hoge"
-- (2) NG: NOT NULL制約に反する
UPDATE hoge SET str = ? WHERE idx = ?;                      -- null, 0
-- (3) NG: INTEGER型の範囲を(一般的には)超えている
INSERT INTO hoge(idx, str) VALUES(?, ?);                    -- Long.MAX_VALUE, "hoge"
-- (4) NG: INTEGER型の範囲を(一般的には)超えている (Long.MAX_VALUE - 1)
INSERT INTO hoge(idx, str) VALUES(9223372036854775806, ?);  -- "hoge"
-- (5) NG: VARCHAR(8)の範囲を(一般的には)超えている
INSERT INTO hoge(idx, str) VALUES(?, ?);                    -- 11, "hogehogehoge"
-- (6) NG: VARCHAR(8)の範囲を(一般的には)超えている
INSERT INTO hoge(idx, str) VALUES(?, 'hogehogehoge');       -- 12

DROP TABLE hoge;

実行するJavaプログラムの断片は以下のような感じ。

System.out.printf("----SQL: [%s][%s]",
        sql,
        params != null ? Arrays.asList(params) : "{}");
try {
    PreparedStatement pstmt = connection.prepareStatement(sql);
    try {
        //
        //--パラメータのセット等
        //

        try {
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.printf("  --E: [%8d:%-8s]%s%n",
                    e.getErrorCode(), e.getSQLState(), e.getMessage());
        }
    } finally {
        pstmt.close();
    }
} catch (SQLException e) {
    System.out.printf("  E: [%8d:%-8s]%s%n",
            e.getErrorCode(), e.getSQLState(), e.getMessage());
}

あと、以下のようなコードでSQLSTATEタイプも出力してみる。

DatabaseMetaData databaseMetaData = connection.getMetaData();
System.out.printf("%-24s: %5d (X/Open: %d, SQL99: %d)%n",
        "SQLStateType",
        databaseMetaData.getSQLStateType(),
        DatabaseMetaData.sqlStateXOpen,
        DatabaseMetaData.sqlStateSQL99);

出力されたSQLSTATEは次のような結果。

DBMS名	SQLStateType	(1)	(2)	(3)	(4)	(5)	(6)
DB2 9.1	SQL99	23505	23502	null	22003	22001	22001
DB2 9.5	SQL99	23505	23502	42815	22003	22001	22001
DB2 9.7	SQL99	23505	23502	42815	22003	22001	22001
Oracle 10g	(*3)	23000	72000	(*4)	(*4)	72000	72000
PostgreSQL 8.0	SQL99	23505	23502	22003	22003	22001	22001
PostgreSQL 8.1	SQL99	23505	23502	22003	22003	22001	22001
PostgreSQL 8.2	SQL99	23505	23502	22003	22003	22001	22001
PostgreSQL 8.3	SQL99	23505	23502	22003	22003	22001	22001
PostgreSQL 8.4	SQL99	23505	23502	22003	22003	22001	22001
PostgreSQL 9.0	SQL99	23505	23502	22003	22003	22001	22001
PostgreSQL 9.1	SQL99	23505	23502	22003	22003	22001	22001
MySQL 4.1	SQL99	23000	(*2)	22001(*6)	23000(*7)	22001(*8)	22001(*8)
MySQL 5.0	SQL99	23000	23000	22001	22001	22001	22001
MySQL 5.1	SQL99	23000	23000	22001	22001	22001	22001
MySQL 5.5	SQL99	23000	23000	22001	22001	22001	22001
Firebird 2.1	X/Open	HY000	HY000	07006	HY000	22001	HY000
Firebird 2.5	X/Open	HY000	HY000	07006	HY000	22001	HY000
Derby 10.5.3.0	SQL99	23505	23502	22003	22003	22001	22001
SQLite 3.6.14.2	SQL99	null	null	(*5)	(*5)	(*1)	(*1)
SQLServer 2005	SQL99	23000	23000	S0002	S0002	22001	22001
SQLServer 2008	SQL99	23000	23000	S0002	S0002	22001	22001
H2 1.2	SQL99	23001	90006	22003	22003	90005	90005
H2 1.3	SQL99	23505	23502	22003	22003	22001	22001
HSQLDB 1.8.1	SQL99	23000	23000	22003	22003	(*1)	(*1)

• (*1) VARCHAR(8)のサイズ指定が無視されて、行が追加されてしまう。
• (*2) setNullが無視されて、空文字列が入っている。
• (*3) 「1」か「2」が返ってくるはずのところで「0」を返しやがる。
• (*4) NUMBER型となるので、32ビット整数の上限は関係ないらしい。
• (*5) integer型に32ビット整数の上限は関係ないらしい。
• (*6) 符号付32ビット整数の最大値「2147483647」が格納されてしまう。例外が発生しているのに‥
• (*7) (3)と同様なのだが、(3)によって主キー重複例外になっているだけ。(3)を実行しなければ「22001」が返される。
• (*8) データがVARCHAR(8)に合わせてtruncateされ、"hogehoge"が挿入されてしまう。例外が発生しているのに‥

SQLSTATEは、ISO/IEC 9075では'0'から'9'の数字か'A'から'Z'までの大文字アルファベット、計36文字の組み合わせから成る文字列で、Classを表す2文字とSubclassを表す3文字の計5文字として規定されている(より詳細には、標準定義クラス(standard-defined class)と実装定義クラス(implementation-defined class)に分類されるらしいが、ここでは触れない)。
で、ISO/IEC 9075:2003では、Class「22」は"data exception"、Class「23」は"integrity constraint violation"、Class「42」は"syntax error or access rule violation"として規定されている。
そう考えると、(1)と(2)は"23xxx"が返ってくるのが自然だし、(3)、(4)、(5)、(6)は"22xxx"が返ってくるのが自然だと言える。
ただし、DB2 9.5/9.7の(3)は動的パラメータとしてINTEGERの範囲を超える値を渡しているため、数値の範囲超え(22003)ではなくINTEGER値としての構文に違反(42815)していると解釈されるのかも。
これは、Javaプログラムで以下のように書いた場合の違いと近いかも。

//コンパイルエラー (DB2で42815が返されるケース)
//   Java：コンパイル時に、intの範囲を超えていることが分かる
//   DB2：prepared statementをコンパイル(?)するときに、INTEGERであることが分かる
//           ⇒pstmt.setLong(2, 9223372036854775806) した時点で(実行前に)範囲超えが分かる
int v = 9223372036854775806;
//実行時例外 (DB2で22003が返されるケース)
//   Java：文字列なので、実行してみないと、intの範囲を超えているか分からない
//   DB2：SQL文を実行(解釈)してみないと、INTEGERの範囲を超えているか分からない
int v = Integer.parseInt("9223372036854775806");

まぁ、結論として、SQLSTATEは参考程度にしかならないということが分かったが、興味のある人はDB2 Information Centerを見てみるといいかもしれない。

• IBM Knowledge Center
  • "SQLSTATE"で検索して、「SQLSTATE メッセージ」を選択してみると、いろいろ出てくる。