How to process large SQL resultsets
A developer asks:
How can our application process huge SQL resultsets?
Is there a way to implement 'cursor-style' processing (iterating through a result set and processing one row at a time) without returning the results to a
The recommended approach is to get only a list of keys to the records in the resultset, and then iterate over the keys, opening one record at a time. After the first record is processed, the clipboard pages it occupied can be deleted (or reused for the second record).
In the absence of a key, or a natural combination of values that constitute the equivalent of a primary key, you can use cursor-style processing by writing Java steps. You can use Java steps to declare the cursor, return one row using the cursor, and then using the Java step or a wrapper activity to call the activity on that one row - and then repeat the process. You will need to manage the database connection directly to hold onto it.
In some situations, case match rules may be applicable. Review Rule-Declare-CaseMatch documentation to see whether this rule type is applicable to your problem.