アプリケーションは 2 つの部分で稼働するよう設計することができます。つまりクライアントで 1 つ、サーバーでもう 1 つです。ストアード・プロシージャーは、アプリケーションと同じトランザクション内のデータベースで稼働する部分です。ストアード・プロシージャーは、組み込み SQL で、または DB2 CLI 関数を使用して作成することができます (CLI でのストアード・プロシージャーの作成を参照)。一般に、ストアード・プロシージャーには次の利点があります。
さらに、組み込み静的 SQL で作成されたストアード・プロシージャーには、次の利点があります。
ストアード・プロシージャーを DB2 CLI アプリケーションから呼び出すには、次の CALL ステートメント構文を SQLExecDirect() に渡すか、または SQLPrepare() と SQLExecute() にこの順番で渡します。
.-,--------. V | >>-CALL---procedure-name--(-----+----+--+---)------------------>< '-?--'
注: |
CALL ステートメントは動的には作成できませんが、 DB2 CLI は動的に作成できる場合と同様に CALL ステートメントを受け入れます。
ストアード・プロシージャーは、 ストアード・プロシージャー呼び出し構文にある ODBC ベンダー・エスケープ・シーケンスを使用して呼び出すこともできます。 |
実行するプロシージャーの名前 (拡張子なし)。起動されるプロシージャーは、次のようにして判別されます。
プロシージャーが一度選択されると、DB2 は外部名で定義されるプロシージャーを起動します。
感嘆符を使用して、識別されるライブラリー名を procedure-name で指定し、実行される関数を func-name で指定します。この方法によって、類似する関数ルーチンを同じストアード・プロシージャー・ライブラリーに入れることができます。
ストアード・プロシージャー・ライブラリーの名前が、全パス名として指定されます。実行される関数は func-name で指定します。
CALL ステートメントおよびストアード・プロシージャーの使用法に関する詳細については、 SQL 解説書 およびアプリケーション開発の手引き を参照してください。
サーバーが DB2 ユニバーサル・データベース バージョン 2.1 以降、または DB2 (MVS/ESA 版) V4.1 以降の場合、 SQLProcedures() を呼び出して、データベースで利用できるストアード・プロシージャーをリストすることができます。
注: | DB2 ユニバーサル・データベースの場合、 SQLProcedures() がすべてのプロシージャーを返さないこともありますが、アプリケーションは有効なプロシージャーであれば、 SQLProcedures() によって返されるかどうかにかかわらず使用することができます。詳細については、ストアード・プロシージャーの登録および SQLProcedures - プロシージャー名のリストを入手するを参照してください。 |
CALL ステートメント構文図内の ? は、ストアード・プロシージャーの引き数に対応するパラメーター・マーカーを示しています。すべての引き数はパラメーター・マーカーを使用して渡さなければなりません。リテラル、NULL キーワード、および特殊レジスターは使用できません。しかし、ベンダーのエスケープ呼び出しステートメントを使用する場合、つまり呼び出しステートメントが中括弧 '{...}' で囲まれている場合には、リテラルを使用することができます。
CALL ステートメント内のパラメーター・マーカーは、 SQLBindParameter() によってアプリケーション変数にバインドされます。ストアード・プロシージャーの引き数は入力と出力の両方に使用できますが、クライアントとサーバーの間で不必要なデータが送信されないようにするために、アプリケーションで SQLBindParameter() の入力引き数のパラメーター・タイプを SQL_PARAM_INPUT に、出力引き数のパラメーター・タイプを SQL_PARAM_OUTPUT に指定する必要があります。入出力の両方の引き数には、パラメーター・タイプ SQL_PARAM_INPUT_OUTPUT があります。
サーバーが DB2 ユニバーサル・データベース バージョン 2.1 以降、または DB2 (MVS/ESA 版) V4.1 以降の場合、アプリケーションが SQLProcedureColumns() を呼び出して、プロシージャー呼び出しのパラメーターのタイプを判別します。詳細については、下記のストアード・プロシージャーの登録および SQLProcedureColumns - プロシージャーの入力 / 出力情報を入手するを参照してください。
DB2 ユニバーサル・データベースの場合、ストアード・プロシージャーが(SYSCAT.PROCEDURES および SYSCAT.PROCPARMS 内にある) サーバーに登録してからでなければ、 SQLProcedures() および SQLProcedureColumns() を呼び出すことはできません。登録されていない場合、上記 2 つのカタログ関数呼び出しは空の結果セットを返します。ストアード・プロシージャーのサーバーでの登録の詳細については、 付録 H, ストアード・プロシージャー登録の疑似カタログ表を参照してください。
ストアード・プロシージャーが DB2 (MVS/ESA 版) V4.1 以降のサーバーにある場合は、ストアード・プロシージャーの名前を SYSIBM.SYSPROCEDURES カタログ表に定義しなければなりません。 DB2 ユニバーサル・データベースで使用する疑似カタログ表は、 DB2 (MVS/ESA 版) SYSIBM.SYSPROCEDURES カタログ表から派生し拡張したものです。
ストアード・プロシージャーが DB2 ユニバーサル・データベース AS/400 用 V3.1 のサーバーにある場合は、アプリケーションは事前にそのプロシージャーの実際のパスおよび名前を認識していなければなりません。ストアード・プロシージャーまたはその引き数リストに関する情報を検索するための実際または疑似のカタログ表はありません。
多くの点で、ストアード・プロシージャーはその他のアプリケーションと似ていますが、 CLI (および組み込み SQL) で書かれたストアード・プロシージャーでは、ストアード・プロシージャー引き数が入っている SQLDA 構造に対して特別な考慮を払う必要があります。 SQLDA 構造については、SQL 解説書 にその詳細が説明されています。
重要なこととして、SQLDA 構造内に格納されているすべてのデータは、 SQL データ・タイプとして格納されていること、そしてそれに応じた仕方でストアード・プロシージャーによって扱われなければならないことを理解しておかなければなりません。たとえば、
推奨されている方法は、ストアード・プロシージャー向けの方法です。 SQLDA を解釈し、すべての入力引き数を項目上のホスト言語変数に、またホスト言語変数から出口上の SQLDA へ移動します。これにより、 SQLDA 特定コードをストアード・プロシージャー内でローカライズすることができます。
DB2 CLI には、ストアード・プロシージャーが、 1 つまたは複数の (おのおのが 1 つの照会に関連づけられている) カーソルがオープンされ、かつストアード・プロシージャーが終了してもカーソルがオープンされたままであるようにコーディングされている場合に、ストアード・プロシージャー呼び出しから 1 つまたは複数の結果セットを取り出すことができる機能が備わっています。 2 つ以上のカーソルがオープンされたままであると、複数の結果セットが返されます。
DB2 CLI アプリケーションは、ストアード・プロシージャーを実行してカーソルをオープンした後、以下の方法で結果セットを検索することができます。
DB2 ユニバーサル・データベースのストアード・プロシージャーは、 1 つまたは複数の結果セットを CLI アプリケーションに返すために、以下の要件を満たす必要があります。
一般には、結果セットを返すストアード・プロシージャーの呼び出しは、照会ステートメントの実行と同等です。以下の制約事項が適用されます。
組み込み SQL のストアード・プロシージャーには多くの利点がありますが、既存の DB2 CLI アプリケーションを所有しているアプリケーション開発者はアプリケーションの構成要素を移動してサーバー上で実行したい場合があります。アプリケーションのコードおよび論理に加えなければならない変更を最小限にするために、これらの構成要素を DB2 CLI を使って作成したストアード・プロシージャーとして組み込むことができます。
DB2 CLI 接続に関連するすべての内部情報は接続ハンドルによって参照され、ストアード・プロシージャーはクライアント・アプリケーションと同じ接続およびトランザクションで実行されるので、 DB2 CLI を用いて作成されるストアード・プロシージャーは、ヌル SQLConnect() の呼び出しを行って、接続ハンドルをクライアント・アプリケーションの基礎接続に関連付ける必要があります。ヌル SQLConnect() とは、ServerName、UserName、および Authentication 引き数がすべて NULL に設定され、そのそれぞれの長さ引き数がすべて 0 に設定されているものです。もちろん、SQLConnect() 呼び出しを完全に行えるためには、環境および接続ハンドルがすでに割り振られている必要があります。
注: | 組み込み SQL を使用して作成されたストアード・プロシージャーは、 DATETIME ISO オプションを使用してプリコンパイルし、 DB2 CLI が日時の値を正確に処理できるようにしなければなりません。 |
以下に示すのは、ストアード・プロシージャー、およびストアード・プロシージャーの呼び出しの例です。 (次の例は入力例です。出力例は outcli2.c、 outsrv2.c のサンプルを参照してください。)
また、 DB2 には複数行の結果セットを返すストアード・プロシージャーを例示するいろいろなプログラム例があります (mrsp で始まる以下のプログラム例を参照してください。 mrspcli.c、mrspcli2.c、mrspcli3.sqc、 clicall.c、mrspsrv.c および mrspsrv2.sqc)。
/* ... */ /******************************************************************** * * PURPOSE: This sample program demonstrates stored procedures, * using CLI. It is rewrite of the inpsrv.sqc embedded SQL * stored procedure. * * There are two parts to this program: * - the inpcli2 executable (placed on the client) * - the inpsrv2 library (placed on the server) * CLI stored procedures can be called by either CLI or embbeded * applications. * * The inpsrv function will take the information * received in the SQLDA to create a table and insert the * names of the presidents. * * Refer to the inpcli2.c program for more details on how * this program is invoked as the inpsrv2 function * in the inpsrv2 library by the EXEC SQL CALL statement. * * The SQL CALL statement will pass in 2 identical SQLDA * structures for input and output because all parameters * on the CALL statement are assummed to have both the * input and output attributes. However, only changes * make to the data and indicator fields in the output SQLDA * will be returned to the client program. * * NOTE: One technique to minimize network flow is to set the * variables that returns no output to null on the server program * before returning to the client program. * This can be achieved by setting the value -128 to the * indicator value associated with the data. * * The sqleproc API will call the inpsrv routine stored * in the inpsrv library. * * The inpsrv routine will take the information received * and create a table called "Presidents" in the "sample" * database. It will then place the values it received in * the input SQLDA into the "Presidents" table. * ********************************************************************/ #include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlda.h> #include <sqlcli1.h> #include "samputil.h" /* Header file for CLI sample code */ int SQL_API_FN inpsrv2( struct sqlchar * input_data, struct sqlda * input_sqlda, struct sqlda * output_sqlda, struct sqlca * ca ) { /* Declare a local SQLCA */ struct sqlca sqlca ; SQLCHAR table_stmt[80] = "CREATE TABLE " ; SQLCHAR insert_stmt[80] = "INSERT INTO " ; SQLCHAR insert_data[21] ; SQLINTEGER insert_data_ind ; /* Delare Miscellanous Variables */ int cntr ; char * table_name ; short table_name_length ; char * data_item[3] ; short data_item_length[3] ; int num_of_data = 0 ; /* Delare CLI Variables */ SQLHANDLE henv, hdbc, hstmt ; SQLRETURN rc ; /*-----------------------------------------------------------------*/ /* Assign the data from the SQLDA to local variables so that we */ /* don't have to refer to the SQLDA structure further. This will */ /* provide better portability to other platforms such as DB2 MVS */ /* where they receive the parameter list differently. */ /* Note: Strings are not null-terminated in the SQLDA. */ /*-----------------------------------------------------------------*/ table_name = input_sqlda->sqlvar[0].sqldata ; table_name_length = input_sqlda->sqlvar[0].sqllen ; num_of_data = input_sqlda->sqld - 1 ; for ( cntr = 0; cntr < num_of_data; cntr++ ) { data_item[cntr] = input_sqlda->sqlvar[cntr+1].sqldata ; data_item_length[cntr] = input_sqlda->sqlvar[cntr+1].sqllen ; } /*-----------------------------------------------------------------*/ /* Setup CLI required environment */ /*-----------------------------------------------------------------*/ SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ; SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ; /*-----------------------------------------------------------------*/ /* Issue NULL Connect, since in CLI we need a statement handle */ /* and thus a connection handle and environment handle. */ /* A connection is not established, rather the current */ /* connection from the calling application is used */ /*-----------------------------------------------------------------*/ SQLConnect( hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS ) ; SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ; /*-----------------------------------------------------------------*/ /* Create President Table */ /* - For simplicity, we'll ignore any errors from the */ /* CREATE TABLE so that you can run this program even when the */ /* table already exists due to a previous run. */ /*-----------------------------------------------------------------*/ strncat( ( char * ) table_stmt, ( char * ) table_name, table_name_length ) ; strcat( ( char * ) table_stmt, " (name CHAR(20))" ) ; SQLExecDirect( hstmt, table_stmt, SQL_NTS ) ; SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) ; /*-----------------------------------------------------------------*/ /* Generate and execute a PREPARE for an INSERT statement, and */ /* then insert the three presidents. */ /*-----------------------------------------------------------------*/ strncat( ( char * ) insert_stmt, ( char * ) table_name, table_name_length ) ; strcat( ( char * ) insert_stmt, " VALUES (?)" ) ; if ( SQLPrepare(hstmt, insert_stmt, SQL_NTS) != SQL_SUCCESS ) goto ext ; /* Bind insert_data to parameter marker */ SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 20, 0, insert_data, 21, &insert_data_ind ) ; for ( cntr = 0; cntr < num_of_data; cntr++ ) { strncpy( ( char * ) insert_data, ( char * ) data_item[cntr], data_item_length[cntr]) ; insert_data_ind = data_item_length[cntr] ; if ( SQLExecute( hstmt ) != SQL_SUCCESS ) goto ext ; } /*-----------------------------------------------------------------*/ /* Return to caller */ /* - Copy the SQLCA */ /* - Update the output SQLDA. Since there's no output to */ /* return, we are setting the indicator values to -128 to */ /* return only a null value. */ /* - Commit or Rollback the inserts. */ /*-----------------------------------------------------------------*/ ext: rc = SQLGetSQLCA( henv, hdbc, hstmt, &sqlca ) ; if ( rc != SQL_SUCCESS ) printf( "RC = %d\n", rc ) ; memcpy( ca, &sqlca, sizeof( sqlca ) ) ; if ( output_sqlda != NULL ) { for ( cntr = 0; cntr < output_sqlda->sqld; cntr++ ) { if ( output_sqlda->sqlvar[cntr].sqlind != NULL ) *( output_sqlda->sqlvar[cntr].sqlind ) = -128 ; } } rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ; CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ; rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ; CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ; printf( ">Disconnecting .....\n" ) ; rc = SQLDisconnect( hdbc ) ; CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ; rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ; CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ; rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ; if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ; return( SQL_SUCCESS ) ; }
/* ... */ SQLCHAR * stmt = "CALL inpsrv2(?, ?, ?, ?)" ; /* ... */ rc = SQLPrepare( hstmt, stmt, SQL_NTS ) ; CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ; rc = SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 9, 0, Tab_Name, 10, NULL ) ; CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ; rc = SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, Pres_Name[0], 11, NULL ) ; CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ; rc = SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, Pres_Name[1], 11, NULL ) ; CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ; rc = SQLBindParameter( hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 10, 0, Pres_Name[2], 11, NULL ) ; CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ; rc = SQLExecute( hstmt ) ; /* Ignore Warnings */ if ( rc != SQL_SUCCESS_WITH_INFO ) CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;