例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コマンドのSQLステートメントを入れる作業リストをこのコマンドで提供するように指定します。
· このコマンドにはSERVICE_LOADキーワードが関連付けられています。これは、SQLステートメントを入れる作業リストのフィールドを指定します。ここで指定するフィールド名は、このJSMコマンドのTO_GET部分の作業リストで定義されたフィールド名と同じでなければなりません。
· INSERTの値のキーワードには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になります。そのため、列は、SQLステートメントに配置される場合と同じ順序でWRKLSTUPD作業リストに表示されます。
15. 結果をコミットします。
16. 最後に、サービスとの接続を解除してJSMを閉じます。
* The purpose of this example is to demonstrate how to
* use the SET PARAMETER(*LIST) command to prepare values
* to be inserted into a table. The example is extended to
* show the syntax required for an INSERT and an UPDATE.
* This program is rather futile in that it inserts a
* record then immediately updates the same record, but
* the real purpose of this is to demonstrate how the
* commands should look. This example also demonstrates
* how the SET command can be used a number of times to
* achieve different objectives. In this case there are 4
* SET commands - one to set the commitment control
* settings, one to prepare an SQL command in advance
* using the SET PARAMETER(*SQL) command, and of course
* the ones used to prepare the list of values to be
* inserted and updated.
* Note that in this example connection is to a
*
IBM
i database called JSMJDBC (since this is a
*
IBM
i example, the database name is a library name).
* The driver name used in the CONNECT command corresponds
* to the driver name and path defined in the SQLService
* properties file. The file being accessed is called
* TBLNAME and it consists of the fields ID, NAME, AGE,
* SALARY, DEPT, and GRADE.
* The steps to be followed are embedded in the code.
FUNCTION OPTIONS(*DIRECT)
* 1. Define the fields to handle the messages to be
* returned from the JSMCOMMANDs
DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(255)
DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(255)
* 2. Define a field that will hold the SQL statement
DEFINE FIELD(#COLCMD) TYPE(*CHAR) LENGTH(100)
* 3. Define a working list that will hold the SQL
* statement. This will be a single column list and the
* field used will be that defined in Step 2
DEF_LIST NAME(#WRKCMD) FIELDS(#COLCMD) TYPE(*WORKING)
* 4. The values that are to be inserted into the remote
* table will first be placed into a working list. The
* working list will have one column for each field we
* need to insert into the file. So first, the fields need
* to be defined for these columns- they must match the
* type and length of the fields in the table. Once
* defined, the working list can be defined. In this
* example the ID, NAME, AGE, and SALARY will be inserted.
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. Another working list is defined to hold the fields
* for the update. In this case we want to update the
* SALARYs, so this working list will contain the SALARY
* in one column and the ID (the key) in another. The
* order in which these columns are placed is important
* and is explained later
DEF_LIST NAME(#WRKLSTUPD) FIELDS(#COL3 #COL1) TYPE(*WORKING)
* 6. Start JSM, LOAD the SQLService, then CONNECT to the
* database driver
* 'Open service'
USE BUILTIN(JSM_OPEN) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 'Load service'
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(ALICK) PASSWORD(MEL123)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 7. Issue a SET command to configure the commitment
* control requirements
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.You will next prepare the SQL statement then place it
* into a working list
CHANGE FIELD(#COLCMD) TO('INSERT INTO TBLNAME(ID,NAME,AGE,SALARY) VALUES(?,?,?,?)')
ADD_ENTRY TO_LIST(#WRKCMD)
* 9. The SET command will be used to store the SQL
* parameter. You will note that:
* the keyword PARAMETER(*SQL) will be used to indicate
* that this command is to provide a working list that
* will hold the SQL statement for the EXECUTE command
* to execute later.
* - there is a SERVICE_LOAD keyword associated with
* this command. This specifies the field in the working
* list that holds the SQL statement. The * field name
* specified here must be the same as that defined in
* the working list in the TO_GET portion of this
* JSM command.
* - the values keyword of the INSERT has four questions
* marks ('?') in it. These '?' represent the columns in the
* WRKLSTINS working list, and they are matched up to the
* '?' in the order they appear in the working list. So in
* this example, ID values go to the first '?', NAME
* values go to the second '?' and so forth
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. Now specify some new records to be entered into
* the database. Notice that each new record appears
* on a new line of the list.
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. Once the values are prepared in the list, you need
* to use the SET PARAMETER(*LIST) command so that the
* EXECUTE command is able to make use of this information
* later. Notice that a SERVICE_LIST is specified as a
* part of this command. It contains the names of the
* columns that appear in the WRKLSTINS working list. Also
* note that this working list is specified in the TO_GET
* portion of this JSM command
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. The final step in this part of the program is to
* run the EXECUTE command which will insert the records
* into the table. Since the values have been prepared
* already, in this command, the keyword
* PREPARED(*SQLPARAMETER) is used.
* Note: If the SQL statement had not been prepared earlier
* then you would have typed it in place of the
* *SQLPARAMETER - for example,
* 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)
* After the EXECUTE the insert is COMMITed
USE BUILTIN(JSM_COMMAND) WITH_ARGS(COMMIT) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
*
13. The next section of code demonstrates how an update
* might work. As was done for preparing the INSERT data,
* the updated data is loaded into the WRKLSTUPD working
* list and the prepared using the SET PARAMETER(*LIST)
* command.
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. The EXECUTE command here demonstrates the
* importance of the column order when using the PREPARED
* approach. You will notice in the UPDATE that
the
* first '?' is for the name, and the second
'?' is for
* the ID. Therefore the columns must appear in the
* WRKLSTUPD working list in the same order as they are to
* be placed in the SQL statement.
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. COMMIT the results
USE BUILTIN(JSM_COMMAND) WITH_ARGS(COMMIT) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 16. Finally disconnect the service then close JSM.
USE BUILTIN(JSM_COMMAND) WITH_ARGS(DISCONNECT) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 'Close service'
USE BUILTIN(JSM_CLOSE) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* SUB ROUTINES
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