EXECUTE

EXECUTE

EXECUTEコマンドは、リモート・データベースに対してSQLステートメントを実行する場合に使用されます。

                                                        条件付

 EXECUTE --------- QUERY -----------値----------------------->

                                     *SQLPARAMETER

               >-- UPDATE ----------値----------------------->

                                     *SQLPARAMETER

               >-- PREPARED --------値----------------------->

                                     *SQLPARAMETER

               >-- CALL ------------値----------------------->

               >-- CALLTYPE -------- *QUERY ---------------------->

                                     *UPDATE

                                     *EXECUTE

               >-- CALLSYNTAX ------ *JDBC ----------------------->

                                     *ORACLE

               >-- RETURN ---------- *NONE -----------------------|

                                     *CHAR

                                     *STRING

                                     *SMALLINT

                                     *INTEGER

                                     *FLOAT

                                     *DOUBLE

                                     *DECIMAL

                                     *NUMERIC

                                     *ORACLECURSOR

キーワード

コメント/警告

EXECUTEコマンドは非常に強力です。このコマンドを最大限に活用するには、その機能を理解することが重要です。

以下に、各コマンドの使用タイミングと最適な使用方法について詳しく説明します。この内容をよく読んでから進めてください。

QUERY

リモート・データベースに対してクエリーを実行し、結果セットが返されることが予想される場合(例えば、SELECTから値が返される場合など)、通常、EXECUTE QUERY(SQLステートメント)コマンドを使用します。結果セットの取得方法については、以下の「リストと変数」を参照してください。

非常に大きなSQLステートメントを使用する場合、SET PARAMETER(*SQL)コマンドでステートメントを準備してからEXECUTE QUERY(*SQLPARAMETER)コマンドを使用する方法があります。

「CALL」キーワードと「PREPARED」キーワードも参照してください。

UPDATE

EXECUTE UPDATE(SQLステートメント)コマンドは、通常、INSERT、UPDATE、またはDELETEを使用してデータベースを更新する場合に使用されます。結果セットは返されません。したがって、この点を考慮すると、データにクエリーを実行する際にこのコマンドは使用されません。

非常に大きなSQLステートメントを使用する場合、SET PARAMETER(*SQL)コマンドでステートメントを準備してからEXECUTE QUERY(*SQLPARAMETER)コマンドを使用する方法があります。

「CALL」キーワードと「PREPARED」キーワードも参照してください。

PREPARED

同じSQLステートメントを複数回実行する場合、通常は実行回数を少なくするため、PREPAREDコマンドを使用することをお勧めします。

これを行うには、プリペアド・ステートメントをデータベースに送信して一度コンパイルします。つまり、ステートメントを実行するたびに、最初にコンパイルしなくてもすぐに実行できます。この方法を使用しない場合、UPDATEコマンドをループに入れる必要があり、ステートメントは毎回データベースへの接続を再確立しなければなりません。

プリペアドSQLステートメントはパラメータを指定せずに実行できますが、ほとんどの場合、ステートメントがパラメータを取る方が利点があります。パラメータは、SET PARAMETER(*LIST)コマンドと組み合わせて作成された作業リストによってSQLステートメントに渡されます。そのため、例えば、多数の社員の住所の詳細を1回で更新できます。この操作を行うには、プリペアドSQLを使用し、更新するデータのリストを渡す方法が最も効率的です。

非常に大きなSQLステートメントを使用する場合、SET PARAMETER(*SQL)コマンドでステートメントを準備してからEXECUTE QUERY(*SQLPARAMETER)コマンドを使用する方法があります。

プリペアドSQLステートメントがINSERTで始まり、VALUES(*CALC)またはVALUES(*ALL)で終わる場合、SQLステートメントの特殊な解析が行われます。*CALCオプションでは、列のバインディング・パラメータが計算され、ステートメントが修正されます。*ALLオプションの場合、各列のバインディング・パラメータが計算され、テーブルのその他の列は省略値を使用して挿入されます。

CALL

このキーワードにより、リモート・サーバーでストアド・プロシージャを呼び出すことができます。このストアド・プロシージャにはSQLステートメントが含まれます。CALLコマンドは、*QUERY、*UPDATE、または*EXECUTEのいずれかのタイプを指定するCALLTYPEコマンドと一緒に使用されます。

ストアド・プロシージャにより、SQLステートメントの実行がかなり効率的になります。例えば、多くのデータが返される場合、ネットワーク・トラフィックが軽減されます。このトピックについて詳しく調べることをお勧めします。IBM InfocentreおよびRedbooksにはこのトピックに関する情報が豊富にあり、どちらもIBM Webサイトで入手できます。

