Passing result sets

PowerBuilder provides three system objects to handle getting result sets from components running in transaction server environments and returning result sets from PowerBuilder user objects running as transaction server components. These system objects, ResultSet, ResultSets, and ADOResultSet, are designed to simplify the conversion of transaction server result sets to and from DataStore objects and do not contain any state information. They are not designed to be used for database updates. You can use ADOResultSet only with COM components.

COM+ uses ActiveX Data Objects (ADO) RecordSet objects to return result sets. An ADO Recordset object consists of records (rows) and fields (columns) and represents the set of records in a database table.

ADO Recordsets and PowerBuilder system objects

In PowerBuilder you use functions on the PowerBuilder ADOResultSet system object to get and set data that is passed in an ADO Recordset. PowerBuilder clients use OLEObjects to handle ADO Recordsets. You use the CreateFrom and GenerateResultSet functions on the DataStore object to convert the result sets stored in ResultSet objects to and from DataStore objects.

About GenerateResultSet GenerateResultSet has an alternative syntax used for returning result sets when using MASP (Method as Stored Procedure) with EAServer.

Table 27-3 summarizes how these objects interact.

**Table 27-3: Result set objects**
Use a variable of this type	In this way
ResultSet	As the return value of a method (function) defined for a COM component. The data is marshaled as an ADO Recordset.
OLEObject	To hold the ADO Recordset returned from a method on a COM component that returns a ResultSet. The OLEObject can be manipulated using ADO Recordset functions such as MoveFirst.
ADOResultSet	Use SetResultSet to populate an ADOResultSet object with data from a ResultSet object. Use SetRecordSet to populate an ADOResultSet object with data from an OLEObject that holds an ADO Recordset. Use GetRecordSet to populate an OLEObject with data from an ADOResultSet. The OLEObject can be manipulated using ADO Recordset functions such as MoveFirst.
DataStore	Use CreateFrom to create a DataStore from an object of type ResultSet or ADOResultSet. Use GenerateResultSet to generate a ResultSet object from a DataStore object in a method on a transaction server. The ResultSet object can be returned to a client.

Accessing result sets in COM components from PowerBuilder clients

When a PowerBuilder client calls a COM component method that returns an ADO Recordset, the data returned is stored in an OLEObject object. You can manipulate the data in the ADO Recordset using Recordset functions, as described in “Using ADO Recordsets in PowerBuilder”.

To use the data stored in the OLEObject object to populate a DataStore object, create an ADOResultSet object and then call its SetRecordSet function to populate it with data stored in the OLEObject object.

The data in the ADOResultSet object can be used to populate a DataStore object using the CreateFrom DataStore function:

OLEObject loo_mycomponent
OLEObject loo_ADOrecordset
ADOresultset lrs_ADOresultset
datastore ds_local
integer li_rc
loo_mycomponent = CREATE OLEObject

li_rc = loo_mycomponent.ConnectToNewObject("PB.Test")
if li_rc <> 0 then
    MessageBox("Connect Failed", string(li_rc) )
    Return
end if

// Use an OLEObject to hold ADO Recordset
// returned from method on COM component
loo_ADOrecordset = loo_mycomponent.GetTestResult()

// Create an ADOResultSet and get its data
// from OLEObject holding passed ADO Recordset
lrs_ADOresultset = CREATE ADOResultSet
lrs_ADOresultset.SetRecordSet(loo_ADOrecordset)

// Use CreateFrom to populate DataStore 
// from ADOResultSet object
ds_local = CREATE datastore
ds_local.createfrom(lrs_ADOresultset)

Using ADO Recordsets in PowerBuilder

If you want to manipulate an ADO Recordset in PowerBuilder using ADO Recordset methods such as MoveFirst or MoveNext, you can use the SetResultSet and GetRecordSet functions. Use SetResultSet to populate a new ADOResultSet object with data from a ResultSet object, then use GetRecordSet to return the ADO Recordset.

This example generates a result set in a ResultSet object from an existing DataStore object. The ResultSet object is used to populate a new ADOResultSet object. The GetRecordSet function on the ADOResultSet object is used to return an ADO Recordset as an OLEObject that can be used with ADO Recordset methods.

ResultSet lrs_resultset
ADOresultset lrs_ADOresultset
OLEObject loo_ADOrecordset

// Generate a result set from an existing DataStore
ds_source.GenerateResultSet(lrs_resultset)

// Create a new ADOResultSet object and populate it
// from the generated result set
lrs_ADOresultset = CREATE ADOResultSet
lrs_ADOResultset.SetResultSet(lrs_resultset)
// Pass the data in the ADOResultSet object

// to an OLEObject you can use as an ADO Recordset
loo_ADOrecordset = CREATE OLEObject
lrs_ADOResultset.GetRecordSet(loo_ADOrecordset)

// Call native ADO Recordset methods on the OLEObject
loo_ADOrecordset.MoveFirst()

Returning result sets from COM and COM+ components

To pass or return result sets from a PowerBuilder user object that will be deployed to COM or COM+, set the datatype of a function’s argument or return value to ResultSet. When the GenerateResultSet function is called to create a result set from a DataStore object in COM or COM+, the result set is marshaled and returned to the client as an ADO Recordset.

In this example, a DataStore object is created and data is retrieved into it, and then the GenerateResultSet function is used to create a result set that can be returned to a client:

datastore ds_datastoreresultset lrs_resultset 
integer li_rc

ds_datastore = create datastore
ds_datastore.dataobject = "d_empdata"
ds_datastore.SetTransObject (SQLCA)
IF ds_datastore.Retrieve() = -1 THEN
    // report error and return
END IF

li_rc = ds_datastore.generateresultset(lrs_resultset)
IF li_rc <> 1 THEN
    // report error and return
END IF
return lrs_resultset