Example of how an indicator variable is used in a REXX program

The way that you use indicator variables for input host variables in REXX programs is slightly different than the way that you use indicator variables in other languages. When you want to pass a null value to a Db2 column, in addition to putting a negative value in an indicator variable, you also need to put a valid value in the corresponding host variable.

For example, the following statements set a value in the WORKDEPT column in table EMP to null:

SQLSTMT="UPDATE EMP" ,
  "SET WORKDEPT = ?"
HVWORKDEPT='000'
INDWORKDEPT=-1
"EXECSQL PREPARE S100 FROM :SQLSTMT"
"EXECSQL EXECUTE S100 USING :HVWORKDEPT :INDWORKDEPT"

In the following program, the phone number for employee Haas is selected into variable HVPhone. After the SELECT statement executes, if no phone number for employee Haas is found, indicator variable INDPhone contains -1.

'SUBCOM DSNREXX'                              
IF RC THEN ,                            
  S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
ADDRESS DSNREXX
'CONNECT' 'DSN'
SQLSTMT = ,
  "SELECT PHONENO FROM DSN8D10.EMP WHERE LASTNAME='HAAS'"
"EXECSQL DECLARE C1 CURSOR FOR S1"
"EXECSQL PREPARE S1 FROM :SQLSTMT"               
Say "SQLCODE from PREPARE is "SQLCODE
"EXECSQL OPEN C1"            
Say "SQLCODE from OPEN is "SQLCODE
"EXECSQL FETCH C1 INTO :HVPhone :INDPhone"
Say "SQLCODE from FETCH is "SQLCODE
If INDPhone < 0 Then ,              
Say 'Phone number for Haas is null.'
"EXECSQL CLOSE C1"             
Say "SQLCODE from CLOSE is "SQLCODE
S_RC = RXSUBCOM('DELETE','DSNREXX','DSNREXX')