Database Guide

Handling result sets from stored procedures

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.

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.

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

Note:Multple result sets are supported by Smalltalk code only. No part support of multiple result sets is provided.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]