読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

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

Java SQL DB2 Oracle PostgreSQL MySQL SQL Server Firebird HSQLDB SQLite H2 Derby

ふと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 メッセージ」を選択してみると、いろいろ出てくる。