Example 2 - Use of the SET PARAMETER(*LIST)
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. The 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 four 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 an IBM i database called JSMJDBC (since this is an 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 are:
1. Define the fields to handle the messages to be returned from the JSMCOMMANDs.
2. Define a field that will hold the SQL statement.
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 4.
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 that needs to be inserted into the file. So first, the fields for these columns need to defined - they must match the type and length of the fields in the table. Once defined, the working list can be defined. In this example we will be inserting the ID, NAME, AGE, and SALARY.
5. Another working list is defined to hold the fields for the update. In this case, the SALARYs are to be updated, 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.
6. Start JSM, LOAD the SQLService, then CONNECT to the database driver.
7. Issue a SET command to configure the commitment control requirements.
8. We will next prepare the SQL statement then place it into a working list.
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 being used to provide a working list that will hold the SQL statement for the EXECUTE command to be executed 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;
10. Now you will specify some new records to be entered into the database. Notice that each new record appears on a new line of the list.
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 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.
12. The final step in this part of the program is to run the EXECUTE command that will insert the records into the table. Since the values have been prepared already, in this command you use the keyword PREPARED(*SQLPARAMETER).
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 …….')
13. As was done for preparing the INSERT data, the updated data is loaded into the WRKLSTUPD working list and then prepared using the SET PARAMETER(*LIST) command.
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.
15. COMMIT the results
16. Finally disconnect the service then close 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