Example 1 - Use of the SET PARAMETER(*SQL) command
This example demonstrates how to use the SQLService to query a database. It will use the SET PARAMETER(*SQL) command to create an SQL statement in advance instead of writing it into the EXECUTE command. A note is included to show how you could achieve the same query without using the SET PARAMETER(*SQL) and simply writing the SQL statement into the EXECUTE command.
Note that this example connects 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 that you will follow are:
1. Define the fields to handle the messages to be returned from the JSMCOMMANDs.
2. Define some fields that will hold the values to be returned from the query.
3. Define a working list whose columns are the fields created in step 2. This working list will eventually hold the result set from the query.
4. Define a field that will hold the SQL statement.
5. 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.
6. Start JSM, LOAD the SQLService, then CONNECT to the database driver.
7. Prepare the SQL statement,then place it into a working list.
8. Use the SET command to store the SQL parameter. You will note that the keyword PARAMETER(*SQL) was used to indicate that this command is to provide a working list that will hold the SQL statement for the EXECUTE command to be executed later. Also note that there is a SERVICE_LOAD keyword associated with this command to specify 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.
9. The next step is to actually EXECUTE the command. You will note in this example, since an already prepared SQL statement is being used, a value of *SQLPARAMETER is specified for the QUERY keyword. You will also notice that a service list is used as a part of this command - this is used to receive the values back into. The columns defined in the service list here must match the columns defined in the working list for the JSM_COMMAND Built In Function.
10. After displaying the results, you will disconnect the service and then close JSM.
* This example demonstrates how to use the SQLService to
* query a database. The SET PARAMETER(*SQL)
* command is used to create an SQL statement
* in advance instead of writing it into the EXECUTE command.
* A note is included to show how you could achieve the
* same query simply by writing the SQL statement
* into the EXECUTE command without using the SET PARAMETER(*SQL).
*
* Note 1: This example connects 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.
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 some fields that will hold the values to be
* 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)
DEFINE FIELD(#COL4) TYPE(*DEC) LENGTH(012) DECIMALS(2)
* 3. Define a working list whose columns are the fields
* created in step 2. This working list will eventually
* hold the result set from the query
DEF_LIST NAME(#WRKLST) FIELDS(#COL1 #COL2 #COL3 #COL4) TYPE(*WORKING)
DEF_LIST NAME(#BRWLST) FIELDS(#COL1 #COL2 #COL3 #COL4)
* 4. Define a field that will hold the SQL statement
DEFINE FIELD(#COLCMD) TYPE(*CHAR) LENGTH(100)
* 5. 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
DEF_LIST NAME(#WRKCMD) FIELDS(#COLCMD) TYPE(*WORKING)
* 6. Start OPEN JSM, LOAD the SQLService, then
* CONNECT to the database driver
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(ALICK) PASSWORD(MEL123)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 7. Prepare the SQL statement then place it in
* a working list
CHANGE FIELD(#COLCMD) TO('SELECT ID,NAME,AGE,SALARY FROM TBLNAME')
ADD_ENTRY TO_LIST(#WRKCMD)
* 8. Use the SET command to store the SQL parameter.
* You will note that the PARAMETER(*SQL) keyword
* is used to indicate that this command provides
* a working list that will hold the SQL statement
* for the EXECUTE command to be executed later
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)
* 9. The next step is to actually EXECUTE the comand.
* You will note in this example, since an already
* prepared SQL statement is being used, a value of
* *SQLPARAMETER is specified for the QUERY keyword.
* You will also notice that a service list is used as a
* part of this command - this is used to receive the
* values back into. The columns defined in the service
* list here must match the columns defined in the working
* list for the JSM_COMMAND Built In Function.
CHANGE FIELD(#JSMCMD) TO('EXECUTE QUERY(*SQLPARAMETER) SERVICE_LIST(COL1,COL2,COL3,COL4)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLST)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* NOTE:
* Another way to express this SQL statement could have
* been to place it directly into the EXECUTE command.
* With this approach, you would remove the SET command
* earlier in the program and rewrite the EXECUTE command
* as follows:
* CHANGE FIELD(#JSMCMD) TO('EXECUTE QUERY(SELECT ID,NAME,
* AGE,SALARY FROM TBLNAME) SERVICE_LIST(COL1,COL2,COL3
* ,COL4)')
SELECTLIST NAMED(#WRKLST)
ADD_ENTRY TO_LIST(#BRWLST)
ENDSELECT
DISPLAY BROWSELIST(#BRWLST)
* 10. After displaying the results, you will disconnect
* the service and then close 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)
* 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