パラメータは、作業リストやSET PARAMETER(*LIST)コマンドを使用してリモート・プロシージャに渡すことができます。この方法については、以下の「リストと変数」を参照してください。

一部のストアド・プロシージャ呼び出しでは、実行されたことを伝える警告が返されるため、SET ONWARNING(*CONTINUE)コマンドを使用して警告チェックを無効にする必要があります。

このコマンドの例とストアド・プロシージャの作成方法については、SQLServiceの例に記載されています。ストアド・プロシージャの例はこのセクションの最後にあります。

CALLSYNTAX

Oracle JDBCドライバーは、データベース・ファンクションから値を返す際に使用される場合、JDBC構文を完全にサポートするわけではありません。このドライバーは独自のOracle構文を使用します。この点を考慮すると、このキーワードを使用して、標準のJDBC (この場合*JDBCを指定)と、Oracle JDBC (この場合*ORACLEを指定)のどちらを使用するかを指定する必要があります。これによって、どのスタイルのJDBCドライバーを用意する必要があるかがSQLServiceに伝えられます。

RETURN

ストアド・プロシージャは、戻り値を指定できます。これは、結果セットを返す場合とは異なります。結果セットは、クエリーから返される1つ以上のレコードや値のリストです。一方、戻り値は、完了後にストアド・プロシージャが返す単一の値です。典型的なストアド・プロシージャ構文には、ストアド・プロシージャで実行された最後のコマンドの1つとして、「RETURN」または類似のコマンド(ターゲット・データベースの構文に従う)と関連するフィールド名があります。この値はどのような値でも構いませんが、一部の一般的な例では、選択または更新されたレコードの数、最大値、一部のフラグを含むフィールドになります。

このRETURNキーワードでは、返された値のデータ・タイプを指定できます。可能なデータ・タイプは、ターゲット・データベースとJDBCドライバーでサポート可能な内容によって異なります。例えば、IBM i で現在サポートするのは、ストアド・プロシージャから返される値のデータ・タイプIntegerのみになります。最終的には、ターゲット・データベースとJDBCドライバーで対応可能なデータ・タイプをユーザーの責任で把握します。

このSQLServiceとOracleデータベースを併用する場合、「Oracleに関する注意事項」を参照してください。

Oracleに関する注意事項

Oracleデータベースは、データベース・ファンクションから値を返す際に使用される場合、JDBC構文を完全にサポートするわけではありません。Oracle構文でのみ機能します。これは、CALLSYNTAXを*ORACLEとして指定する必要があるためです。その結果、SQLServiceでは予想される事態とその対処方法を把握します。

Oracleには2種類の呼び出し可能なプログラムがあります。1つはストアド・プロシージャ、もう1つはファンクションです。ストアド・プロシージャは値を返すことができないため、値を返したい場合はファンクションを使用する必要があります。

Oracle JDBCドライバーとOracleデータベースでは、Java JDBCの「ResultSet resultSet = call.executeQuery ()」メソッドをサポートしません。Oracleで結果セットをJDBCクライアントに返す場合、Oracleカーソルを返すOracleファンクションを作成する必要があり、JDBC call.execute ()メソッドを使用する必要があります。ターゲット・データベースが確実にOracleで、結果セットがストアド・プロシージャから返されると予想される場合、このEXECUTEコマンドのRETURN値として*ORACLECURSORを指定する必要があります。これはSQLServiceで使用され、どのレコードを返すかを決定します。カーソルが返されることを示すこの明示的な要件は、Oracleのみに関連します。他のデータベースでは、結果セットの取得に関してこのキーワードを使用する必要はありません。キーワードでこの値を使用する場合、カーソルがSQLServiceに返されるようにOracleファンクションで「RETURN cursorname」コマンドを使用する必要があります。

さらに、JDBCクライアントでは、業界標準のJDBC構文ではなくOracle構文を使用して呼び出しを準備する必要があります。

以下に、Oracleファンクションの一例を示します。

SET PARAMETER(*LIST)コマンドを使用したパラメータの処理

EXECUTEコマンドとSET PARAMETER(*LIST)コマンドを組み合わせて実行する場合、データのリストは、リモート・データベースとSQLステートメントに渡されます。この方法を使用する場合、SQLステートメントでは、コマンドに挿入する必要がある値ごとに「?」を入れる必要があります。これらはパラメータ・バインディング・ポジションと呼ばれます。基本的に、リストの最初の列は最初の「?」がある場所に、2番目の列は2番目の「?」がある場所にといった具合に配置されます。サンプル・コードでは、この機能の適切な例が示されています。

