The &ROWSAFFECTED variable is populated with the number of rows affected by the following DPT DML commands: INSERT, UPDATE, and DELETE.

&ROWSAFFECTED is initialized to -1 and is set to -1 by any Direct SQL Passthru command that does not return the number of rows affected, such as a SELECT statement. &ROWSAFFECTED is undefined for other types of requests, and remains unchanged for those requests.

Note: The value of &ROWSAFFECTED is overwritten each time a Direct SQL Passthru INSERT, UPDATE, or DELETE command is executed. If you want to retain the value, you must copy it to another Dialogue Manager variable or store it in a calculated field.

The &ROWSAFFECTED variable is independent of the adapter PASSRECS setting and is always populated for a Direct SQL Passthru INSERT, UPDATE, or DELETE command, regardless of the value of the PASSRECS parameter.

The following request creates an Oracle table:

TABLE FILE EMPLOYEE
SUM LAST_NAME FIRST_NAME DEPARTMENT
HIRE_DATE CURR_SAL CURR_JOBCODE
BY EMP_ID
ON TABLE HOLD AS EMPINFO FORMAT SQLORA
END

The EMPINFO Master File generated by the request follows:

FILENAME=EMPINFO, SUFFIX=SQLORA
SEGNAME=EMPINFO       ,SEGTYPE=S0,$ 
FIELD=EMP_ID         ,ALIAS=EID         ,USAGE=A9    ,ACTUAL=A9,$ 
FIELD=LAST_NAME      ,ALIAS=LN          ,USAGE=A15   ,ACTUAL=A15,$ 
FIELD=FIRST_NAME     ,ALIAS=FN          ,USAGE=A10   ,ACTUAL=A10,$ 
FIELD=HIRE_DATE      ,ALIAS=HDT         ,USAGE=YMD   ,ACTUAL=DATE,$ 
FIELD=DEPARTMENT     ,ALIAS=DPT         ,USAGE=A10   ,ACTUAL=A10,  
  MISSING=ON,$ 
FIELD=CURR_SAL ,ALIAS=CSAL        ,USAGE=P9.2  ,ACTUAL=P4,$
FIELD=CURR_JOBCODE   ,ALIAS=CJC         ,USAGE=A3    ,ACTUAL=A3,$

The EMPINFO Access File generated by the request follows:

SEGNAME=EMPINFO, TABLENAME=EMPINFO, WRITE=YES, KEYS=1, $

The following procedure sets the adapter PASSRECS setting to OFF, then uses the Direct SQL Passthru DELETE command to delete all rows from the Oracle table where the last name is SMITH. It saves the value returned to &ROWSAFFECTED in another variable named &DELETE1. Next, it deletes all rows where the first name is JOHN. It then displays the number of rows affected by each delete command. &DELETE1 contains the value saved from the first delete command and &ROWSAFFECTED contains the value from the second DELETE command:

SQL SQLORA SET PASSRECS OFF
SQL SQLORA
INSERT INTO EMPINFO (EID, LN, FN, DPT, HDT, CSAL, CJC) VALUES('111111111', 'ABEL', 'AARON', 'CORP', '990101', 25000, 'A05');
END
-RUN
-SET &IAFFECTED = &ROWSAFFECTED;
SQL SQLORA
DELETE FROM EMPINFO WHERE EID='111111111';
END
-RUN
-TYPE Rows affected on INSERT and DELETE: &IAFFECTED and &ROWSAFFECTED
SQL SQLORA SET PASSRECS OFF
SQL SQLORA
DELETE FROM EMPINFO WHERE LN='SMITH';
END
-RUN
-SET &DELETE1 = &ROWSAFFECTED;
SQL SQLORA
DELETE FROM EMPINFO WHERE FN='JOHN';
END
-RUN
-TYPE Rows affected on both deletes: &DELETE1 and &ROWSAFFECTED

Since two employees had the last name SMITH and two employees had the first name JOHN, two rows were deleted by each DELETE command:

Rows affected on both deletes: 2 and 2

If you set the value of PASSRECS to ON, in addition to populating the &ROWSAFFECTED variable you generate the FOC1364 message for each Direct SQL Passthru INSERT, UPDATE, or DELETE command:

SQL SQLORA SET PASSRECS ON
SQL SQLORA
DELETE FROM EMPINFO WHERE LN='SMITH';
END
-RUN
-SET &DELETE1 = &ROWSAFFECTED;
SQL SQLORA
DELETE FROM EMPINFO WHERE FN='JOHN';
END
-RUN
-TYPE Rows affected on both deletes: &DELETE1 and &ROWSAFFECTED

The PASSRECS ON setting generates the FOC1364 messages:

(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 2/DELETE
(FOC1364) ROWS AFFECTED BY PASSTHRU COMMAND : 2/DELETE
Rows affected on both deletes: 2 and 2