Page History

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.

Syntax:

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.

...

• 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.

Example

RDML

* 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)'
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)'
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