アプリケーション開発の手引き

動的 SQL への変数入力

この節では、動的 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 ステートメントの箇所にリストされています。

例: VARINP プログラム

これは、パラメーター・マーカーを探索および更新の条件内で使用している UPDATE の例です。このサンプルは、以下のプログラム言語で入手可能です。

C
varinp.sqc

Java
Varinp.java

COBOL
varinp.sqb

VARINP プログラムの動作の仕組み

  1. SELECT SQL ステートメントを準備する。 PREPARE ステートメントを呼び出すと、SQL ステートメントが動的に準備されます。この SQL ステートメント内では、パラメーター・マーカーは ? で明示されます。 staffjob フィールドは、結果表に指定されていなくても更新可能として定義されます。
  2. カーソルを宣言する。 DECLARE CURSOR ステートメントはカーソル c1 を、 (1) で準備された照会に関連付けます。
  3. カーソルをオープンする。カーソル c1 がオープンすると、データベース・マネージャーは照会を実行し、結果表を作成します。カーソルは第 1 行目より前に 置かれます。
  4. UPDATE SQL ステートメントを準備する。 PREPARE ステートメントを呼び出すと、 SQL ステートメントが動的に準備されます。このステートメントのパラメーター・マーカーは Clerk に設定されますが、更新時に指定された列データ・タイプに適合している限り、設定を任意に動的に変更することも可能です。
  5. 行を取り出す。 FETCH ステートメントはカーソルを次の行に置き、その行の内容をホスト変数に移動します。この行が現在 行になります。
  6. 現在行を更新する。現在行および指定された列 job は、渡されたパラメーター parm_var の内容を用いて更新されます。
  7. カーソルをクローズする。 CLOSE ステートメントを発行すると、カーソルに関連したリソースが解放されます。ただし、カーソルは再度オープンすることができます。

CHECKERR マクロ / 関数は、プログラム外部にあるエラー検査ユーティリティーです。エラー検査ユーティリティーの所在は、ご使用のプログラム言語により異なります。

C
DB2 API を呼び出す C プログラムの場合、 utilapi.c 内の sqlInfoPrint 関数は、 utilapi.h 内の API_SQL_CHECK として再定義されます。 C 組み込み SQL プログラムの場合、 utilemb.sqc 内の sqlInfoPrint 関数は、 utilemb.h 内の EMB_SQL_CHECK として再定義されます。

Java
すべての SQL エラーは SQLException としてスローされ、アプリケーションの catch ブロックで処理されます。

COBOL
CHECKERRcheckerr.cbl という名前の外部プログラムです。

このエラー検査ユーティリティーのソース・コードについては、 プログラム例での GET ERROR MESSAGE の使用を参照してください。

C の例: VARINP.SQC

#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 */

Java の例: Varinp.java

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);
    } 
  }
}

COBOL の例: VARINP.SQB

       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.


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]