Example 3 - READ command receiving the result set from query
In this SQLService example, you will see how to use the READ command to receive the result set from the query. In using the READ command you must use the SET PARAMETER(*MAP) command to set up the field column mappings.
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 that this example follows are:
1. Define the fields to handle the messages to be returned from the JSMCOMMANDs;
2. Define the fields that will be used to map the table fields to the columns in the results list. The fields defined here should match the length and type of the fields defined in the table being queried.
3. Define the two fields required for the list that is used to hold the field column mappings.
4. Define the list that will hold the column mappings. This should be a two-column list - the first column in this list will hold the column number and the second field will hold the column name.
5. Define two working lists using the fields defined in step 2. These lists will be populated by the READ command from the result list that the query generates. In this example, two columns are used to demonstrate how you can keep going back to the result list to access different columns as and when required. Two browselists are defined with the same shape as these working lists, and these will be used to display the results on a screen. See point 9 for details on the significance of the ENTRYS value for the working lists.
6. Next start JSM, LOAD the SQLService, then CONNECT to the database driver.
7. Define the column field mappings. This is done by setting #FIELD to the appropriate column and the #COLUMN to the appropriate field name (from the table). Each mapping will be added as a new entry to the #MAPLST working list, and then a SET PARAMETER(*MAP) command will be issued. Notice that a SERVICE_LIST is passed as a part of this command - the fields defined here are those used in the mapping list. The mapping list must also be specified in the TO_GET portion of the JSM command.
8. EXECUTE the command. In this example, a prepared statement is not used, but is typed directly into the EXECUTE command. Notice how a working list is not supplied with this JSM command. For an EXECUTE QUERY the working list would normally be provided to hold the result list, but in this case the READ command has been chosen instead to access the result list.
9. The next step is to issue the READ command to access the data in the result set. You will notice that this is done twice in this example, each time accessing different columns. The capability demonstrated with this example will be particularly useful in situations where your result list is larger than what a working list can hold, so by reducing the number of columns in the READ you can access the data in more manageable chunks. You will note that the first READ has a keyword SCROLL(*NO). This allows the second READ to select the columns from the same records selected by the first READ. The second READ has a keyword SCROLL(*YES), which means that when the next DOWHILE look begins, the first READ will begin with the next set of records. The number of records read with each READ depends on the size of the working lists, which in this example has been set to 100 - NB ENTRYS(100). Notice that the READ commands include a SERVICE_LIST - the fields specified with this keyword must be the same as the fields defined in the working list specified in the TO_GET section of the JSM command.
10. After displaying the results, disconnect the service then close JSM.
* In this SQLService example, you will see how to
* use the READ command to receive the result set from the
* query. In using the READ command you must use the SET
* PARAMETER(*MAP) command to set up the field column
* mappings.
* 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 follow 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 the fields that will be used to map the table
* fields to the columns in the results list. The fields
* defined here should match the length and type of the
* fields defined in the table being queried
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 the 2 fields required for the list that is
* used to hold the field column mappings.
DEFINE FIELD(#FIELD) TYPE(*CHAR) LENGTH(010)
DEFINE FIELD(#COLUMN) TYPE(*CHAR) LENGTH(030)
* 4. Define the list that will hold the column mappings.
* This should be a two column list - the first column in
* this list will hold the column name and the second
* field will hold the column name
DEF_LIST NAME(#MAPLST) FIELDS(#FIELD #COLUMN) TYPE(*WORKING)
* 5. Define 2 working lists using the fields defined in
* the step 2. These lists will be populated by the READ
* command from the result list that the query generates.
* In this example, 2 columns are used to demonstrate how
* you can keep going back to the result list to access
* different columns as and when required. 2 browselists
* with the same shape as these working lists are defined,
* and these will be used to display the results on a
* screen. See point 9 for details on the significance of
* the ENTRYS value for the working lists.
DEF_LIST NAME(#WRKLST1) FIELDS(#COL1 #COL3) TYPE(*WORKING) ENTRYS(100)
DEF_LIST NAME(#WRKLST2) FIELDS(#COL1 #COL2 #COL4) TYPE(*WORKING) ENTRYS(100)
DEF_LIST NAME(#BRWLST1) FIELDS(#COL1 #COL3) ENTRYS(100)
DEF_LIST NAME(#BRWLST2) FIELDS(#COL1 #COL2 #COL4) ENTRYS(100)
* 6. Next start 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. Define the column field mappings. This is done by
* setting #FIELD to the appropriate column and the
* #COLUMN to the appropriate field name (from the table).
* Each mapping will be added as a new entry to the
* #MAPLST working list, and then a SET PARAMETER(*MAP)
* command will be issued. Notice that a SERVICE_LIST is
* passed as a part of this command - the fields defined
* here are those used in the mapping list. The mapping
* list must also be specified in the TO_GET portion of
* the JSM command.
CHANGE FIELD(#FIELD) TO(COL1)
CHANGE FIELD(#COLUMN) TO(ID)
ADD_ENTRY TO_LIST(#MAPLST)
CHANGE FIELD(#FIELD) TO(COL2)
CHANGE FIELD(#COLUMN) TO(NAME)
ADD_ENTRY TO_LIST(#MAPLST)
CHANGE FIELD(#FIELD) TO(COL3)
CHANGE FIELD(#COLUMN) TO(AGE)
ADD_ENTRY TO_LIST(#MAPLST)
CHANGE FIELD(#FIELD) TO(COL4)
CHANGE FIELD(#COLUMN) TO(SALARY)
ADD_ENTRY TO_LIST(#MAPLST)
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*MAP) SERVICE_LIST(FIELD,COLUMN)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #MAPLST)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 8. EXECUTE the command. In this example, a prepared
* statement is not used, but is typed directly into the
* EXECUTE command. Notice how a working list is not supplied
* with this JSM command. For an EXECUTE QUERY the working
* list would normally be provided to hold the result list,
* but in this case, the READ command has been chosen
* to access the result list instead.
CHANGE FIELD(#JSMCMD) TO('EXECUTE QUERY(SELECT ID,NAME,AGE,SALARY FROM TBLNAME)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
* 9. The next step is to issue the READ command to access
* the data in the result set. You will notice that this is
* done twice in this example, each time accessing
* different columns. The capability demonstrated with
* this example will be particularly useful in situations
* where your result list is larger than what a working
* list can hold, so by reducing the number of columns in
* the READ you can access the data in more manageable
* chunks.
* You will note that the first READ has a keyword
* SCROLL(*NO). This allows the second READ to select the
* columns from the same records selected by the first
* READ. The second READ has a keyword SCROLL(*YES), which
* means that when we begin the next DOWHILE look, the
* first READ will begin with the next set of records. The
* number of records read with each READ depends on the
* size of the working lists, which in this example have
* been set to 100 - NB ENTRYS(100).
* Notice that the READ commands include a
* SERVICE_LIST - the fields specified with this keyword
* must be the same as the fields defined in the working
* list specified in the TO_GET section of the JSM
* command.
DOWHILE COND('#JSMSTS *EQ OK')
CLR_LIST NAMED(#WRKLST1)
CLR_LIST NAMED(#WRKLST2)
CLR_LIST NAMED(#BRWLST1)
CLR_LIST NAMED(#BRWLST2)
USE BUILTIN(JSM_COMMAND) WITH_ARGS('READ SERVICE_LIST(COL1,COL3) SCROLL(*NO)') TO_GET(#JSMSTS #JSMMSG #WRKLST1)
IF COND('#JSMSTS *EQ NOROW')
LEAVE
ENDIF
SELECTLIST NAMED(#WRKLST1)
ADD_ENTRY TO_LIST(#BRWLST1)
ENDSELECT
DISPLAY BROWSELIST(#BRWLST1)
USE BUILTIN(JSM_COMMAND) WITH_ARGS('READ SERVICE_LIST(COL1,COL2,COL4)') TO_GET(#JSMSTS #JSMMSG #WRKLST2)
EXECUTE SUBROUTINE(CHECK) WITH_PARMS(#JSMSTS #JSMMSG)
SELECTLIST NAMED(#WRKLST2)
ADD_ENTRY TO_LIST(#BRWLST2)
ENDSELECT
DISPLAY BROWSELIST(#BRWLST2)
ENDWHILE
* 10. After displaying the results disconnect the
* service 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