A result set is a collection of rows returned from a stored procedure. When a stored procedure returns a result set, you must be able to fetch the result set into memory. When you run a stored procedure that returns more than one result set, you must be able to fetch multiple result sets into memory.
The class AbtProcedureResultSets is a subclass of AbtResultTable and can handle one result set, multiple result sets, and no result sets. It also handles output values. To retrieve the rows, use iterator methods such as do:, for:, and while:, which are inherited from the AbtResultTable class. These methods retrieve the result sets into one OrderedCollection of rows.
If returning a result set is optional, you must handle the case where no result sets are returned. When no result sets are returned, the collection is empty. If you will never return any result sets, use a method such as the executeQuerySpec method to retrieve the rows.
If one result set is returned, you must fetch the result set into memory. For example, the following Smalltalk code calls a stored procedure that queries a salary database and returns all the salaries above the median salary:
| connection querySpec resultSet medianSalary collection | "Assuming already connected to a database." connection := AbtDbmSystem activeDatabaseConnection. querySpec := AbtQuerySpec new statement: 'CALL msrpsrv2(:medianSalary)'; hostVarShape: (AbtCompoundType new addField: (AbtDatabaseDoubleFloatField new name: 'medianSalary'; nullsOk: true; isFloat: false; procBindType: 2; udt: nil)); yourself. resultSet := connection resultSetsFromQuerySpec: querySpec. medianSalary:= resultSet parameterOutputRow at: 'medianSalary'. collection := OrderedCollection new. resultSet do: [:row | collection add: row ]] ^collection
When multiple result sets are returned, the rows for each result set may have different shapes. To separate the different types of rows, use the #allResultSets method instead of the iterator methods. The #allResultSets method returns an OrderedCollection. This OrderedCollection is a collection of OrderedCollections of rows. (The do:, for:, and while: methods retrieve the result sets into one OrderedCollection of rows.)
For example, the following Smalltalk code calls a stored procedure to query a salary database and return all the salaries above the median salary, all the bonuses above the median bonus, and all the commissions above the median commission.
| connection querySpec resultSet medianSalary medianBonus medianCommission | "Assuming already connected to a database." connection := AbtDbmSystem activeDatabaseConnection. querySpec := AbtQuerySpec new statement: 'CALL msrpsrv2(:medianSalary:medianBonus:medianCommission)'; hostVarShape: (AbtCompoundType new addField: (AbtDatabaseDoubleFloatField new name: 'medianSalary'; nullsOk: true; isFloat: false; procBindType: 2; udt: nil); addField: (AbtDatabaseDoubleFloatField new name: 'medianBonus'; nullsOk: true; isFloat: false; procBindType: 2; udt: nil); addField: (AbtDatabaseDoubleFloatField new name: 'medianCommission'; nullsOk: true; isFloat: false; procBindType: 2; udt: nil); yourself). yourself. resultSet := connection resultSetsFromQuerySpec: querySpec. medianSalary:= resultSet parameterOutputRow at: 'medianSalary'. medianBonus:=resultSet parameterOutputRow at: 'medianBonus'. medianCommission:=resultSet parameterOutputRow at: 'medianCommission'. resultSet allResultSets