Accessing a database from a COM component
To take advantage of MTS or COM+ support for transaction
management, you need to use one of the database interfaces supported
by MTS or COM+ to connect to your database. For more information
about database connections for components developed in PowerBuilder,
see Connecting to Your Database
.
COM components developed in PowerBuilder can use DataStores
to interact with the database. DataStores are nonvisual DataWindow
controls that act just like DataWindow controls except that they
do not have visual attributes. They can be useful in a distributed
application: they give you the ability to perform database processing
on a remote server instead of on each client computer.
For more information about using DataStores for database access
in a transaction server environment, see “Using DataStores”.
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.
MTS and COM+ use 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 26-3 summarizes
how these objects interact.
| 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 Use GetRecordSet to populate an OLEObject |
| DataStore | Use CreateFrom to create a DataStore from an object of type ResultSet or ADOResultSet. Use GenerateResultSet to generate a ResultSet |
Accessing result sets in MTS components from
PowerBuilder clients
When a PowerBuilder client calls an MTS 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:
|
1 |
OLEObject loo_mycomponent<br /> OLEObject loo_ADOrecordset<br /> ADOresultset lrs_ADOresultset<br /> datastore ds_local<br /> integer li_rc<br /> loo_mycomponent = CREATE OLEObject<br /> <br /> li_rc = loo_mycomponent.ConnectToNewObject("PB.Test")<br /> if li_rc <> 0 then<br /> MessageBox("Connect Failed", string(li_rc) )<br /> Return<br /> end if<br /> <br /> // Use an OLEObject to hold ADO Recordset<br /> // returned from method on MTS component<br /> loo_ADOrecordset = loo_mycomponent.GetTestResult()<br /> <br /> // Create an ADOResultSet and get its data<br /> // from OLEObject holding passed ADO Recordset<br /> lrs_ADOresultset = CREATE ADOResultSet<br /> lrs_ADOresultset.SetRecordSet(loo_ADOrecordset)<br /> <br /> // Use CreateFrom to populate DataStore <br /> // from ADOResultSet object<br /> ds_local = CREATE datastore<br /> 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.
|
1 |
ResultSet lrs_resultset<br /> ADOresultset lrs_ADOresultset<br /> OLEObject loo_ADOrecordset<br /> <br /> // Generate a result set from an existing DataStore<br /> ds_source.GenerateResultSet(lrs_resultset)<br /> <br /> // Create a new ADOResultSet object and populate it<br /> // from the generated result set<br /> lrs_ADOresultset = CREATE ADOResultSet<br /> lrs_ADOResultset.SetResultSet(lrs_resultset)<br /> // Pass the data in the ADOResultSet object<br /> <br /> // to an OLEObject you can use as an ADO Recordset<br /> loo_ADOrecordset = CREATE OLEObject<br /> lrs_ADOResultset.GetRecordSet(loo_ADOrecordset)<br /> <br /> // Call native ADO Recordset methods on the OLEObject<br /> loo_ADOrecordset.MoveFirst() |
Returning result sets from MTS and COM+ components
To pass or return result sets from a PowerBuilder user object
that will be deployed to MTS 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 MTS 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:
|
1 |
datastore ds_datastoreresultset lrs_resultset <br /> integer li_rc<br /> <br /> ds_datastore = create datastore<br /> ds_datastore.dataobject = "d_empdata"<br /> ds_datastore.SetTransObject (SQLCA)<br /> IF ds_datastore.Retrieve() = -1 THEN<br /> // report error and return<br /> END IF<br /> <br /> li_rc = ds_datastore.generateresultset(lrs_resultset)<br /> IF li_rc <> 1 THEN<br /> // report error and return<br /> END IF<br /> return lrs_resultset |