例2 - SET PARAMETER(*LIST) の使用
この例の目的は、SET PARAMETER(*LIST)コマンドを使用して、テーブルに挿入する値を用意する方法を実証することです。この例は、INSERTとUPDATEに必要な構文を示すために拡張されています。レコードを挿入してからすぐに同じレコードを更新するという点ではプログラムは役に立ちません。このプログラムの実際の目的は、コマンドがどのようになるかを実証することです。この例では、SETコマンドを何度か使用して、さまざまな目的を実現する方法についても実証します。この場合は4つのSETコマンドがあります。具体的には、コミット制御設定を行うSETコマンド、SET PARAMETER(*SQL)コマンドを使用してSQLコマンドを事前に作成するSETコマンド、挿入と更新を行う値のリストの作成に使用されるSETコマンドになります。
この例では、JSMJDBC という IBM i のデータベースに接続します (これは IBM i の例であるため、データベース名はライブラリ名になります)。CONNECTコマンドで使用されるドライバー名は、SQLServiceプロパティ・ファイルで定義されるドライバー名とパスに相当します。アクセスするファイルはTBLNAMEとなります。これは、ID、NAME、AGE、SALARY、DEPT、GRADEの各フィールドで構成されます。
以下のステップを行います。
1. JSMCOMMAND から返されるメッセージを処理するフィールドを定義します。
2. SQLステートメントを入れるフィールドを定義します。
3. SQLステートメントを入れる作業リストを定義します。これは1つの列のリストになり、使用されるフィールドはステップ4で定義したフィールドになります。
4. リモート・テーブルに挿入する値は、最初に作業リストに配置されます。作業リストには、ファイルに挿入する必要があるフィールドごとに1つの列があります。そのため、これらの列のフィールドを最初に定義する必要があります。フィールドは、テーブルのフィールドのタイプと長さに一致しなければなりません。定義したら、作業リストを定義できます。この例では、ID、NAME、AGE、SALARYを挿入します。
5. 更新するフィールドを入れるために別の作業リストが定義されます。この場合、SALARYが更新されるため、作業リスト内のある列にはSALARYが入り、別の列にはID (キー)が入ります。これらの列を配置する順序が重要です。これについては後ほど説明します。
6. JSM を開始し、SQLService をロードして、データベース・ドライバーに接続します。
7. コミット制御要件を構成するSETコマンドを発行します。
8. 次にSQLステートメントを作成してから作業リストに配置します。
9. SETコマンドを使用してSQLパラメータを保存します。以下の点に注意します。
- キーワードPARAMETER(*SQL)を使用して、実行するEXECUTEコマンドSQLSQLステートメントを入れる作業リストをこのコマンドで提供するように指定します。
- このコマンドにはSERVICE_LOADキーワードが関連付けられています。これは、SQLステートメントを入れる作業リストのフィールドを指定します。ここで指定するフィールド名は、このJSMコマンドのTO_GET部分の作業リストで定義されたフィールド名と同じでなければなりません。
- 挿入の値のキーワードには4つの疑問符(?)があります。これらの「?」は、WRKLSTINS作業リスト内の列を表すため、列は作業リストに表示される順序で「?」に相当します。そのため、この例ではID値が最初の「?」になり、NAME値が2番目の「?」になるといった具合になります。
10. ここで、データベースに入力する新しいレコードをいくつか指定します。新しい各レコードはリストの新しい行に表示されます。
11. 値がリストで用意されたら、EXECUTEコマンドでこの情報を後から利用できるようにSET PARAMETER(*LIST)コマンドを使用する必要があります。このコマンドの一部としてSERVICE_LISTを指定します。この中には、WRKLSTINS作業リストに表示される列の名前が含まれます。また、この作業リストはこのJSMコマンドのTO_GET部分にも指定されます。
12. プログラムのこの部分の最終ステップとして、テーブルにレコードを挿入するEXECUTEコマンドを実行します。値はすでに用意されているため、このコマンドではキーワードPREPARED(*SQLPARAMETER)を使用します。
注：SQLステートメントが以前に作成されていない場合は、*SQLPARAMETERの代わりにPREPARED('INSERT INTO …….')などを入力します。
13. INSERTデータを作成する場合と同様に、更新されたデータはWRKLSTUPD作業リストにロードされ、SET PARAMETER(*LIST)コマンドを使用して作成されます。
14. ここで、EXECUTEコマンドにより、PREPARED方法を使用する際の列順序の重要性が明確になります。UPDATE 内の最初の '?' は名前用、2 番目の '?' は ID 用であることが分かると思います。ですから、作業リスト WRKLSTUPD にある列も SQL ステートメントに配置された時と同じ順序でなければいけません。
15. 結果をコミットします。
16. 最後に、サービスとの接続を解除してJSMを閉じます。
- この例の目的は、テーブルに挿入する値を
- SET PARAMETER(*LIST) を使って準備する
- 方法を示すことですが、この例には、
- INSERT や UPDATE に必要となる構文も示されています。
- このプログラムでは、レコードの挿入直後に
- 同じレコードが更新されるため、内容的には無意味ですが、
- コマンドの使い方を示すことが、本来の目的です。.
- また、この例には、SET コマンドを用いて
- 様々な目的を達成する方法も多く紹介されています。
- ここでは、次の 4 つの目的の SET コマンドがあります。
- 1つは、コミット制御を設定するためのもの。
- もう 1 つは SET PARAMETER(*SQL) コマンドを使って、
- 事前に SQL コマンドを準備するためのもの。
- 残りの 2 つは、挿入・更新する値のリストを
- 準備するために使われています。
- この例では、JSMJDBC という名前の IBM i データベースに
- 接続していることに注意してください。
- (これは IBM i の例のため、このデータベース名はライブラリ名です)。
- CONNECT コマンドで使用されるドライバ名は、
- SQLService のプロパティ・ファイルに定義されたドライバ名とパスに
- 一致します。アクセスするファイルの名前はTBLNAME で、
- このファイルは、フィールド ID、NAME、AGE、SALARY、
- DEPT、GRADE のフィールドから構成されています。
- 手順の詳細はコード内に示されています。
FUNCTION OPTIONS(*DIRECT) - 1. JSMCOMMAND から戻されるメッセージを処理する
- 処理するフィールドを定義
DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(256)
DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256) - 2. SQL ステートメントを保持するフィールドを定義
DEFINE FIELD(#COLCMD) TYPE(*CHAR) LENGTH(100) - 3. SQL ステートメントを保持する作業リストを定義
- この作業リストは 1 列のリストで、このフィールドは、
- ステップ 2 で定義したフィールドです。
DEF_LIST NAME(#WRKCMD) FIELDS(#COLCMD) TYPE(*WORKING)
*4. リモートのテーブルに挿入する値は、 - 最初に作業リストに入れられます。この作業リストには、
- ファイルに挿入する各フィールドに対応する列が存在します。
- まず、これらの列に対応するフィールドの定義が必要です。
- 各フィールドはテーブル内のフィールドとタイプ・長さが
- 一致していなければいけません。
- このフィールド定義が終わったら、作業リストの定義ができるようになります。
- この例では、ID、NAME、AGE、SALARY のフィールドが挿入されます。
DEFINE FIELD(#COL1) TYPE(*CHAR) LENGTH(010)
DEFINE FIELD(#COL2) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#COL3) TYPE(*DEC) LENGTH(008) DECIMALS(0)
DEFINE FIELD(#COL4) TYPE(*DEC) LENGTH(012) DECIMALS(2)
DEF_LIST NAME(#WRKLSTINS) FIELDS(#COL1 #COL2 #COL3 #COL4) TYPE(*WORKING)
*5. 更新用のフィールドを保持するための別の作業リストを定義します。 - この例では、フィールド SALARY (給与) が更新されます。
- ですから、この作業リストには、1 つの列に SALARY、
- もう 1 つの列には、ID (キー) が含まれます。
- これらの列を配置する順序が大切です。
- この詳細は後ほど説明します。
DEF_LIST NAME(#WRKLSTUPD) FIELDS(#COL3 #COL1) TYPE(*WORKING) - 6. JSM を開始し、SQLService をロード (LOAD) して、
- データベース・ドライバに接続 (CONNECT) します。
- サービスを'開く'
USE BUILTIN(JSM_OPEN) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - サービスの'ロード'
USE BUILTIN(JSM_COMMAND) WITH_ARGS('SERVICE_LOAD SERVICE(SQLSERVICE) TRACE(*NO)') TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
CHANGE FIELD(#JSMCMD) TO('CONNECT DRIVER(DB2) DATABASE(JSMJDBC) USER(PERSON1) PASSWORD(BINGO)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 7. SET コマンドを発行して、
- コミット制御の要件を構成
CHANGE FIELD(#JSMCMD) TO('SET ISOLATION(*READCOMMITTED) AUTOCOMMIT(*NO) ONERROR(*ROLLBACK) ONWARNING(*CONTINUE)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 8.次に SQL ステートメントを準備し、
- これを作業リストに入れます。
CHANGE FIELD(#COLCMD) TO('INSERT INTO TBLNAME(ID,NAME,AGE,SALARY) VALUES(?,?,?,?)')
ADD_ENTRY TO_LIST(#WRKCMD) - 9. SET コマンドは、SQL パラメータの保管用に
- 使われますが、次の点に注意が必要です。
- キーワード PARAMETER(*SQL) を使用することで、
- このコマンドから SQL ステートメントを含む作業リストが提供され、
- 後に EXECUTE コマンドを使って実行されることを
- 示すことができます。.
- - このコマンドの関連キーワードに SERVICE_LOAD
- がありますが、このキーワードで SQL ステートメントを保持する
- リストのフィールドが指定されます。
- ここに指定されるフィールド名は
- この JSM コマンドの TO_GET 部分にある作業リスト
- に定義されたものと同じでなければいけません。
- - INSERT のキーワード values には 4 つの疑問符 ('?') が
- あります。これらの '?' は作業リスト WRKLSTINS の
- 列を表しており、作業リストに表示される順に
- '?' が一致するようになっています。ですから、
- この例では、ID の値が最初の '?'、 NAME の値が
- 2 つめの '?' になるということです。
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*SQL) SERVICE_LIST(COLCMD)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKCMD)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 10. 次に、データベースに入力する新規レコードを
- 指定します。各新規レコードはリストの新しい行に
- なります。
CHANGE FIELD(#COL1) TO(S2221)
CHANGE FIELD(#COL2) TO(TOM)
CHANGE FIELD(#COL3) TO(45)
CHANGE FIELD(#COL4) TO(35000.60)
ADD_ENTRY TO_LIST(#WRKLSTINS)
CHANGE FIELD(#COL1) TO(S2222)
CHANGE FIELD(#COL2) TO(SQUIRE)
CHANGE FIELD(#COL3) TO(22)
CHANGE FIELD(#COL4) TO(27000.60)
ADD_ENTRY TO_LIST(#WRKLSTINS) - 11. リスト内の値の準備ができたら、
- SET PARAMETER(*LIST) コマンドを使って
- これらの情報を後ほど EXECUTE コマンドで利用できるように
- します。SERVICE_LIST がこのコマンドの一部として
- 指定されています。
- これには、作業リスト WRKLSTINS に含まれる列の名前が含まれます。
- また、この JSM コマンドの TO_GET 部分には
- 作業リストが指定されています。
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(COL1,COL2,COL3,COL4)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLSTINS)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
*12. 最後の手順は、テーブルにレコードを挿入する - EXECUTE コマンドを実行します。
- 値がすでに準備されているため、
- このコマンドでは、キーワード PREPARED(*SQLPARAMETER)
- を使用します。
- 注: SQL ステートメントが事前に準備されていない場合は、
- *SQLPARAMETER の部分にステートメントを入力します。
- 例えば、次のようになります。
- PREPARED('INSERT INTO .....')
CHANGE FIELD(#JSMCMD) TO('EXECUTE PREPARED(*SQLPARAMETER)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 挿入の実行 (EXECUTE) がコミット (COMMIT) された後
USE BUILTIN(JSM_COMMAND) WITH_ARGS(COMMIT) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 13. 以下のコードでは、更新処理の方法が示されています。
- INSERT (挿入) データを準備する際に行ったように、
- 更新されたデータは作業リスト WRKLSTUPD に
- ロードされ、SET PARAMETER(*LIST) コマンドを使って
- 準備されます。
CHANGE FIELD(#COL3) TO(123456.99)
CHANGE FIELD(#COL1) TO(S2221)
ADD_ENTRY TO_LIST(#WRKLSTUPD)
CHANGE FIELD(#COL3) TO(654321.11)
CHANGE FIELD(#COL1) TO(S2222)
ADD_ENTRY TO_LIST(#WRKLSTUPD)
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(COL3,COL1)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLSTUPD)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 14. 以下の EXECUTE コマンドを見れば、PREPARED (事前準備)
- のアプローチでは、列の順序が重要であることが分かります。
- UPDATE (更新) 時、
- 最初の '?' は名前、そして 2 番目の '?' は ID です。
- つまり、作業リスト WRKLSTUPD の列は
- SQL ステートメントで配置する際と同じ順序で
- なければなりません。
CHANGE FIELD(#JSMCMD) TO('EXECUTE PREPARED(UPDATE TBLNAME SET SALARY=? WHERE ID=?)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 15. 結果をコミットします。
USE BUILTIN(JSM_COMMAND) WITH_ARGS(COMMIT) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - 16. 最後に、サービスとの接続を解除してJSMを閉じます。
USE BUILTIN(JSM_COMMAND) WITH_ARGS(DISCONNECT) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - サービスを'閉じる'
USE BUILTIN(JSM_CLOSE) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG) - サブルーチン
SUBROUTINE NAME(CHECK) PARMS((#JSMSTS *RECEIVED) (#JSMMSG *RECEIVED))
IF COND('#JSMSTS *NE OK')
DISPLAY FIELDS(#JSMMSG)
USE BUILTIN(JSM_CLOSE) TO_GET(#JSMSTS #JSMMSG)
MENU MSGTXT('Java service error has occurred')
ENDIF
ENDROUTINE