java.sql.Array を使ってみる
DB2 v9.5 から ARRAY 型がサポートされるようになった。ただし、自分が認識している範囲では次の制限がある。
- プロシジャ(Procedure)のパラメータでのみ指定できる。
- (DB2 v9.7以降)関数(Function)のパラメータか戻り値にも指定できる。(とオンラインマニュアルには書いてある)
- "CREATE TYPE"文であらかじめ配列型を生成しなければならない。
2つ目の制限はどういうことかというと、
paramName VARCHAR(100) ARRAY[64]
とはできず、
CREATE TYPE VARCHAR_100_ARRAY AS VARCHAR(100) ARRAY[64]
などとしてから
paramName VARCHAR_100_ARRAY
とする必要があるということ。
しかし、これは java.sql.Array を使用するチャンス(何)なので、ARRAY 型に関する機能を(見つけた限り)一通り試してみる。
出力として ARRAY 値を受け取る
ARRAY 型とプロシジャの定義は次のとおり。(プロシジャ定義にセミコロン(';')が含まれるので、文セパレータをエクスクラメーションマーク('!')にしている。)
CREATE TYPE "TYPE_VARCHAR_100_ARRAY_64" AS VARCHAR(100) ARRAY[64] ! CREATE PROCEDURE "PROCEDURE_HOGE_01" (IN INPUT VARCHAR(100), OUT OUTPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64") LANGUAGE SQL DETERMINISTIC CONTAINS SQL BEGIN SET OUTPUT_ARRAY[1] = 'Good morning, ' || INPUT; SET OUTPUT_ARRAY[2] = 'Good afternoon, ' || INPUT; SET OUTPUT_ARRAY[3] = 'Good evening, ' || INPUT; END !
で、これを使用する Java プログラムは次のとおり。
import java.sql.Array; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types; public class Main1 { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/sandbox", "db2admin", "db2admin"); try { CallableStatement cstmt = conn.prepareCall("{CALL PROCEDURE_HOGE_01(?, ?)}"); try { cstmt.setString(1, "hhelibex"); cstmt.registerOutParameter(2, Types.ARRAY); cstmt.execute(); Array array = cstmt.getArray(2); String[] values = (String[]) array.getArray(); System.out.println("values:"); for (int i = 0; i < values.length; ++i) { System.out.printf(" [%2d]: %s\n", i, values[i]); } } finally { cstmt.close(); } } finally { conn.close(); } } }
で、これを実行すると、次のような出力を得る。
values: [ 0]: Good morning, hhelibex [ 1]: Good afternoon, hhelibex [ 2]: Good evening, hhelibex
ちなみに、関数も試そうと思って DB2 v9.7 でも試したが、プロシジャと基本的に同じ内容の定義を書いたところ怒られた
SQL0104N "HAR_100_ARRAY_64"; " に続いて予期しないトークン "SET" が見つかりました。予期されたトークンに "<if>" が含まれている可能性があります。 LINE NUMBER=10. SQLSTATE=42601
ので、エラーになるところを削っていくと、最終的に次のようになり、
CREATE FUNCTION "FUNCTION_HOGE_01" (INPUT VARCHAR(100)) RETURNS "TYPE_VARCHAR_100_ARRAY_64" LANGUAGE SQL DETERMINISTIC CONTAINS SQL BEGIN ATOMIC -- DECLARE OUTPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64"; -- SET OUTPUT_ARRAY[1] = 'Good morning, ' || INPUT; -- SET OUTPUT_ARRAY[2] = 'Good afternoon, ' || INPUT; -- SET OUTPUT_ARRAY[3] = 'Good evening, ' || INPUT; -- RETURN OUTPUT_ARRAY; RETURN NULL; END !
次のように怒られる。
SQL20441N データ・タイプ "ARRAY" は、使用されているコンテキストではサポートされません。 LINE NUMBER=17. SQLSTATE=428H2
‥えっ!?
入力として ARRAY 値を渡す
ARRAY 型とプロシジャの定義は次のとおり。(プロシジャ定義にセミコロン(';')が含まれるので、文セパレータをエクスクラメーションマーク('!')にしている。)
CREATE TYPE "TYPE_VARCHAR_100_ARRAY_64" AS VARCHAR(100) ARRAY[64] ! CREATE PROCEDURE "PROCEDURE_HOGE_02" (IN INPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64", OUT OUTPUT VARCHAR(6464)) LANGUAGE SQL DETERMINISTIC READS SQL DATA BEGIN DECLARE i INTEGER; SET i = 1; SET OUTPUT = ''; WHILE i <= CARDINALITY(INPUT_ARRAY) DO IF i > 1 THEN SET OUTPUT = OUTPUT || ','; END IF; SET OUTPUT = OUTPUT || INPUT_ARRAY[i]; SET i = i + 1; END WHILE; END !
で、これを使用する Java プログラムは次のとおり。
import java.sql.Array; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.Map; public class Main2 { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/sandbox", "db2admin", "db2admin"); try { { CallableStatement cstmt = conn.prepareCall("{CALL PROCEDURE_HOGE_02(?, ?)}"); try { Array array; array = new TestArray(); cstmt.setArray(1, array); cstmt.registerOutParameter(2, Types.VARCHAR); cstmt.execute(); String value = cstmt.getString(2); System.out.println("value: " + value); } finally { cstmt.close(); } } } finally { conn.close(); } } private static class TestArray implements Array { public Object getArray() throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getArray()"); return new String[] { "C", "C++", "Java", "Ruby", "Phthon", "Go", }; } public Object getArray(Map<String, Class<?>> map) throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getArray(Map<String, Class<?>> map)"); return null; } public Object getArray(long index, int count) throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getArray(long index, int count)"); return null; } public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getArray(long index, int count, Map<String, Class<?>> map)"); return null; } public int getBaseType() throws SQLException { return Types.VARCHAR; } public String getBaseTypeName() throws SQLException { return "VARCHAR"; } public ResultSet getResultSet() throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getResultSet()"); return null; } public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getResultSet(Map<String, Class<?>> map)"); return null; } public ResultSet getResultSet(long index, int count) throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getResultSet(long index, int count)"); return null; } public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException { // TODO 自動生成されたメソッド・スタブ System.out.println("### getResultSet(long index, int count, Map<String, Class<?>> map)"); return null; } } }
TestArray クラスは java.sql.Array インタフェースの実装だが、かなり適当(汗)。「とりあえず動けばいいや」的な ^^;;
で、これを実行すると、次のような出力を得る。
### getArray() value: C,C++,Java,Ruby,Phthon,Go
ちなみに、関数も試そうと思って DB2 v9.7 でも試したが、プロシジャと基本的に同じ内容の定義を書いたところ怒られた
SQL0440N 互換性のある引数を持つ、タイプ "FUNCTION" の "CARDINALITY" という名前の許可されたルーチンが見つかりませんでした。 LINE NUMBER=13. SQLSTATE=42884
ので、エラーになるところを削っていくと、最終的に次のようになり、
CREATE FUNCTION "FUNCTION_HOGE_02" (INPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64") RETURNS VARCHAR(6464) LANGUAGE SQL DETERMINISTIC READS SQL DATA BEGIN ATOMIC DECLARE OUTPUT VARCHAR(6464); DECLARE i INTEGER; SET i = 1; SET OUTPUT = ''; -- WHILE i <= CARDINALITY(INPUT_ARRAY) DO -- IF i > 1 THEN -- SET OUTPUT = OUTPUT || ','; -- END IF; -- SET OUTPUT = OUTPUT || INPUT_ARRAY[i]; -- SET i = i + 1; -- END WHILE; RETURN OUTPUT; END !
次のように怒られる。
SQL20441N データ・タイプ "ARRAY" は、使用されているコンテキストではサポートされません。 LINE NUMBER=23. SQLSTATE=428H2
‥えっ!?(しつこい)
表への問い合わせ結果から ARRAY 値を生成する
使用する表、ARRAY 型とプロシジャの定義は次のとおり。(プロシジャ定義にセミコロン(';')が含まれるので、文セパレータをエクスクラメーションマーク('!')にしている。)
CREATE TABLE "TABLE_HOGE" (KEY VARCHAR(100) NOT NULL, VALUE VARCHAR(100) NOT NULL) ! INSERT INTO "TABLE_HOGE" VALUES ('key1', 'value1-3'), ('key1', 'value1-1'), ('key1', 'value1-2'), ('key2', 'value2-2'), ('key2', 'value2-1'), ('key3', 'value3-1') ! CREATE TYPE "TYPE_VARCHAR_100_ARRAY_64" AS VARCHAR(100) ARRAY[64] ! CREATE PROCEDURE "PROCEDURE_HOGE_03" (IN INPUT VARCHAR(100), OUT OUTPUT_ARRAY "TYPE_VARCHAR_100_ARRAY_64") LANGUAGE SQL DETERMINISTIC READS SQL DATA BEGIN SET OUTPUT_ARRAY = ( SELECT ARRAY_AGG(VALUE ORDER BY KEY ASC, VALUE ASC) FROM "TABLE_HOGE" WHERE KEY = INPUT ); END !
で、これを使用する Java プログラムは次のとおり。
import java.sql.Array; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types; public class Main3 { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.ibm.db2.jcc.DB2Driver"); Connection conn = DriverManager.getConnection("jdbc:db2://localhost:50000/sandbox", "db2admin", "db2admin"); try { CallableStatement cstmt = conn.prepareCall("{CALL PROCEDURE_HOGE_03(?, ?)}"); try { cstmt.setString(1, "key1"); cstmt.registerOutParameter(2, Types.ARRAY); cstmt.execute(); Array array = cstmt.getArray(2); String[] values = (String[]) array.getArray(); System.out.println("values:"); for (int i = 0; i < values.length; ++i) { System.out.printf(" [%2d]: %s\n", i, values[i]); } } finally { cstmt.close(); } } finally { conn.close(); } } }
で、これを実行すると、次のような出力を得る。
values: [ 0]: value1-1 [ 1]: value1-2 [ 2]: value1-3
ちなみに、関数も(以下略)