Informix Guide to SQL: Tutorial Creating and Using SPL Routines

Within SPL routines, you can use the DBINFO function to find out the number of rows that have been processed in SELECT, INSERT, UPDATE, DELETE, EXECUTE PROCEDURE, and EXECUTE FUNCTION statements.

Figure 10-113 shows an SPL function that uses the DBINFO function with the 'sqlca.sqlerrd2' option to determine the number of rows that are deleted from a table.

CREATE FUNCTION del_rows ( pnumb INT )
RETURNING INT;


DEFINE nrows INT;


DELETE FROM sec_tab WHERE part_num = pnumb;
LET nrows = DBINFO('sqlca.sqlerrd2');


RETURN nrows;


END FUNCTION;

To ensure valid results, use this option after SELECT and EXECUTE PROCEDURE or EXECUTE FUNCTION statements have completed executing. In addition, if you use the 'sqlca.sqlerrd2' option within cursors, make sure that all rows are fetched before the cursors are closed to ensure valid results.