Accessing a database from a COM or MTS component
To take advantage of MTS support for transaction management,
you need to use the Open Database Connectivity (ODBC) database interface
to connect to a 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 only use ADOResultSet with COM
components.
MTS 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 Jaguar.
This table 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 object with data from an OLEObject that holds an ADO RecordsetUse 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 ADOResultSetUse 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 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 |
1 |
OLEObject loo_ADOrecordset |
1 |
ADOresultset lrs_ADOresultset |
1 |
datastore ds_local |
1 |
integer li_rc |
1 |
1 |
loo_mycomponent = CREATE OLEObject |
1 |
li_rc = loo_mycomponent.ConnectToNewObject("PB.Test") |
1 |
if li_rc <> 0 then |
1 |
MessageBox("Connect Failed", string(li_rc) ) |
1 |
Return |
1 |
end if |
1 |
1 |
// Use an OLEObject to hold ADO Recordset |
1 |
// returned from method on MTS component |
1 |
loo_ADOrecordset = loo_mycomponent.GetTestResult() |
1 |
1 |
// Create an ADOResultSet and get its data |
1 |
// from OLEObject holding passed ADO Recordset |
1 |
lrs_ADOresultset = CREATE ADOResultSet |
1 |
lrs_ADOresultset.SetRecordSet(loo_ADOrecordset) |
1 |
1 |
// Use CreateFrom to populate DataStore |
1 |
// from ADOResultSet object |
1 |
ds_local = CREATE datastoreds_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 |
1 |
ADOresultset lrs_ADOresultset |
1 |
OLEObject loo_ADOrecordset |
1 |
1 |
// Generate a result set from an existing DataStore |
1 |
ds_source.GenerateResultSet(lrs_resultset) |
1 |
1 |
// Create a new ADOResultSet object and populate it |
1 |
// from the generated result set |
1 |
lrs_ADOresultset = CREATE ADOResultSet |
1 |
lrs_ADOResultset.SetResultSet(lrs_resultset) |
1 |
1 |
// Pass the data in the ADOResultSet object |
1 |
// to an OLEObject you can use as an ADO Recordset |
1 |
loo_ADOrecordset = CREATE OLEObject |
1 |
lrs_ADOResultset.GetRecordSet(loo_ADOrecordset) |
1 |
1 |
// Call native ADO Recordset methods on the OLEObject |
1 |
loo_ADOrecordset.MoveFirst() |
Returning result sets from MTS components
To pass or return result sets from a PowerBuilder user object
that will be deployed to MTS, set the data type 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, 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_datastore |
1 |
resultset lrs_resultset |
1 |
integer li_rc |
1 |
1 |
ds_datastore = CREATE datastore |
1 |
ds_datastore.SetTransObject (SQLCA) |
1 |
IF ds_datastore.Retrieve() = -1 THEN |
1 |
// report error and return |
1 |
END IF |
1 |
1 |
li_rc = ds_datastore.generateresultset(lrs_resultset) |
1 |
IF li_rc <> 1 THEN |
1 |
// report error and return |
1 |
END IF |
1 |
return lrs_resultset |