この節では、動的 SQL アプリケーションでパラメーター・マーカーを使用してホスト変数情報を表現する方法について説明します。以下のトピックを扱います。
動的 SQL ステートメントにはホスト変数を入れることができません。それは、ホスト変数情報 (データ・タイプおよび長さ) がアプリケーションのプリコンパイルの間しか使用できないためです。実行時には、ホスト変数情報はありません。そのため、アプリケーション変数を表すには新しい方法が必要です。ホスト変数は疑問符 (?) で表されます。これは、パラメーター・マーカー と呼ばれます。パラメーター・マーカーは、ホスト変数が SQL ステートメントの内部で置換される位置を示します。パラメーター・マーカーは、 SQL ステートメント内部で使用する文脈によって想定されたデータ・タイプおよび長さを持っています。
パラメーター・マーカーのデータ・タイプが、これを使用しているステートメントの内容からはっきり判別できない場合は、 CAST を使用してタイプを指定することができます。このようなパラメーター・マーカーは、 タイプ付きパラメーター・マーカー とみなされます。タイプ付きパラメーター・マーカーは、指定されたタイプのホスト変数と同様に扱われます。たとえば、ステートメント SELECT ? FROM SYSCAT.TABLES は、結果列のタイプが DB2 には認識されないため無効です。ただし、SELECT CAST(? AS INTEGER) FROM SYSCAT.TABLES は、パラメーター・マーカーが INTEGER を表すことがキャストによって約束 されているため、 DB2 には結果列のタイプが認識されます。
パラメーター・マーカーが入っている文字ストリングは、次のような形となります。
DELETE FROM TEMPL WHERE EMPNO = ?
このステートメントが実行されると、 EXECUTE ステートメントの USING 文節によってホスト変数つまり SQLDA 構造が指定されます。ステートメントを実行する際に、ホスト変数の内容が使用されます。
SQL ステートメントにパラメーター・マーカーが 1 つ以上あると、 EXECUTE ステートメントの USING 文節はホスト変数 (各パラメーター・マーカーに 1 つずつ) のリストを指定するか、または各パラメーター・マーカーの SQLVAR 項目を持つ SQLDA を識別しなければなりません。 (LOB の場合は、各パラメーター・マーカーに SQLVAR が 2 つずつあることに注意してください。) ホスト変数リストまたは SQLVAR 項目は、ステートメント内のパラメーター・マーカーの順序に従って突き合わせが行われます。また、これらのデータ・タイプには互換性がなければなりません。
動的 SQL でのパラメーター・マーカーの使用は、静的 SQL でのホスト変数の使用に似ていることに注意してください。いずれの場合も、最適化プログラムは配布統計を使用せず、最適のアクセス・プランを選択することはありえません。
パラメーター・マーカーに適用される規則は、 SQL 解説書 の PREPARE ステートメントの箇所にリストされています。
これは、パラメーター・マーカーを探索および更新の条件内で使用している UPDATE の例です。このサンプルは、以下のプログラム言語で入手可能です。
CHECKERR マクロ / 関数は、プログラム外部にあるエラー検査ユーティリティーです。エラー検査ユーティリティーの所在は、ご使用のプログラム言語により異なります。
このエラー検査ユーティリティーのソース・コードについては、 プログラム例での GET ERROR MESSAGE の使用を参照してください。
#include <stdio.h> #include <stdlib.h> #include <string.h> #include "utilemb.h" EXEC SQL INCLUDE SQLCA; int main(int argc, char *argv[]) { EXEC SQL BEGIN DECLARE SECTION; char pname[10]; short dept; char userid[9]; char passwd[19]; char st[255]; char parm_var[6]; EXEC SQL END DECLARE SECTION; printf( "Sample C program: VARINP \n" ); if (argc == 1) { EXEC SQL CONNECT TO sample; EMB_SQL_CHECK("CONNECT TO SAMPLE"); } else if (argc == 3) { strcpy (userid, argv[1]); strcpy (passwd, argv[2]); EXEC SQL CONNECT TO sample USER :userid USING :passwd; EMB_SQL_CHECK("CONNECT TO SAMPLE"); } else { printf ("\nUSAGE: varinp [userid passwd]\n\n"); return 1; } /* endif */ strcpy (st, "SELECT name, dept FROM staff "); strcat (st, "WHERE job = ? FOR UPDATE OF job"); EXEC SQL PREPARE s1 FROM :st; (1) EMB_SQL_CHECK("PREPARE"); EXEC SQL DECLARE c1 CURSOR FOR s1; (2) strcpy (parm_var, "Mgr"); EXEC SQL OPEN c1 USING :parm_var; (3) EMB_SQL_CHECK("OPEN"); strcpy (parm_var, "Clerk"); strcpy (st, "UPDATE staff SET job = ? WHERE CURRENT OF c1"); EXEC SQL PREPARE s2 from :st; (4) do { EXEC SQL FETCH c1 INTO :pname, :dept; (5) if (SQLCODE != 0) break; printf( "%-10.10s in dept. %2d will be demoted to Clerk\n", pname, dept ); EXEC SQL EXECUTE s2 USING :parm_var; (6) EMB_SQL_CHECK("EXECUTE"); } while ( 1 ); EXEC SQL CLOSE c1; (7) EMB_SQL_CHECK("CLOSE CURSOR"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("ROLLBACK"); printf( "\nOn second thought -- changes rolled back.\n" ); EXEC SQL CONNECT RESET; EMB_SQL_CHECK("CONNECT RESET"); return 0; } /* end of program : VARINP.SQC */
import java.sql.*; class Varinp { static { try { Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance (); } catch (Exception e) { System.out.println ("\n Error loading DB2 Driver...\n"); System.out.println (e); System.exit(1); } } public static void main(String argv[]) { try { System.out.println (" Java Varinp Sample"); // Connect to Sample database Connection con = null; // URL is jdbc:db2:dbname String url = "jdbc:db2:sample"; if (argv.length == 0) { // connect with default id/password con = DriverManager.getConnection(url); } else if (argv.length == 2) { String userid = argv[0]; String passwd = argv[1]; // connect with user-provided username and password con = DriverManager.getConnection(url, userid, passwd); } else { throw new Exception("\nUsage: java Varinp [username password]\n"); } // Enable transactions con.setAutoCommit(false); // Perform dynamic SQL using JDBC try { PreparedStatement pstmt1 = con.prepareStatement( "SELECT name, dept FROM staff WHERE job = ? FOR UPDATE OF job"); (1) // set cursor name for the positioned update statement pstmt1.setCursorName("c1"); (2) pstmt1.setString(1, "Mgr"); ResultSet rs = pstmt1.executeQuery(); (3) PreparedStatement pstmt2 = con.prepareStatement( "UPDATE staff SET job = ? WHERE CURRENT OF c1"); (4) pstmt2.setString(1, "Clerk"); System.out.print("\n"); while( rs.next() ) (5) { String name = rs.getString("name"); short dept = rs.getShort("dept"); System.out.println(name + " in dept. " + dept + " will be demoted to Clerk"); pstmt2.executeUpdate(); (6) }; rs.close(); pstmt1.close(); (7) pstmt2.close(); } catch( Exception e ) { throw e; } finally { // Rollback the transaction System.out.println("\nRollback the transaction..."); con.rollback(); System.out.println("Rollback done."); } } catch( Exception e ) { System.out.println(e); } } }
Identification Division. Program-ID. "varinp". Data Division. Working-Storage Section. copy "sqlca.cbl". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 pname pic x(10). 01 dept pic s9(4) comp-5. 01 st pic x(127). 01 parm-var pic x(5). 01 userid pic x(8). 01 passwd. 49 passwd-length pic s9(4) comp-5 value 0. 49 passwd-name pic x(18). EXEC SQL END DECLARE SECTION END-EXEC. 77 errloc pic x(80). Procedure Division. Main Section. display "Sample COBOL program: VARINP". * Get database connection information. display "Enter your user id (default none): " with no advancing. accept userid. if userid = spaces EXEC SQL CONNECT TO sample END-EXEC else display "Enter your password : " with no advancing accept passwd-name. * Passwords in a CONNECT statement must be entered in a VARCHAR format * with the length of the input string. inspect passwd-name tallying passwd-length for characters before initial " ". EXEC SQL CONNECT TO sample USER :userid USING :passwd END-EXEC. move "CONNECT TO" to errloc. call "checkerr" using SQLCA errloc. move "SELECT name, dept FROM staff - " WHERE job = ? FOR UPDATE OF job" to st. EXEC SQL PREPARE s1 FROM :st END-EXEC. (1) move "PREPARE" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. (2) move "Mgr" to parm-var. EXEC SQL OPEN c1 USING :parm-var END-EXEC (3) move "OPEN" to errloc. call "checkerr" using SQLCA errloc. move "Clerk" to parm-var. move "UPDATE staff SET job = ? WHERE CURRENT OF c1" to st. EXEC SQL PREPARE s2 from :st END-EXEC. (4) move "PREPARE S2" to errloc. call "checkerr" using SQLCA errloc. * call the FETCH and UPDATE loop. perform Fetch-Loop thru End-Fetch-Loop until SQLCODE not equal 0. EXEC SQL CLOSE c1 END-EXEC. (7) move "CLOSE" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL ROLLBACK END-EXEC. move "ROLLBACK" to errloc. call "checkerr" using SQLCA errloc. DISPLAY "On second thought -- changes rolled back.". EXEC SQL CONNECT RESET END-EXEC. move "CONNECT RESET" to errloc. call "checkerr" using SQLCA errloc. End-Main. go to End-Prog. Fetch-Loop Section. EXEC SQL FETCH c1 INTO :pname, :dept END-EXEC. (5) if SQLCODE not equal 0 go to End-Fetch-Loop. display pname, " in dept. ", dept, " will be demoted to Clerk". EXEC SQL EXECUTE s2 USING :parm-var END-EXEC. (6) move "EXECUTE" to errloc. call "checkerr" using SQLCA errloc. End-Fetch-Loop. exit. End-Prog. stop run.