SQL構文に関する注意事項

使用する構文はターゲット・データベースに関連するため、使用するJDBCドライバーの構文を把握する必要があることにも注意してください。

SQLステートメント内では文字列の値を引用府で囲む必要があります。例：

  EXECUTE UPDATE(UPDATE TABLE SET COL1='値')

  EXECUTE UPDATE(DELETE FROM TABLE WHERE COL1='値')

SQLステートメントに開き括弧や閉じ括弧がある場合、ステートメント全体を二重引用府で囲む必要があります。例：

  EXECUTE PREPARED("INSERT INTO TABLE (COL1,COL2,COL3) VALUES(?,?,?)")

リストと変数

QUERYキーワード、SQLステートメントがSELECTの場合のPREPAREDキーワード、またはCALLTYPEに*QUERYが指定されたCALLキーワードのいずれかを使用する場合、結果セットが返されるようにするには、このコマンドで作業リストを提供します。

この作業リストには、クエリーから返されると予想されるフィールドが含まれます。リストはフィールドの順序で埋められます。SELECTステートメントは、このリストに示されるよりも多くのフィールドを実際に返すことができますが、列よりも多いリスト・フィールドは返されません。列の値は、resultSet.getString (列インデックス)メソッドを使用して結果セットから受け取ります。リスト・エントリー・フィールドはこの文字列値で設定され、Javaデータ・タイプからネイティブ・データ・タイプへの変換が行われます。

非常に大きなリストが返されると予想される場合、この作業リストを省略し、READコマンドを使用して結果セットにアクセスできます。

UPDATEキーワードは結果セットを返さないため、この状況で作業リストを指定する必要はありません。

CALLでCALLTYPEに*UPDATEを指定した場合、行数(更新されたレコードの数)が返されるため、この値を取得するには1列のリストを指定できます。

呼び出したプロシージャ(CALLコマンドを使用)にパラメータを渡す必要がある場合、パラメータを含む作業リストを指定する必要があります。この作業リストには、渡す必要があるパラメータと同じだけの列があります。作業リストの最初の行のみが使用されます。リストは、SET PARAMETER(*LIST)コマンドを使用して準備する必要があります。

この仕組みについて確認するには、以下の例とSQLServiceの例を参照してください。RDML、RDMLX、または3GLアプリケーションのサービス・コマンドに作業リストを指定する方法については、「Java Service Managerクライアント」を参照してください。

例

SQLServiceサービスのSET、EXECUTE、READの各コマンドは、それぞれ緊密に関連しています。そのため、「SQLServiceの例」の拡張例を確認することをお勧めします。

RDML

* Define the JSM command and message fields

DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)

DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(256)

DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256)

* Define the fields and list that will contains the result set returned from the query

