The IP indicates that it is operating in cursor mode by returning DAM_SUCCESS_WITH_RESULT_PENDING instead of DAM_SUCCESS from IP EXECUTE that is called with DAM_SELECT or DAM_SELECT_FOR_UPDATE. If the IP returns DAM_SUCCESS_WITH_RESULT_PENDING, then IP EXECUTE is called again and again with DAM_FETCH until it returns DAM_SUCCESS or DAM_FAILURE.
For optimized cursor implementation, the IP determines how many rows to add in any one call by calling dam_getInfo(DAM_INFO_FETCH_BLOCK_SIZE). In this case, the sequence of operations in IP EXECUTE is as follows:
1. If this is the first time, then the statement type is set to DAM_SELECT or DAM_SELECT_FOR_UPDATE. At this time the IP determines the criteria to use for building rows. To decide how to get information from the query that can be used to reduce the number of rows that are processed, see Optimization. If this is not the first time, then the statement type is DAM_FETCH and the optimization information that was saved with the last state of the statement execution is used.
2. Determine how many rows to process. Call: dam_getInfo(DAM_INFO_FETCH_BLOCK_SIZE).
This value is obtained from the ServiceSQLFetchBlockSize service attribute setting.
3. Get the last state of the statement execution. Call: dam_getIP_hstmt.
4. Build a row with the required column. Call dam_allocRow to allocate a row and then use dam_addValToRow to add values for the columns. Here you may select to build a row with all columns as defined in the schema or to build only the required ones. Use the functions dam_getFirstCol and dam_getNextCol to pick out columns that appear in the various parts of the query. At minimum, you should add only values for columns that appear either in the SELECT part of the query or in the WHERE condition of the query. Find these using dam_getFirstCol(DAM_COL_IN_USE).
5. Evaluate against the expression. Call dam_isTargetRow to evaluate the row against the WHERE condition.
6. Add the row to the result set if it evaluates true. If not, free it. Call dam_addRowToTable to add the row and then increment the number of rows added. Call dam_freeRow to free it.
7. Repeat from Step 4 until the number of requested rows is processed or no more rows remain. If processing is complete, then return DAM_SUCCESS; otherwise, save the current processing state using dam_setIP_hstmt and return DAM_SUCCESS_WITH_RESULTS_PENDING to be called again after the processed rows have been sent to the client. If using optimization information, it should be saved as part of the statement environment.
8. If the processing is canceled before all rows have been processed, IP EXECUTE is called with statement type DAM_CLOSE to close the cursor. At this point, you should clean up any processing setup for this statement.
The number of rows processed depends on how the candidate rows are selected. Optimization techniques that include the use of search lists and restriction lists reduce the number of rows processed. To learn ways to reduce the number of rows you process, see Optimization.
The algorithm for SELECT processing when the results are returned in a single call to IP EXECUTE is also used for UPDATE and DELETE processing, but there is a difference in what occurs when a row matching the WHERE condition is found. For UPDATE processing, the updates to the row are written into the database. For DELETE processing, a row is deleted from the database.
Note:
SQL engine will call the IP to return the entire result set when any of the following conditions is true:
• Query timeout is set for query processing.
• The statement being executed needs post processing. For example, usage of aggregate functions in a query like SELECT AVG(SALARY) FROM EMP.
• The statement being executed is a nested subquery. For example, SELECT ENAME FROM EMPLOYEE WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT).
