READ
The READ command is used to read a result set returned by the SQL statement specified in the EXECUTE command. It uses the optional COLUMN_LIST keyword values or the field and column mapping supplied by the SET PARAMETER(*MAP) command.
If the EXECUTE command does not have a working list to receive the result list into, then it will remain on the server until accessed by a READ command.
You should use the READ command when the number of expected records will exceed the size or width of a working list. The READ command enables you to reduce the number of records brought down from the server at any one time. If the SQL result is going to return 20,000 records, for example, it would be better to read them into sets of 1,000, reducing the amount of resources being allocated.
As another example, the maximum width of an RDML working list is 256 characters. If you are expecting a result set of 300 characters in width then you could not return this into a working list as a part of the EXECUTE command. In this case you would use the READ command to retrieve the records in manageable chunks. If need be, you could issue multiple READs and place them results into lists of less than 256 characters in width.
READ ------------ SCROLL ---------- *YES ---------------------->
*NO
>-- COLUMN_LIST ----- value ---------------------|
Keywords
|
SCROLL |
This keyword is used to specify whether new rows or the old rows are selected for the next read.
Refer to Comments / Warnings for further information on this. |
|
COLUMN_LIST |
A comma separated list of columns that exist in the result set. |
Comments / Warnings
The READ command reads the current result set using the field and column mapping supplied by the SET PARAMETER (*MAP) command. Working list fields are mapped to columns and data for these columns are mapped to the working list fields.
As mentioned above the power of this READ command is its ability to enable you to access information in more manageable chunks. The SCROLL keyword enables you to decide whether your subsequent READ will be retrieving new columns for the same records as this current read, or columns from the next set of records. That is, if you select:
- *YES - it will select the columns specified in the READ, then scroll forward so that you next read will be retrieving columns from a completely new set of records
- *NO - it will select the columns in the READ, and not scroll forward so that your next READ may select new columns from the same rows.
The number of records it scrolls forward will be that number defined as the number of entries in your working list.
Lists and Variables
This command will need to supply a working list to which the retrieved values are returned. The columns defined in the working list will be those that you are wish to retrieve for this particular READ. The relationship between these fields and the actual fields in the remote table will have been set up in the SET PARAMETER(*MAP) command.
Refer to the following examples and the SQLService Examples to see how this works. For information on how to supply a working list to service commands from RDML, RDMLX or 3GL applications, refer to Java Service Manager Clients.
Examples
The SET, EXECUTE, and READ commands of the SQLService service are very tightly related to each other. As such, you are recommended to review the more extensive examples in the SQLService Examples.
RDML
* Define the JSM command and message fields
DEFINE FIELD(#JSMSTS) TYPE(*CHAR) LENGTH(020)
DEFINE FIELD(#JSMMSG) TYPE(*CHAR) LENGTH(256)
DEFINE FIELD(#JSMCMD) TYPE(*CHAR) LENGTH(256)
* Define the fields and the list that will be used to indicate the field/column mappings
DEFINE FIELD(#FIELD) TYPE(*CHAR) LENGTH(010)
DEFINE FIELD(#COLUMN) TYPE(*CHAR) LENGTH(030)
DEF_LIST NAME(#MAPLST) FIELDS(#FIELD #COLUMN) TYPE(*WORKING)
* Define the fields and the lists that the queried data will be returned back into
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(#WRKLST1) FIELDS(#COL1 #COL3) TYPE(*WORKING)
DEF_LIST NAME(#WRKLST2) FIELDS(#COL1 #COL2 #COL4) TYPE(*WORKING)
* Define the mapping
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)
CHANGE FIELD(#JSMCMD) TO('EXECUTE QUERY(SELECT ID,NAME,AGE,SALARY FROM TBLNAME)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
USE BUILTIN(JSM_COMMAND) WITH_ARGS('READ SERVICE_LIST(COL1,COL3) SCROLL(*NO)') TO_GET(#JSMSTS #JSMMSG #WRKLST1)
USE BUILTIN(JSM_COMMAND) WITH_ARGS('READ SERVICE_LIST(COL1,COL2,COL4)') TO_GET(#JSMSTS #JSMMSG #WRKLST2)
RDMLX
* Define the JSM command and message fields
Define Field(#JSMSTS) Type(*CHAR) Length(020)
Define Field(#JSMMSG) Type(*CHAR) Length(256)
Define Field(#JSMCMD) Type(*CHAR) Length(256)
Define Field(#JSMHND) Type(*Char) Length(4)
* Define the fields and the list that will be used to indicate the field/column mappings
Define Field(#FIELD) Type(*Char) Length(010)
Define Field(#COLUMN) Type(*Char) Length(030)
Def_List Name(#MAPLST) Fields(#FIELD #COLUMN) Type(*Working)
* Define the fields and the lists that the queried data will be returned back into
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(#WRKLST1) Fields(#COL1 #COL3) Type(*Working)
Def_List Name(#WRKLST2) Fields(#COL1 #COL2 #COL4) Type(*Working)
* Define the mapping
#FIELD := COL1
#COLUMN := ID
Add_Entry To_List(#MAPLST)
#FIELD := COL2
#COLUMN := NAME
Add_Entry To_List(#MAPLST)
#FIELD := COL3
#COLUMN := AGE
Add_Entry To_List(#MAPLST)
#FIELD := COL4
#COLUMN := SALARY
Add_Entry To_List(#MAPLST)
#JSMCMD := 'Set Parameter(*Map) Service_List(FIELD,COLUMN)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #MAPLST)
* Run the Query
#JSMCMD := 'Execute Query(Select ID,NAME,AGE,SALARY From TBLNAME)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
* Read the columns 1 & 3 (which hold ID and AGE)
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND 'Read Service_List(COL1,COL3) Scroll(*NO)') To_Get(#JSMSTS #JSMMSG #WRKLST1)
* Read the columns 1, 2, & 4 (which hold ID, NAME, and SALARY)
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND 'Read Service_List(COL1,COL2,COL4) Scroll(*NO)') To_get(#JSMSTS #JSMMSG #WRKLST2)