Recently in an interview I was asked , How to get the data count when inserting data into the file using an INSERT statement , I told we can use a Count statement later on to get the count , but he insisted can we get the count while executing the insert statement. I would really like to know can we get the data count when executing the insert statement in SQLRPGLE if yes then I would really like to know the answer.
You have two options.
SQLERRD(3)
For a CONNECT for status statement, SQLERRD(3) contains information about the connection status. See CONNECT (type 2) for more information. For INSERT, MERGE, UPDATE, REFRESH, and DELETE, shows the number of rows affected.
For a TRUNCATE statement, the value will be -1.
For a FETCH statement, SQLERRD(3) contains the number of rows fetched.
For the PREPARE statement, contains the estimated number of rows selected. If the number of rows is greater than 2 147 483 647, then 2 147 483 647 is returned.
usage
exec SQL insert ...;
recCnt = sqlErrD(3);
ROW_COUNT
Identifies the number of rows associated with the previous SQL statement that was executed. If the previous SQL statement is a DELETE, INSERT, REFRESH, or UPDATE statement, ROW_COUNT identifies the number of rows deleted, inserted, or updated by that statement, excluding rows affected by either triggers or referential integrity constraints. If the previous SQL statement is a MERGE statement, ROW_COUNT identifies the total number of rows deleted, inserted, and updated by that statement, excluding rows affected by either triggers or referential integrity constraints. If the previous SQL statement is a multiple-row-fetch, ROW_COUNT identifies the number of rows fetched. Otherwise, the value zero is returned.
usage
exec SQL insert ...;
exec SQL GET DIAGNOSTICS :recCnt = ROW_COUNT;