DEFINE FIELD(#COL1) TYPE(*CHAR) LENGTH(010)

DEFINE FIELD(#COL2) TYPE(*CHAR) LENGTH(020)

DEFINE FIELD(#COL3) TYPE(*DEC) LENGTH(008) DECIMALS(0)

DEF_LIST NAME(#WRKLST) FIELDS(#COL1 #COL2 #COL3) TYPE(*WORKING)

* Define the field and list that will be passed to the remote procedure to indicate what values to query

DEFINE FIELD(#PARAM1) TYPE(*CHAR) LENGTH(010)

DEF_LIST NAME(#PARAMLST) FIELDS(#PARAM1) TYPE(*WORKING)

CHANGE FIELD(#PARAM1) TO(A1001)

ADD_ENTRY TO_LIST(#PARAMLST)

* Set up the list so that it is passed to the remote procedure when the EXECUTE is run

CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(PARAM1)')

USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #PARAMLST)

* Call procedure

CHANGE FIELD(#JSMCMD) TO('EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY) SERVICE_LIST(COL1,COL2,COL3)')

USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLST)

RDMLX

* Define the JSM command and message fields

Define Field(#JSMSTS) Type(*CHAR) Length(020)

Define Field(#JSMMSG) Type(*CHAR) Length(256)

Define Field(#JSMCMD) Type(*CHAR) Length(256)

Define Field(#JSMHND) Type(*Char) Length(4)

* Define the fields and list that will contains the result set returned from the query

Define Field(#COL1) Type(*CHAR) Length(010)

Define Field(#COL2) Type(*CHAR) Length(020)

Define Field(#COL3) Type(*DEC) Length(008) Decimals(0)

Def_List Name(#WRKLST) Fields(#COL1 #COL2 #COL3) Type(*WORKING)

* Define the field and list that will be passed to the remote procedure to indicate what values to query

Define Field(#PARAM1) Type(*CHAR) Length(010)

Def_List Name(#PARAMLST) Fields(#PARAM1) Type(*WORKING)

#PARAM1 := A1001

Add_Entry To_List(#PARAMLST)

* Set up the list so that it is passed to the remote procedure when the EXECUTE is run

#JSMCMD := 'Set Parameter(*LIST) Service_List(PARAM1)'

Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #PARAMLST)

* Call procedure

#JSMCMD := 'Execute("CallSelect(?)") CallType(*QUERY) Service_List(COL1,COL2,COL3)'

Use Builtin (JSM_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKLST)

IBM i のストアド・プロシージャの例：

IBM i データベースのストアド・プロシージャを作成するには、ソース・メンバーを編集し、メンバー・タイプTXTを使用する必要があります。

以下に、IBM i の3つのストアド・プロシージャの例を示します。最初の行は、ストアド・プロシージャの名前を指定することに注意してください。例えば、最初の行はCALLSELECTと呼ばれ、その場所はJSMJDBCライブラリになります。2番目と3番目の例は、値が返される状況を示しています。

本書では、ストアド・プロシージャの作成について詳しく説明していません。そのため、このトピックについて詳しく調べることをお勧めします。IBM InfocentreおよびRedbooksにはこのトピックに関する情報が豊富にあるため、これらの参照から始めると良いかもしれません。どちらもIBM Webサイトで入手できます。

*************** Beginning of data *************************************

CREATE PROCEDURE JSMJDBC/CALLSELECT(IN CODE CHAR (10))

LANGUAGE SQL

READS SQL DATA

RESULT SETS 1

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR

SELECT ID,NAME,AGE FROM JSMJDBC/TBLNAME WHERE ID = CODE;

OPEN C1;

END

****************** End of data ****************************************

*************** Beginning of data *************************************

CREATE PROCEDURE JSMJDBC/CALLUPDATE(IN CODE CHAR (10))

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE num_records INTEGER;

UPDATE JSMJDBC/TBLNAME SET SALARY=12000.43 WHERE ID = CODE;

GET DIAGNOSTICS num_records = ROW_COUNT;

RETURN num_records;

END

****************** End of data ****************************************

*************** Beginning of data *************************************

CREATE PROCEDURE JSMJDBC/CALLEXECUT(IN CODE CHAR (10))

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE num_flag INTEGER DEFAULT 0;

UPDATE JSMJDBC/TBLNAME SET SALARY=16000.26 WHERE ID = CODE;

RETURN num_flag;

END

****************** End of data ****************************************

ストアド・プロシージャを作成するには、RUNSQLSTMコマンドを使用する必要があります。例：

RUNSQLSTM SRCFILE(JSMJDBC/QCLSRC) SRCMBR(CALLSELECT) COMMIT(*NONE)

SQLServiceを使用してこれらのストアド・プロシージャを実行する場合は、以下のEXECUTEコマンドのようなコマンドを使用できます。

EXECUTE CALL("CALLSELECT(?)") CALLTYPE(*QUERY)

EXECUTE CALL("CALLUPDATE(?)") CALLTYPE(*UPDATE) RETURN(*INTEGER)

EXECUTE CALL("CALLEXECUT(?)") CALLTYPE(*EXECUTE) RETURN(*INTEGER)

Oracleデータベース・ファンクションの例：

以下に、結果セットを作成するOracleデータベース・ファンクションの例を示します。これ自体は、カーソルを返すことに注意してください。

本書では、ストアド・プロシージャの作成について詳しく説明していません。そのため、他の媒体でこのトピックについて詳しく調べることをお勧めします。

CREATE OR REPLACE FUNCTION "SYSTEM"."TEST" ( PARAM1 IN CHAR, PARAM2 IN CHAR ) RETURN SYS_REFCURSOR AS CURSOR_1 SYS_REFCURSOR ;

BEGIN

OPEN CURSOR_1 FOR SELECT * FROM MYTABLE WHERE FLD_1='AB';

RETURN CURSOR_1 ;

END;

SQLServiceを使用してこのファンクションを実行する場合は、以下のEXECUTEコマンドのようなコマンドを使用できます。

EXECUTE CALL("TEST(?,?)") CALLTYPE(*EXECUTE) CALLSYNTAX(*ORACLE) RETURN(*ORACLECURSOR) SERVICE_LIST(COL1,COL2)