GET
The GET command is used to get return values from a call to a stored procedure or to get the row number that is in error from a parameter list in a prepared statement.
GET ------------- OBJECT ---------- *PARAMETERLISTROW ---------|
*PARAMETERCALL
*NEXTRESULT
Keywords
|
OBJECT |
The *PARAMETERLISTROW is used to return the list entry that has caused the error. It will return the first erroneous row number found into the JSM message field of the JSM command Built-In Function. |
|
The *PARAMETERCALL is used to return the stored procedure call parameters The *NEXTRESULT is used to moved to the next result set. If not result set is available then status code NORESULT is returned. |
Comments / Warnings
This command is very useful when a prepared statement with a parameter list has returned an exception, such as a duplicate key error. Using this command will allow you to ascertain which row in the list is causing the error, so that you may then take some corrective measures.
The typical flow of using this command might be as follows:
Step 1: Execute a prepared statement.
Step 2: If the returned status is OK, then continue with your processing.
Step 3: If the returned status is not OK, then use the GET command to establish which row is in error.
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 field to hold the INSERT statement
DEFINE FIELD(#COLCMD) TYPE(*CHAR) LENGTH(100)
DEF_LIST NAME(#WRKCMD) FIELDS(#COLCMD) TYPE(*WORKING)
*
* Define the fields used in the working list
DEFINE FIELD(#COL1) TYPE(*CHAR) LENGTH(10)
DEFINE FIELD(#COL2) TYPE(*CHAR) LENGTH(20)
DEFINE FIELD(#COL3) TYPE(*DEC) LENGTH(8) DECIMALS(0)
DEFINE FIELD(#COL4) TYPE(*DEC) LENGTH(12) DECIMALS(2)
* Define the working list to hold the values
* to be used by the INSERT statement
DEF_LIST NAME(#WRKLST) FIELDS(#COL1 #COL2 #COL3 #COL4) TYPE(*WORKING)
*
* Create bind values
CHANGE FIELD(#COL1) TO(B2001)
CHANGE FIELD(#COL2) TO('Tom')
CHANGE FIELD(#COL3) TO(45)
CHANGE FIELD(#COL4) TO(35000.60)
ADD_ENTRY TO_LIST(#WRKLST)
* (**********)
CHANGE FIELD(#COL1) TO(A2012)
CHANGE FIELD(#COL2) TO('Antony')
CHANGE FIELD(#COL3) TO(45)
CHANGE FIELD(#COL4) TO(35000.60)
ADD_ENTRY TO_LIST(#WRKLST)
*
* Prepare the INSERT
CHANGE FIELD(#COLCMD) TO('INSERT INTO TBLNAME(ID,NAME,AGE,SALARY) VALUES(?,?,?,?)')
ADD_ENTRY TO_LIST(#WRKCMD)
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*SQL) SERVICE_LIST(COLCMD)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKCMD)
*
* Prepare the list to contain the data to be inserted
CHANGE FIELD(#JSMCMD) TO('SET PARAMETER(*LIST) SERVICE_LIST(COL1,COL2,COL3,COL4)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG #WRKLST)
*
* Execute the prepared statement
CHANGE FIELD(#JSMCMD) TO('EXECUTE PREPARED(*SQLPARAMETER)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
*
* If an error occur, find out the problem row
IF COND('#JSMSTS *NE OK')
CHANGE FIELD(#JSMCMD) TO('GET OBJECT(*PARAMETERLISTROW)')
USE BUILTIN(JSM_COMMAND) WITH_ARGS(#JSMCMD) TO_GET(#JSMSTS #JSMMSG)
*
DISPLAY FIELDS(#JSMMSG)
ENDIF
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 field to hold the INSERT statement
Define Field(#COLCMD) Type(*CHAR) Length(100)
Def_List Name(#WRKCMD) Fields(#COLCMD) Type(*WORKING)
*
* Define the fields and list that will contains the result set 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(0)
Def_List Name(#WRKLST) Fields(#COL1 #COL2 #COL3) Type(*WORKING)
*
* Create bind values
#COL1 := 'B2001'
#COL2 := 'Tom'
#COL3 := 45
#COL4 := 35000.60
Add_Entry To_list(#Wrklst)
*
#COL1 := 'B2002'
#COL2 := 'Jones'
#COL3 := 23
#COL4 := 22000.60
Add_Entry To_list(#Wrklst)
*
* Prepare the INSERT
#COLCMD := 'Insert Into TBLNAME(ID,NAME,AGE,SALARY) VALUES(?,?,?,?)'
Add_Entry To_list(#Wrkcmd)
#JSMCMD := 'Set Parameter(*SQL) Service_List(COLCMD)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKCMD)
*
* Prepare the list to contain the data to be inserted
#JSMCMD := 'Set Parameter(*LIST) Service_List(COL1,COL2,COL3,COL4)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG #WRKLST)
*
* Execute the prepared statement
#JSMCMD := 'Execute Prepared(*SQLPARAMETER)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
*
If '#JSMSTS *ne Ok'
* If an error occur, find the problem row - Get command will return the row number into the #JSMMSG field
#JSMCMD := 'Get Object(*PARAMETERLISTROW)'
Use Builtin(JSMX_COMMAND) With_Args(#JSMHND #JSMCMD) To_Get(#JSMSTS #JSMMSG)
ENDIF