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:
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.
Handling result sets from stored procedures
Note: A stored procedure returns a result set by opening a cursor and leaving it
open. If more than one cursor is open, you can get multiple result
sets. The client fetches rows from the first open cursor until it is
exhausted and then moves on to the next cursor.
| 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
| 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
Note: Multple result sets are supported by Smalltalk code only. No part
support of multiple result sets is provided.