![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Day 18Using the Remote Data Control and the Remote Data ObjectsToday, you'll learn about two alternative methods for accessing database information using Visual Basic 5: the Remote Data Control (RDC) and the Remote Data Objects (RDOs). Both these alternative methods are designed for reading and updating data stored in relational database management systems (RDBMSs) that are external to Visual Basic and to the Microsoft Jet data engine. Although it is possible to use the standard data control and Microsoft Jet data object collections to access data stored in RDBMS, the RDC and RDOs have properties and methods that make them better suited to manipulating data in remote systems.
Along with the details of the RDC and the RDOs, you learn some of the basics of remote data access in general. These basics are hidden from you when you use the data control, or they do not apply unless you are accessing remote data. You'll learn the meaning and use of these elements:
In today's lesson, you'll learn the properties, methods, and events of the RDC and how you can use these to develop data-entry forms using the same data-bound controls you learned about in the first week's lessons. After you learn the details of the RDC programming tool, you'll build a simple data-entry form based on the RDC. In this chapter, you even use an ODBC definition that links your RDC to a Microsoft Jet Access database. You also learn the details of the RDOs. The RDOs are programming objects similar
to the Microsoft Jet data-access objects (DAOs) you learned about in Week 2. Like
the RDC, the RDO collection has special properties and methods that make it better
suited to accessing data from remote storage systems. In this chapter, you'll learn
how to use the RDO programming objects listed in Table 18.1.
As Table 18.1 shows, most RDOs have a parallel in the Microsoft Jet DAO collections. If you have not read through the chapter covering the Microsoft Jet Database objects (Day 9, "Visual Basic and the Microsoft Jet Engine"), you might want to review that material before you continue with this chapter. When you complete this chapter, you'll be able to create data-bound entry forms using the RDCs and to build Visual Basic programs that manipulate RDBMS data using the RDOs. The Basics of Remote Data AccessBefore getting into the details of the RDC and RDO programming tools, it is important to review a few basic principles of remote data access. When you use the Microsoft Jet DAOs or the standard data control, you usually do not need to deal with some of the issues covered here. The three concepts covered here are cursor drivers, dataset types, and lock types. Manipulating the parameters of these three properties affects the type of dataset you are working with and the types of operations you can perform on that data. By default, all remote connections to data are non-updatable (read-only) datasets. You can change this behavior by manipulating the three properties covered here. Cursor DriversYou use the cursor drivers to define the way in which you can move within a set of data. When you connect to data using the standard data control or the Microsoft Jet DAOs, you can, by default, move forward and backward in the dataset and move to any record in the collection. You can use any of the Move and Find methods (MoveFirst, MoveLast, MoveNext, MovePrevious, FindFirst, FindLast, FindNext, and FindPrevious), for example. In order to have this movement capability, the database engine must be able to keep track of all the records in the collection and their place in the dataset. This process of keeping track of the location of the data pointer in the dataset is called cursor management. You can use two primary locations to keep track of the cursor location: the client workstation or the database server. Under the RDC and RDOs, the local workstation version of cursor management is handled by the ODBC driver. The server-side cursor management is handled by the database server that holds the data. Under RDC and RDOs, there are two other possible settings for cursor management: Client Batch and None. You can use the Client Batch option with advanced data servers that allow multiple data requests to be sent simultaneously over the same connection. In this case, you can batch up multiple SQL statements in a single string and send them to the server at one time. The server manages the requests and reports results back to you as each SQL statement is completed. You also can choose to use no cursor driver when accessing remote data. This results in the server sending you a single record from the dataset each time you request it. You cannot request previous records from the server and, if you want to start at the top of the collection, you must restart the query. This is the most limited cursor management available for a remote connection.
Cursor drivers can have five possible settings when you are working with RDC and
RDOs. Table 18.2 shows these values and their meanings.
As you can see in Table 18.2, the default option for RDC/RDOs is to allow rdoEngine to select the most appropriate cursor available. When rdUseIfNeeded is in place, RDC/RDOs attempt to use server-side cursors if they are available (rdUseServer or rdUseClientBatch if Batch mode is in force). If the RDBMS does not support server-side cursor management, RDC/RDOs use the client workstation cursor manager (rdUseOdbc). The rdUseNone option is used only if the value is explicitly selected. Again, the important thing to remember about cursor drivers is that they govern the way you can navigate the dataset. The other important aspect of cursor drivers is that, with RDC/RDOs, you can select the driver you prefer: client-side or server-side. The only caveat to all this is that the data source (RDBMS) must support your cursor request in order for you to be able to select certain server-side options. Dataset TypeThe selection of cursor drivers is just one of the options you must determine
when accessing remote data. Another important parameter is the dataset type property
of the connection. Several types of datasets can be returned by the remote data source.
Table 18.3 outlines these types.
The information in Table 18.3 deserves some additional comment. Remember that the primary work of dataset management from the workstation point of view is gaining access to the actual rows of data stored in the remote system. The most efficient method for gaining access is to receive a set of row pointers from the RDBMS, not to actually receive the complete rows of data. In this way, the RDBMS can allow multiple users to have access to the same set of records without having to deal with major synchronization work if more than one user attempts to update the same row of data. For this reason, RDC/RDOs support the use of rdOpenKeyset datasets. These are sets of data that contain not just data rows, but also key-set pointers to other rows in the requested dataset. rdOpenKeysets are the remote data-access versions of Microsoft Jet Dynaset-type datasets. The rdOpenDynamic datasets do not support keys, but they do act as dynamically changing sets of data. These dynamic datasets reflect newly added or deleted records just as rdOpenKeyset datasets, but they do not support the use of bookmarks. This is because the rdOpenDynamic datasets are kept dynamic through the use of recurring refreshes of the data membership from the remote data source. Although this method is accurate, it is hardly efficient. If you have large sets of data, using the rdOpenDynamic option can result in decreased throughput, because all data members are shipped to the client each time the set is refreshed. It also is important to note that rdOpenStatic datasets are updatable. Even though their membership is kept static (there is no constant refresh from the remote data source), it still is possible to update the records in the set. These datasets act much like an updatable version of the Microsoft Jet Snapshot dataset. Lock TypesThe final concept that deserves special attention when dealing with remote data
access is the lock type used to manage dataset updates. With Microsoft Jet data access,
you have two options: pessimistic (lock at start of edit) and optimistic (lock at
start of update). RDC/RDOs offer a few additional variations to these two basic options.
Table 18.4 shows the lock-type options with RDC/RDOs.
The type of locking mechanism used can have a great effect on the performance of your application and the capability of others to access the shared data. The rdConcurReadOnly option allows anyone else to access the same data your application is using. Your application cannot update the dataset unless you are using action queries, however. The rdConcurLock option provides the greatest degree of locking. As soon as your dataset is created, all buffered rows in the dataset are locked. Because locks occur on pages, this can result in hundreds of record locks while your application browses the dataset. The rdConcurRowver and rdConcurValues options allow optimistic locking schemes. In the rdConcurRowver option, each record's ID value (usually, the TimeStamp column) is checked at the time the row is updated. If the ID has changed since your application retrieved the data, an error is reported. In the rdConcurValues option, each value in the row's columns is checked against the original value. If the value has changed since your application received the dataset, an error is reported. The least intrusive form of locking for read/write datasets is the rdConcurRowver or rdConcurValues option. The most secure form of locking for read/write datasets is the rdConcurLock option. An advantage that RDC/RDOs have over Microsoft Jet is that you easily can tune the number of rows in the row set by using the RowsetSize property. If you set this property sufficiently low, you can use the rdConcurLock option without adversely affecting other users who are attempting to access the remote data. Now that you understand the basics of accessing remote data, you are ready to begin using the RDC and RDO tools to build Visual Basic programs that read and update remote data sources. Building an ODBC DefinitionBefore you can complete any of the projects in this chapter, you need to build an ODBC connection to the BOOKS5.MDB database that ships on the CD-ROM with this book. After you build this ODBC data source definition, you'll be able to use both RDC and RDO programming tools to access the BOOKS5.MDB database.
To build an ODBC definition, you need to call up the ODBC Administrator. Follow these steps:
|
Property | Type | Default Setting | Description |
Connection | rdoConnection | <none> | Contains an object reference to the RDO Connection object created by the RDC. You can use this as you use the Database property of the standard data control. |
CursorDriver | Integer | rdUseIfNeeded (0) | Controls the source and behavior of the cursor manager. |
DataSourceName | String | <none> | Specifies the name of the ODBC data source you want to access. This is the RDO equivalent of the database name. |
EditMode | Integer | rdEditNone (0) | Indicates whether an edit or AddNew oper-ation is in effect. You can use this to deter-mine whether an Update method must be used to complete a pending action. |
Environment | rdoEnvironment | <none> | Contains an object reference to the RDO Environment object created by the RDC. You can use this as you use the Workspace object in Microsoft Jet data access. |
KeysetSize | Integer | 100 | Specifies the number of rows in the keyset buffer. Using this value and the MaxRows and RowsetSize properties can affect the way records are buffered and locked. |
LockType | Integer | rdConcurReadOnly (0) | Controls how records are locked for update. |
LoginTimeOut | Integer | 0 | Using a value greater than zero indicates the length of time (in seconds) that RDC/RDOs wait before reporting a time-out error when trying to log onto the remote data source. A typical LAN connection time-out value is 15 seconds. RAS/Internet connections may need a longer time-out setting. |
LogMessages | String | <none> | Setting this value to a valid drive/path/filename enables the creation of a trace file for ODBC con-versations. This file can get quite long and should be used only for temporary debugging of ques-tionable connections. |
MaxRows | Integer | -1 | Controls how many rows are affected by an action. When set to -1 (default) all rows matching the criteria are affected. You can set this value to 1 to ensure that only one record is updated when us-ing an UPDATE query. |
Prompt | Integer | rdDriverPrompt (0) | Controls the behav-ior of the ODBC logon process. Set-ting this value to rdDriverNoPrompt suppresses the ODBC logon screen. You can use this set-ting to log onto the data source without asking the user for additional parameters. If the values set in the programmatic logon are invalid, an error is reported. |
Resultset | rdoResultset | <none> | Contains an object reference to the rdoResultset object created by the RDC. you can use this as you use the Recordset object of the standard data control. |
ResultsetType | Integer | rdOpenStatic (0) | Controls the type of dataset returned by the RDC. The rdOpenStatic option returns an updatable dataset with unchanging membership; the rdOpenKeyset opens an updatable dataset with changing mem-bership. |
RowsetSize | Integer | 100 | Controls the number of rows buffered to your application. This is also the num-ber of rows locked when using pessimistic locking (rdConcurLock). |
SQL | String | <none> | Specifies the SQL statement used to populate the dataset for the RDC. This is the RDC equivalent of the RecordSource property of the stan-dard data control. |
StillExecuting | Boolean | False | Indicates whether the dataset is still in the process of being created. You can check this periodically on long data connections. |
Transactions | Boolean | False | Indicates whether the remote data source supports the use of BeginTrans, CommitTrans, and RollbackTrans methods. You can check this before you attempt to use these methods. |
Several methods are associated with the RDC. Most of these methods have counterparts
with the standard data control. Table 18.6 lists the RDC methods.
Table 18.6. The RDC methods.
Method | Function |
BeginTrans, | Enables programmers to provide transaction management |
CommitTrans, | for RDC actions. This improves data integrity on action queries RollbackTrans and can speed up processing on single-row updates. |
Cancel | Cancels any pending Query, Edit, AddNew, or Delete operation. |
Refresh | Repopulates the dataset. |
UpdateControls | Refreshes the data-bound controls with the contents of the dataset. |
UpdateRows | Refreshes the dataset with the values in the data-bound controls. |
The RDC offers a set of unique events that are similar to the events supported by the Microsoft Jet data control: Validate, Reposition, and Error. The RDC has an additional event not supported by the Microsoft Jet data control: the QueryCompleted event. This event is fired after the dataset has returned successfully from the remote data source. This event can be used to alert users of the completion of delayed queries because of slow connections or a large dataset size.
The events, methods, and properties covered in the previous sections are illustrated in the RDC data-entry project in the next section.
In this section, you create a simple data-entry form using the RDC. This form illustrates the use of most of the properties, methods, and events covered in previous sections of this chapter. If you haven't already done so, start Visual Basic 5.0 and select a new, Standard EXE project.
Before you begin laying out the data-entry form, be sure to load the RDC by choosing
Project | Components. The Components dialog box appears, as shown in Figure 18.4.
Figure
18.4. Loading the RDC.
After adding the RDC to the project, create your data-entry form based on Figure
18.5 and Table 18.7.
Table 18.7. Laying out the RDC data-entry form.
Control | Property | Setting |
VB.Form | Name | frmDataEntry |
Caption | "Form1" | |
ClientHeight | 2250 | |
ClientLeft | 60 | |
ClientTop | 345 | |
ClientWidth | 6510 | |
StartUpPosition | 3 `Windows Default | |
VB.CommandButton | Name | cmdBtn |
Caption | "&Close" | |
Height | 300 | |
Index | 4 | |
Left | 5160 | |
Top | 1500 | |
Width | 1200 | |
VB.CommandButton | Name | cmdBtn |
Caption | "&Refresh" | |
Height | 300 | |
Index | 3 | |
Left | 3900 | |
Top | 1500 | |
Width | 1200 | |
VB.CommandButton | Name | cmdBtn |
Caption | "&Update" | |
Height | 300 | |
Index | 2 | |
Left | 2640 | |
Top | 1500 | |
Width | 1200 | |
VB.CommandButton | Name | cmdBtn |
Caption | "&Delete" | |
Height | 300 | |
Index | 1 | |
Left | 1380 | |
Top | 1500 | |
Width | 1200 | |
VB.CommandButton | Name | cmdBtn |
Caption | "&Add" | |
Height | 300 | |
Index | 0 | |
Left | 120 | |
Top | 1500 | |
Width | 1200 | |
VB.TextBox | Name | txtDOB |
DataSource | "MSRDC1" | |
Height | 285 | |
Left | 1440 | |
Text | "Text2" | |
Top | 960 | |
Width | 1200 | |
VB.TextBox | Name | txtName |
DataSource | "MSRDC1" | |
Height | 285 | |
Left | 1440 | |
Text | "Text1" | |
Top | 540 | |
Width | 2400 | |
MSRDC.MSRDC | Name | MSRDC1 |
Align | Bottom | |
Height | 330 | |
Left | 0 | |
Top | 1920 | |
Width | 6510 | |
VB.Label | Name | lblDOB |
Caption | "DOB" | |
Height | 255 | |
Left | 120 | |
Top | 1020 | |
Width | 1200 | |
VB.Label | Name | lblName |
Caption | "Name" | |
Height | 255 | |
Left | 120 | |
Top | 600 | |
Width | 1200 | |
VB.Label | Name | lblAuthorID |
Caption | "Author ID" | |
Height | 255 | |
Left | 120 | |
Top | 180 | |
Width | 1200 | |
VB.Label | Name | lblAUID |
BorderStyle | 1 `Fixed Single | |
Caption | "Label1" | |
DataSource | "MSRDC1" | |
Height | 255 | |
Left | 1440 | |
Top | 180 | |
Width | 1200 |
Now save the project as PRJRDC.VBP, the main form as FRMDATAENTRY.FRM, and the support
form as FRMMSGS.FRM. Now you're ready to add Visual Basic code to the forms.
First, you need to add code to two events in the frmMsgs form. Listing 18.1 shows the code for the Form_Load and Form_Resize events. Add this code to your support form.
Private Sub Form_Load() ` Me.Caption = "MSRDC Event Messages" txtMsg.Text = "" ` End Sub Private Sub Form_Resize() ` ` make text box fill the form ` If Me.WindowState <> vbMinimized Then txtMsg.Left = 1 txtMsg.Top = 1 txtMsg.Width = Me.ScaleWidth txtMsg.Height = Me.ScaleHeight End If ` End Sub
The code in Listing 18.1 sets up the initial caption and textbox on the form and
then forces the textbox to fill the entire frmMsgs form space. This form displays
progress messages reported by the main data-entry form. You can save and close this
form. This is all the code you need to add to the frmMsgs form.
Now open the main data-entry form (frmDataEntry) and add the code from Listing 18.2 to the Form_Load event of the form.
Private Sub Form_Load() ` ` project setup actions ` frmMsgs.Show ` launch message window ` ` set up RDC MSRDC1.DataSourceName = "RDC Books5" MSRDC1.SQL = "SELECT * FROM Authors" MSRDC1.CursorDriver = rdUseClientBatch MSRDC1.LockType = rdConcurBatch MSRDC1.ResultsetType = rdOpenStatic MSRDC1.Refresh ` ` bind inputs to rdc lblAUID.DataField = "AUID" txtName.DataField = "Name" txtDOB.DataField = "DOB" ` ` set form title Me.Caption = "Remote Data Control Demo" ` End Sub
The code in Listing 18.2 sets up the basic parameters to the RDC, uses the Refresh
method to fetch the data, and then updates the DataField properties of the bound
input controls in order to link them to the dataset returned in the RDC. Note that
you are using the rdOpenStatic option to create an updatable, fixed-membership dataset.
Now add the code from Listing 18.3 to the cmdBtn_Click event of the form. This single set of code handles all the command buttons, because the buttons were added as a control array.
Private Sub cmdBtn_Click(Index As Integer) ` ` handle button selections ` Select Case Index Case 0 ` add MSRDC1.Resultset.AddNew Case 1 ` delete MSRDC1.Resultset.Delete Case 2 ` update MSRDC1.UpdateRow Case 3 ` refresh MSRDC1.UpdateControls Case 4 ` close Unload Me End Select ` End Sub
Listing 18.4 shows the code you need to add to the four RDC-related events. Note
that all four of these routines use a PostMsg subroutine and that the Validate event
calls a ShowAction function. You build these routines in the next step of the project.
Private Sub MSRDC1_Error(ByVal Number As Long, Description As String, ByVal ÂScode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal ÂHelpContext As Long, CancelDisplay As Boolean) ` PostMsg "MSRDC1_Error - Number=" & CStr(Number) & ", Description = " & ÂDescription & ", Scode = " & CStr(Scode) & ", Source = " & Source & ", ÂHelpFile = " & HelpFile & ", HelpContext = " & CStr(HelpContext) & ", ÂCancelDisplay = " & CStr(CancelDisplay) ` End Sub Private Sub MSRDC1_QueryCompleted() ` PostMsg "MSRDC1_QueryCompleted" ` End Sub Private Sub MSRDC1_Reposition() ` PostMsg "MSRDC1_Reposition" ` End Sub Private Sub MSRDC1_Validate(Action As Integer, Reserved As Integer) ` PostMsg "MSRDC1_Validate - Action=" & ShowAction(Action) & ", Reserved=" & ÂCStr(Reserved) ` End Sub
After adding the code for all the events, add the code in Listing 18.5 to the form
to create the ShowAction function. This function converts the integer value passed
from the Validate event into a friendly string name. This is used to display progress
messages in the frmMsg form.
Public Function ShowAction(intAction As Integer) As String ` ` convert numeric action value ` into friendly string value ` Dim strMsg As String ` Select Case intAction Case rdActionCancel `0 strMsg = "Cancel" Case rdActionMoveFirst `1 strMsg = "MoveFirst" Case rdActionMovePrevious `2 strMsg = "MovePrevious" Case rdActionMoveNext `3 strMsg = "MoveNext" Case rdActionMoveLast `4 strMsg = "MoveLast" Case rdActionAddNew `5 strMsg = "AddNew" Case rdActionUpdate `6 strMsg = "Update" Case rdActionDelete `7 strMsg = "Delete" Case rdActionFind `8 strMsg = "Find" Case rdActionBookmark `9 strMsg = "Bookmark" Case rdActionClose '10 strMsg = "Close" Case rdActionUnload '11 strMsg = "Unload" Case rdActionUpdateAddNew '12 strMsg = "UpdateAddNew" Case rdActionUpdateModified '13 strMsg = "UpdateModified" Case rdActionRefresh '14 strMsg = "Refresh" Case rdActionCancelUpdate '15 strMsg = "CancelUpdate" Case rdActionBeginTransact '16 strMsg = "BeginTrans" Case rdActionCommitTransact '17 strMsg = "CommitTrans" Case rdActionRollbackTransact '18 strMsg = "RollbackTrans" Case rdActionNewParameters '19 strMsg = "NewParameters" Case rdActionNewSQL '20 strMsg = "NewSQL" End Select ` ShowAction = strMsg ` End Function
Notice that the Validate event of the RDC has several action values that are not
available with the standard Microsoft Jet data control. These additional values come
in handy when managing remote data connections.
Now add the PostMsg method to your data-entry form. Listing 18.6 shows the code for this support routine.
Public Sub PostMsg(strMsg As String) ` ` post a message to a text box ` Static lngCounter As Long ` lngCounter = lngCounter + 1 frmMsgs.txtMsg = Format(lngCounter, "000") & ":" & strMsg & vbCrLf & ÂfrmMsgs.txtMsg ` End Sub
As mentioned earlier, the sole purpose of this routine is to post messages to the
supporting form so that you can see the progress of data requests using the RDC.
Now save the form (FRMDATAENTRY.FRM) and the project (PRJRDC.VBP) before running
it.
When you run the project, the message form and the data form appear. Note the
messages that have been posted to the support form. Modify the first record (change
the value of the Name field) and move the record pointer. You see a number of messages
appear in the support form indicating the validation of the new data, the updating
of the modified data, and the repositioning of the record pointer on the new record
(see Figure 18.6).
Figure
18.6. Running the RDC data-entry project.
That's all there is to creating data-entry forms using data-bound controls and the
RDC. In the next section, you'll learn how to create database applications that use
the RDOs instead of a data-bound control set.
Creating database applications that use the RDOs is quite similar to programming with the Microsoft Jet DAOs. Most of the material in this section of the chapter refers to Chapter 9. If you have not already completed that chapter, you might want to review it before you continue with this chapter.
The RDO programming objects are arranged in a hierarchy of collections. The top-level object is the rdoEngine object. This is the programmatic access to the Microsoft remote data engine used by Visual Basic 5 to gain access to all remote data. All requests using the RDO objects are handled by the rdoEngine.
The rdoEngine creates one or more rdoEnvironment objects. These objects are used to manage the details of the various connections to datasets. rdoEnvironment objects can create rdoConnection objects. These are the actual connections to existing data sources at the remote data system. Each rdoConnection can create one or more rdoResultset objects. The rdoResultset object contains the actual rows of data. You also can create and access rdoQuery objects or rdoTable objects from the rdoConnection object. The rdoQuery object also has an rdoParameter object to manage the passing of parameters during the processing of queries.
In the sections that follow, you code examples of each of the RDO objects, inspect their properties, and exercise their methods. To do this, start a new Visual Basic 5 Standard EXE project. Set the project name to prjRDO and the form name to frmRDO. Save the empty form as FRMRDO.FRM and the project as PRJRDO.VBP. In each of the following sections, you add code that illustrates each of the RDO objects.
The rdoEngine object is the top-level object in the RDO collection. This object has only a handful of parameters and has no collection of its own. You can have only one rdoEngine instance in your Visual Basic programs. Add a new command button to your form. Set its Name property to cmdRDOEngine and its Caption to RDO Engine. Now add the code in Listing 18.7 to the cmdRDOEngine_Click event.
Private Sub cmdRDOEngine_Click() ` ` show rdo engine properties ` Dim strMsg As String Dim rdoEng As rdoEngine Dim aryCursorDriver As Variant ` aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone") ` Set rdoEng = rdoEngine ` strMsg = strMsg & "rdoDefaultCursorDriver=" & ÂaryCursorDriver(rdoEng.rdoDefaultCursorDriver) & vbCrLf strMsg = strMsg & "rdoDefaultErrorThreshold=" & ÂCStr(rdoEng.rdoDefaultErrorThreshold) & vbCrLf strMsg = strMsg & "rdoDefaultLoginTimeOut=" & ÂCStr(rdoEng.rdoDefaultLoginTimeout) & vbCrLf strMsg = strMsg & "rdoDefaultPassword=" & rdoEng.rdoDefaultPassword & vbCrLf strMsg = strMsg & "rdoDefaultUser=" & rdoEng.rdoDefaultUser & vbCrLf strMsg = strMsg & "rdoLocaleID=" & CStr(rdoEng.rdoLocaleID) & vbCrLf strMsg = strMsg & "rdoVersion=" & CStr(rdoEng.rdoVersion) & vbCrLf ` MsgBox strMsg, vbInformation, "RDOEngine" ` Set rdoEng = Nothing ` End Sub
Save and run the project. After you click the RDO Engine command button, you should
see something like the message in Figure 18.7.
Figure
18.7. Displaying the RDO Engine properties.
The rdoEnvironment object contains information about the current environment for data connections. The rdoEnvironment object is the RDO equivalent of the Microsoft Jet Workspaces object. rdoEnvironment objects can be used to group rdoConnection objects together for transaction-management purposes as well.
You can create multiple rdoEnvironment objects under the rdoEngine object. All rdoEnvironment objects are contained in the rdoEnvironments collection object.
Add a new button to the form. Set its Name property to cmdRDOEnvironment and
its Caption to Environment. Now enter the code from Listing 18.8 to the cmdRDOEnvironment_Click
event.
Private Sub cmdRDOEnvironment_Click() ` ` show environment collection as properties ` Dim strMsg As String Dim rdoEnv As rdoEnvironment Dim rdoNewEnv As rdoEnvironment Dim aryCursorDriver As Variant ` aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone") ` Set rdoNewEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") ` For Each rdoEnv In rdoEngine.rdoEnvironments strMsg = strMsg & "rdoCursorDriver=" & ÂaryCursorDriver(rdoEnv.CursorDriver) & vbCrLf strMsg = strMsg & "hEnv=" & CStr(rdoEnv.hEnv) & vbCrLf strMsg = strMsg & "LoginTimeOut=" & CStr(rdoEnv.LoginTimeout) & vbCrLf strMsg = strMsg & "Name=" & rdoEnv.Name & vbCrLf `strMsg = strMsg & "Password=" & rdoEnv.Password & vbCrLf strMsg = strMsg & "UserName=" & rdoEnv.UserName & vbCrLf strMsg = strMsg & vbCrLf Next ` MsgBox strMsg, vbInformation, "rdoEnvironment" ` rdoNewEnv.Close Set rdoEnv = Nothing Set rdoNewEnv = Nothing ` End Sub
After you save and run the project, click the Environment button to display a dialog
box similar to the one in Figure 18.8.
Figure
18.8. Displaying the rdoEnvironment properties.
Notice that a default environment always is available to your application. It is
advisable, however, to create your own environment before you attempt to establish
a connection to remote data sources.
The rdoConnection object contains the details needed to establish a connection between your application and the remote data source. The rdoConnection object is similar to the Microsoft Jet Database object.
You can create more than one rdoConnection object under the same rdoEnvironment object. All rdoConnection objects are stored in the rdoConnections collection.
Add a new button to the form. Set its Name to cmdRDOConnection and its Caption to Connection. Then add the code in Listing 18.9 to the cmdRDOConnection_Click event.
Private Sub cmdRDOConnection_Click() ` ` show resultsets ` Dim strMsg As String Dim rdoEnv As rdoEnvironment Dim rdoCon As rdoConnection Dim rdoNewCon As rdoConnection Dim aryCursorDriver As Variant ` aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone") ` Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") Set rdoNewCon = rdoEnv.OpenConnection("RDC Books5") ` For Each rdoCon In rdoEnv.rdoConnections strMsg = strMsg & "AsyncCheckInterval=" & ÂCStr(rdoCon.AsyncCheckInterval) & vbCrLf strMsg = strMsg & "Connect=" & rdoCon.Connect & vbCrLf strMsg = strMsg & "CursorDriver=" & aryCursorDriver(rdoCon.CursorDriver) Â& vbCrLf strMsg = strMsg & "hDbc=" & CStr(rdoCon.hDbc) & vbCrLf strMsg = strMsg & "LoginTimeOut=" & CStr(rdoCon.LoginTimeout) & vbCrLf strMsg = strMsg & "LogMessages=" & rdoCon.LogMessages & vbCrLf strMsg = strMsg & "Name=" & rdoCon.Name & vbCrLf strMsg = strMsg & "QueryTimeOut=" & CStr(rdoCon.QueryTimeout) & vbCrLf strMsg = strMsg & "RowsAffected=" & CStr(rdoCon.RowsAffected) & vbCrLf strMsg = strMsg & "StillConnecting=" & CStr(rdoCon.StillConnecting) & ÂvbCrLf strMsg = strMsg & "StillExecuting=" & CStr(rdoCon.StillExecuting) & ÂvbCrLf strMsg = strMsg & "Transactions=" & CStr(rdoCon.Transactions) & vbCrLf strMsg = strMsg & "Updatable=" & CStr(rdoCon.Updatable) & vbCrLf strMsg = strMsg & "Version=" & rdoCon.Version & vbCrLf Next ` MsgBox strMsg, vbInformation, "rdoConnection" ` rdoNewCon.Close rdoEnv.Close Set rdoEnv = Nothing Set rdoCon = Nothing Set rdoNewCon = Nothing ` End Sub
Save and run the project. After you click the Connection button, you'll see a display
of all the default properties of an rdoConnection object, as shown in Figure 18.9.
Figure
18.9. Displaying the rdoConnection properties.
Note that the Version property of the rdoConnection object reports the version number
of the ODBC driver used to establish the connection to the remote data source.
After a connection is established between your program and the remote data, you can use the rdoResultset object to create a collection of records. The rdoResultset object is the RDO equivalent of the Microsoft Jet Recordset object. rdoResultset contains a direct reference to all the rows and columns in the dataset.
You can have multiple rdoResultset objects for each rdoConnection object. All Resultset objects are stored in the Resultsets collection of the rdoConnection object.
Add a new button to the project. Set its Name property to cmdRDOResultset and its Caption to Resultset. Now add the code in Listing 18.10 to the cmdRDOResultset_Click event.
Private Sub cmdRDOResultset_Click() ` ` show result set properties ` Dim rdoEnv As rdoEnvironment Dim rdoCon As rdoConnection Dim rdoRS As rdoResultset Dim strMsg As String Dim aryEditMode As Variant Dim aryLockType As Variant Dim aryType As Variant ` aryEditMode = Array("rdEditNone", "rdEditInProgress", "rdEditAdd") aryLockType = Array("rdConcurReadOnly", "rdConcurLock", "rdConcurRowVer", Â"rdConcurValues", "rdConCurBatchEdit") aryType = Array("rdOpenForwardOnly", "rdOpenKeyset", "rdOpenDynamic", Â"rdOpenStatic") ` ` set up env/con/rs Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") Set rdoCon = rdoEnv.OpenConnection("RDC Books5") Set rdoRS = rdoCon.OpenResultset("SELECT * FROM Authors") ` ` show properties of the rdoRS strMsg = strMsg & "AbsolutePosition=" & CStr(rdoRS.AbsolutePosition) & ÂvbCrLf strMsg = strMsg & "BOF=" & CStr(rdoRS.BOF) & vbCrLf strMsg = strMsg & "Bookmark=" & rdoRS.Bookmark & vbCrLf strMsg = strMsg & "Bookmarkable=" & CStr(rdoRS.Bookmarkable) & vbCrLf strMsg = strMsg & "EditMode=" & aryEditMode(rdoRS.EditMode) & vbCrLf strMsg = strMsg & "EOF=" & CStr(rdoRS.EOF) & vbCrLf strMsg = strMsg & "hStmt=" & CStr(rdoRS.hStmt) & vbCrLf strMsg = strMsg & "LastModified=" & rdoRS.LastModified & vbCrLf strMsg = strMsg & "LockEdits=" & CStr(rdoRS.LockEdits) & vbCrLf strMsg = strMsg & "LockType=" & aryLockType(rdoRS.LockType) & vbCrLf strMsg = strMsg & "Name=" & rdoRS.Name & vbCrLf strMsg = strMsg & "PercentPosition=" & CStr(rdoRS.PercentPosition) & vbCrLf strMsg = strMsg & "Restartable=" & CStr(rdoRS.Restartable) & vbCrLf strMsg = strMsg & "RowCount=" & CStr(rdoRS.RowCount) & vbCrLf strMsg = strMsg & "Status=" & CStr(rdoRS.Status) & vbCrLf strMsg = strMsg & "StillExecuting=" & CStr(rdoRS.StillExecuting) & vbCrLf strMsg = strMsg & "Transactions=" & CStr(rdoRS.Transactions) & vbCrLf strMsg = strMsg & "Type=" & aryType(rdoRS.Type) & vbCrLf strMsg = strMsg & "Updatable=" & CStr(rdoRS.Updatable) & vbCrLf ` MsgBox strMsg, vbInformation, "rdoResultset" ` rdoRS.Close rdoCon.Close rdoEnv.Close Set rdoRS = Nothing Set rdoCon = Nothing Set rdoEnv = Nothing ` End Sub
Note the use of the OpenResultset method of the rdoConnection object to create the
rdoResultset. Save and run the project. After you click the Resultset button, you'll
see a display similar to the one in Figure 18.10.
Figure
18.10. Displaying the rdoResultset properties.
You also can open an rdoTable object from the rdoConnection object. This object contains information about each of the columns in the base table that exist on the remote data source. You can use the rdoTables collection to get a listing of all the base objects available through the rdoConnection. The rdoTables collection returns more than just the defined base tables. You also receive all the stored queries (views) available at the remote data source.
WARNING: The rdoTable object is included in Visual Basic 5.0 for backward compatibility with previous versions of the RDO Engine. Although the rdoTable object works as expected in this version of Visual Basic, it might not be supported in future versions of Visual Basic.
Add a new button to the project. Set its Name property to cmdRDOTables and its Caption to RDO Tables. Now add the code in Listing 18.11 to the cmdRDOTables_Click event.
Private Sub cmdRDOTables_Click() ` ` get rdo table collection ` Dim rdoEnv As rdoEnvironment Dim rdoCon As rdoConnection Dim rdoTbl As rdoTable Dim strMsg As String ` ` set env/con Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") Set rdoCon = rdoEnv.OpenConnection("RDC Books5") ` ` update the tables collection rdoCon.rdoTables.Refresh ` ` show table properties For Each rdoTbl In rdoCon.rdoTables strMsg = strMsg & "Name=" & rdoTbl.Name & vbCrLf strMsg = strMsg & "RowCount=" & CStr(rdoTbl.RowCount) & vbCrLf strMsg = strMsg & "Type=" & CStr(rdoTbl.Type) & vbCrLf strMsg = strMsg & "Updatable=" & CStr(rdoTbl.Updatable) strMsg = strMsg & vbCrLf ` MsgBox strMsg, vbInformation, "rdoTable" strMsg = "" Next ` rdoCon.Close rdoEnv.Close Set rdoTbl = Nothing Set rdoCon = Nothing Set rdoEnv = Nothing ` End Sub
Notice the use of the Refresh method on the rdoTables collection. This is required
if you want to get a list of all the table and view objects available from the data
source. The rdoTables collection is not automatically refreshed when you create the
rdoConnection object.
Save and run the project. After you click the RDO Tables button, you'll see a
list of the tables and views available from the data source. Figure 18.11 shows one
of those displays.
Figure
18.11. Inspecting the rdoTable properties.
The rdoColumns object contains detailed information about the contents and pro-
perties of each data column in the rdoTable or rdoResultset object. The rdoColumn
object corresponds to the Microsoft Jet Field object. Usually, more than one rdoColumn
object exists for each rdoTable or rdoResultset object. All rdoColumn objects are
stored in the rdoColumns collection.
Add a new button to the project. Set its Name to cmdRDOColumns and its Caption to Columns. Now add the code in Listing 18.12 to the cmdRDOColumns_Click event.
Private Sub cmdRDOColumns_Click() ` ` show rdo columns collection ` Dim rdoEnv As rdoEnvironment Dim rdoCon As rdoConnection Dim rdoTbl As rdoTable Dim rdoCol As rdoColumn Dim strMsg As String ` ` set up connection Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") Set rdoCon = rdoEnv.OpenConnection("RDC Books5") ` ` get table info rdoCon.rdoTables.Refresh Set rdoTbl = rdoCon.rdoTables("Authors") ` ` get column info For Each rdoCol In rdoTbl.rdoColumns strMsg = strMsg & "AllowZeroLength=" & CStr(rdoCol.AllowZeroLength) & ÂvbCrLf strMsg = strMsg & "Attributes=" & Hex(rdoCol.Attributes) & vbCrLf strMsg = strMsg & "ChunkRequired=" & CStr(rdoCol.ChunkRequired) & vbCrLf strMsg = strMsg & "Name=" & rdoCol.Name & vbCrLf strMsg = strMsg & "OrdinalPosition=" & CStr(rdoCol.OrdinalPosition) & ÂvbCrLf strMsg = strMsg & "Required=" & CStr(rdoCol.Required) & vbCrLf strMsg = strMsg & "Size=" & CStr(rdoCol.Size) & vbCrLf strMsg = strMsg & "SourceColumn=" & rdoCol.SourceColumn & vbCrLf strMsg = strMsg & "SourceTable=" & rdoCol.SourceTable & vbCrLf strMsg = strMsg & "Type=" & CStr(rdoCol.Type) & vbCrLf strMsg = strMsg & "Updatable=" & CStr(rdoCol.Updatable) & vbCrLf ` MsgBox strMsg, vbInformation, "rdoColumn" strMsg = "" ` Next ` rdoCon.Close rdoEnv.Close Set rdoCol = Nothing Set rdoTbl = Nothing Set rdoCon = Nothing Set rdoEnv = Nothing ` End Sub
The code in Listing 18.12 displays detailed properties for each column in the Authors
table at the data source. Note that the .Value and the .OriginalValue properties
have been left out of this example. You also can access these properties in your
programs. Also, the value of the .Type property maps to a set of predefined Visual
Basic constants. Table 18.8 lists those values.
Table 18.8. Various type values of the rdoColumns.Type property.
Visual Basic Constant | Integer Value | Description |
rdTypeCHAR | 1 | Fixed-length character string. Length set by Size property. |
rdTypeNUMERIC | 2 | Signed, exact numeric value with precision p and scale s (1 p 15; 0 s p). |
rdTypeDECIMAL | 3 | Signed, exact numeric value with precision p and scale s (1 p 15; 0 s p). |
rdTypeINTEGER | 4 | Signed, exact numeric value with precision 10, scale 0 (signed: -231 n 231-1; unsigned: 0 n 232-1). |
rdTypeSMALLINT | 5 | Signed, exact numeric value with precision 5, scale 0 (signed: -32,768 n 32,767; unsigned: 0 n 65,535). |
rdTypeFLOAT | 6 | Signed, approximate numeric value with mantissa precision 15 (zero or absolute value 10-308 to 10308). |
rdTypeREAL | 7 | Signed, approximate numeric value with mantissa precision 7 (zero or absolute value 10-38 to 1038). |
rdTypeDOUBLE | 8 | Signed, approximate numeric value with mantissa precision 15 (zero or absolute value 10-308 to 10308). |
rdTypeDATE | 9 | Date: Data-source dependent. |
rdTypeTIME | 10 | Time: Data-source dependent. |
rdTypeTIMESTAMP | 11 | TimeStamp: Data-source dependent. |
rdTypeVARCHAR | 12 | Variable-length character string. Maximum length: 255. |
rdTypeLONGVARCHAR | -1 | Variable-length character string. Maximum length determined by data source. |
rdTypeBINARY | -2 | Fixed-length binary data. Maximum length: 255. |
rdTypeVARBINARY | -3 | Variable-length binary data. Maximum length: 255. |
rdTypeLONGVARBINARY | -4 | Variable-length binary data. Maximum data-source dependent. |
rdTypeBIGINT | -5 | Signed, exact numeric value with precision 19 (signed) or 20 (unsigned); scale 0 (signed: -263 n 263-1; unsigned: 0 n 264-1). |
rdTypeTINYINT | -6 | Signed, exact numeric value with precision 3, scale 0; (signed: -128 n 127; unsigned: 0 n 255). |
rdTypeBIT | -7 | Single binary digit. |
Save and run the project. After you click the RDO Columns button, you'll see a
series of dialog boxes that show the details of each column in the Authors table.
Figure 18.12 shows one of those dialog boxes.
Figure
18.12. Viewing the rdoColumn properties.
The rdoQuery object provides a method for creating and executing defined queries or views on the remote data source. The rdoQuery object is the RDO version of the Microsoft Jet QueryDef object. You can create more than one rdoQuery object on each rdoConnection object. All rdoQuery objects are accessed through the rdoQueries collection object.
Add a new button to the form. Set its Name to cmdRDOQueries and its Caption to RDO Queries. Add the code in Listing 18.13 to the cmdRDOQueries_Click event.
Private Sub cmdRDOQueries_Click() ` ` example rdo query ` Dim rdoEnv As rdoEnvironment Dim rdoCon As rdoConnection Dim rdoQry As rdoQuery Dim rdoNewQry As rdoQuery Dim rdoRS As rdoResultset Dim strMsg As String Dim strSQL As String Dim aryLockType As Variant Dim aryType As Variant Dim aryCursorDriver As Variant ` aryCursorDriver = Array("rdUseIfNeeded", "rdUseOdbc", "rdUseServer", Â"rdUseClientBatch", "rduseNone") aryLockType = Array("rdConcurReadOnly", "rdConcurLock", "rdConcurRowVer", Â"rdConcurValues", "rdConCurBatchEdit") aryType = Array("rdOpenForwardOnly", "rdOpenKeyset", "rdOpenDynamic", Â"rdOpenStatic") ` strSQL = "SELECT * FROM Publishers,Titles WHERE ÂPublishers.PubID=Titles.PubId" ` ` set env/con Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") Set rdoCon = rdoEnv.OpenConnection("RDC Books5") ` ` build a new query & collect data set Set rdoNewQry = rdoCon.CreateQuery("rdoQryTest", strSQL) Set rdoRS = rdoNewQry.OpenResultset() ` ` show details For Each rdoQry In rdoCon.rdoQueries strMsg = strMsg & "BindThreshold=" & CStr(rdoQry.BindThreshold) & vbCrLf strMsg = strMsg & "CursorType=" & aryCursorDriver(rdoQry.CursorType) & ÂvbCrLf strMsg = strMsg & "hStmt=" & CStr(rdoQry.hStmt) & vbCrLf strMsg = strMsg & "KeysetSize=" & CStr(rdoQry.KeysetSize) & vbCrLf strMsg = strMsg & "LockType=" & aryLockType(rdoQry.LockType) & vbCrLf strMsg = strMsg & "MaxRows=" & CStr(rdoQry.MaxRows) & vbCrLf strMsg = strMsg & "Name=" & rdoQry.Name & vbCrLf strMsg = strMsg & "Prepared=" & rdoQry.Prepared & vbCrLf strMsg = strMsg & "QueryTimeOut=" & CStr(rdoQry.QueryTimeout) & vbCrLf strMsg = strMsg & "RowsAffeced=" & CStr(rdoQry.RowsAffected) & vbCrLf strMsg = strMsg & "RowsetSize=" & CStr(rdoQry.RowsetSize) & vbCrLf strMsg = strMsg & "SQL=" & rdoQry.SQL & vbCrLf strMsg = strMsg & "StillExecuting=" & CStr(rdoQry.StillExecuting) & ÂvbCrLf strMsg = strMsg & "Type=" & aryType(rdoQry.Type) & vbCrLf ` MsgBox strMsg, vbInformation, "rdoQuery" strMsg = "" ` Next ` rdoNewQry.Close rdoCon.Close rdoEnv.Close Set rdoQry = Nothing Set rdoNewQry = Nothing Set rdoCon = Nothing Set rdoEnv = Nothing ` End Sub
Notice the use of the OpenResultset method on the rdoQuery object. This is the way
to fetch rows from the data source using the rdoQuery object as the base. Save and
run the project. After you click the RDO Queries button, you'll see a detailed listing
of the properties of the query, as shown in Figure 18.13.
Figure
18.13. Displaying the rdoQuery properties.
The rdoParameter object enables you to populate the various predefined runtime parameters of a sorted query so that you can create flexible queries that can be adjusted at runtime by programming code or user input. You can define more than one rdoParameter object for each rdoQuery object. All the parameter objects are accessed via the rdoParameters collection.
Add one last button to the project. Set its Name to cmdRDOParameter and its Caption to Parameters. Then add the code in Listing 18.14 to the cmdRDOParameter_Click event.
Private Sub cmdRDOParameters_Click() ` ` example of rdo parameters ` Dim rdoEnv As rdoEnvironment Dim rdoCon As rdoConnection Dim rdoQry As rdoQuery Dim rdoRS As rdoResultset Dim rdoPrm As rdoParameter Dim strMsg As String Dim strSQL As String ` strSQL = "SELECT * FROM Authors WHERE Name Like ?" ` ` open env/con Set rdoEnv = rdoEngine.rdoCreateEnvironment("rdoTEMP", "admin", "") Set rdoCon = rdoEnv.OpenConnection("RDC Books5") ` ` create a parameter query Set rdoQry = rdoCon.CreateQuery("rdoQryPrm", strSQL) ` ` load parameter rdoQry.rdoParameters(0).Value = "%s%" rdoQry.rdoParameters(0).Type = rdTypeCHAR ` ` get result from parameterized query Set rdoRS = rdoQry.OpenResultset(rdOpenKeyset) rdoRS.MoveLast ` ` show some details strMsg = strMsg & "Name=" & rdoRS.Name & vbCrLf strMsg = strMsg & "Parameter=" & rdoQry.rdoParameters(0) & vbCrLf strMsg = strMsg & "RowCount=" & CStr(rdoRS.RowCount) & vbCrLf ` MsgBox strMsg, vbInformation, "rdoParameters" ` rdoQry.Close rdoCon.Close rdoEnv.Close Set rdoPrm = Nothing Set rdoQry = Nothing Set rdoRS = Nothing Set rdoCon = Nothing Set rdoEnv = Nothing ` End Sub
The code in Listing 18.14 first opens the data connection and then creates a parameterized
query (note the ? that represents the parameter portion of the statement). Then the
rdoParameter object is populated by using the Value and Type properties. Notice that
it is not necessary to surround string parameters in single or double quotation marks.
This is handled by the remote data source. Then the OpenResultset method is used
to populate the dataset, and the MoveLast method is used to force the cursor to traverse
the entire record collection. This ensures an accurate value for the Rowcount property
of the rdoResultset. Finally, the results appear in a dialog box, as shown in Figure
18.14.
Figure
18.14. Viewing the rdoParameters properties.
TIP: The code in Listing 18.14 uses the Type property of the rdoParameter object. This is not a required property when creating rdoParameter objects, but it is highly recommended. If no Type property is set, the remote data source makes a guess at the data type of the parameter. This can lead to unexpected errors. If you have a CHAR column in your table that contains a shoe size (8.5, 9, and so on), for example, and use this column in a parameterized query, it is possible that passing a value of 8 will be misinterpreted by the RDBMS as an integer or long value instead of a CHAR value.
That completes your tour of the RDO programming object collection.
In today's lesson, you learned about two alternative methods for accessing remote data. You learned that you can use the RDC to create simple data-entry forms with data-bound controls. You also learned to use the RDOs to create Visual Basic 5 programs that can access data from a remote RDBMS.
Along with the details of the RDC and the RDOs, you also learned some of the basics of remote data access in general:
You also learned the details of the following Microsoft RDOs:
You have been asked to build a quick utility that scans any RDBMS database and provides a list of all the tables and views in that database. This will be used to catalog old RDBMS databases and assist in maintenance chores.
Create a simple data entry form that allows users to select any available ODBC data source and then view all tables and views in the data source in a list box. Hint: Use the rdo.connection method with an empty DSN string to get the ODBC dialog to appear.
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |