As with UPDATE, DELETE and INSERT statements, it is sometimes desirable to execute the same SELECT statement multiple times. For instance, a complicated select statement that uses inner and outer joins to return all of the data associated with a given object . However, there is an addition complication with select statements. How do you return the results?
Given a single set of parameter values, a select statement returns a collection or rows that must be individually fetched into memory. This collection of rows is referred to as a "result set". Consequently, the result of executing a single select statement with multiple value sets, must be a collection of value sets.
Whenever a select statement is executed, an instance of the UtyDB2ResultTable is returned to the application. By default, the first result set is immediately available to the application. However, once the application has finished with that result set, it must send the nextResultSet message to the result table when it wishes access to the next result set. If the current result set is the last result set then the next call and all subsequent calls to the nextResultSet method will return nil.
The following example illustrates how to process multiple result sets using both the nextResultSet and resultSetsDo: methods.
stmt := connection prepareSql: 'SELECT * FROM TEMP WHERE NUM = ?' withParameterTypes: (Array with: UtyDB2IntegerType new). result := stmt executeWithParameterValueSets: #((1)(3)(5)). rows := OrderedCollection new. rows := result rows. [ result nextResultSet notNil ] whileTrue: [ rows addAll: result rows ]. result := stmt executeWithParameterValueSets: #((3)(4)). result resultSetsDo: [ :rs | rows add: rs rows ]. stmt close. rows