RDS 2.5 -

How to use RDS

to get and maintain data

on a Remote Server

Carl D. Prothman

Microsoft’s Visual Basic MVP

Certified Computer Professional (CCP)

Able Consulting, Inc.

http://www.able-consulting.com

Overview

This article will provide you with an in-depth look at Microsoft’s Remote Data Services (RDS) 2.5.

We'll focus specifically on how to use the RDS Data Control's new URL property to call an ASP web page that gets and maintains data on a remote database server.

We'll also cover how to use the RDS Data Space to call middle-tier VB Custom Business Objects hosted by COM+ running on Windows 2000 Server.

You'll learn what are their strengths and weaknesses, which will help you decide which approach to use in your applications.

Technical level:

Intermediate to Advanced

Prerequisites:

HTML, ASP, VB, ADO, SQL

Table Of Contents

Introduction. 4

What Is RDS?. 4

Where do I get RDS?. 5

How do I install RDS?. 6

Key Components of RDS. 7

Client-Side RDS Objects. 7

RDS DataControl 7

Creating An Instance. 7

Getting Data. 8

ASYNC –vs– SYNC Mode. 9

Checking For Errors. 10

Data Binding. 11

Viewing Records. 11

Sorting Records. 12

Filtering Records. 12

Saving Changes. 13

RDS DataSpace. 15

Creating An Instance. 15

Using CreateObject 16

Server-Side RDS Objects. 17

RDS DataFactory. 17

Creating an Instance. 17

Getting Data. 17

Custom Business Object 18

ASP Web Page. 19

RDS Security. 22

Summary. 23

Introduction

What Is RDS?

Remote Data Services (RDS) is one of Microsoft’s remote data access technologies. It allows end-users to bring one or more disconnected ActiveX Data Object (ADO) recordsets from a remote server to a client computer using the HTTP, HTTPS, or DCOM protocols.

Once the disconnected ADO recordset is cached on the client computer, the end-user can insert, update, or delete records in the local recordset. Since the recordset is disconnected, only the local data is affected and no changes are made to the remote database.

When the end-user is ready, the local changes are then sent back to the remote server for storage in the remote database. If any update conflicts occur, the end-user is notified. The end-user can then resolve the conflicts.

Figure 1 – Overview of ADO –vs- RDS

Where do I get RDS?

RDS is part of Microsoft Data Access Components (MDAC). MDAC contains ADO, RDS, ODBC, and OLE DB core components, plus the ODBC Drivers and OLE DB Providers.

MDAC can be obtained by installing one of Microsoft applications that installs MDAC (e.g. Internet Explorer), or by installing Microsoft’s Windows 2000 operating system, or by running the MDAC redistribution file (MDAC_TYP.EXE).

You can get the latest version of the MDAC redistribution from Microsoft’s data web site: http://www.microsoft.com/data

How do I install RDS?

In order for RDS to work correctly, MDAC must be installed on both the client and the server machines.

On the client machine, you can install Microsoft’s Internet Explorer 5.01, which installs MDAC 2.1 (core components only and no ODBC drivers or OLE DB providers). Since RDS uses the ODBC driver (or OLE DB provider) on the remote server, RDS will still work correctly. If you need the ODBC driver (or OLE DB provider) on the client, then just run the MDAC redistribution file (MDAC_TYP.EXE). Or just install Windows 2000 Professional, which installs MDAC 2.5 for you.

On the server machine, install MDAC_TYP.EXE on NT 4.0 machines, or just install Windows 2000 Server, which installs MDAC 2.5 for you. By default, a new install of Windows 2000 has RDS disabled. To enable RDS, you must remove the IP restriction on the MSADC virtual directory via the IIS MMC (Q250536).

If installing MDAC via the MDAC redistribution file (MDAC_TYP.EXE), keep in mind the following:

1) On NT machines, you must be logged on with Admin rights (Q177514). After the reboot, you must log in again with Admin rights for the install to work correctly.

2) On Windows 95/98, you must install DCOM 95/98 first (Q191094). DCOM 95/98 will require a reboot before running MDAC_TYP.EXE.

3) MDAC_TYP.EXE can be put into a CAB file using VB's Package and Deployment Wizard (Q217754).

For more information how to setup MDAC, see Q232060

Key Components of RDS

Client-Side RDS Objects

The key RDS components on the client-side are the RDS DataControl and the RDS DataSpace.

RDS DataControl

The RDS Data Control is a non-visual, data-binding control. It can be used to get and maintain data on a remote server by one of the following techniques:

1) URL property - By setting the URL property, the RDS DataControl will call the corresponding ASP Web page, which will get and maintain data on the remote database server.

2) Server/Connect/SQL properties – By setting the Server, Connect, SQL properties (and the optional Handler property), the RDS DataControl will use the RDS DataSpace on the client to call the RDS DataFactory on the remote server to get and maintain data on the remote database server.

3) SourceRecordset property – By setting the SourceRecordset property, the RDS DataControl can display the data in a client-side ADO recordset, which was obtained by some other means (e.g. using the RDS DataSpace to RDS DataFactory, or RDS DataSpace to Custom Business Object). In order to save any changes, you must use one of the techniques listed above, or pass the disconnected ADO recordset back to a Custom Business Object, which can then save the changes. Note starting with MDAC 2.x, the ADOR type library exists only to maintain backward compatibility with MDAC 1.5 clients (Q216389). Hence if you have MDAC 2.x installed on the client, then just use the ADODB type library.

Creating An Instance

To create a new instance of the RDS DataControl in ASP, include the following HTML OBJECT tag.

</OBJECT>

To create a new instance of the RDS DataControl in Visual Basic, first make a reference to the “Microsoft Remote Data Service 2.5 Library”. Then create the RDS DataControl as follows:

Dim oRdc As RDS.DataControl

Set oRdc = New RDS.DataControl

Getting Data

With the newly created instance of the RDS DataControl, you can get data from a remote server by setting RDS DataControl’s URL property (or Server, Connect, SQL properties) and invoking it’s Refresh method.

If using the URL property

With oRdc

.URL = "http://carl2/GetAuthorByState.asp?state=CA"

.Refresh

End With

If using the Server/Connect/SQL properties

With oRdc

.Server = "http://carl2"

.Sql = "Select * From Authors Where State = 'CA'"

.Connect = "Provider=sqloledb;" & _

"Data Source=(local);" & _

"Initial Catalog=pubs;" & _

"User Id=sa;" & _

"Password=;"

.Refresh

End With

If using the Server/Connect/SQL properties with the RDS Default Handler

With oRdc

.Server = "http://carl2"

.Handler = "MSDFMAP.Handler"

.Connect = "Data Source=MyConnectTag;"

.Sql = "MySQLTag"

.Refresh

End With

The corresponding CONNECT and SQL sections in the \WINNT\MSDFMAP.INI file would be:

[connect MyConnectTag]

Access = ReadWrite

Connect = "Provider=sqloledb;Data Source=(local);Initial Catalog=pubs;User Id=sa;Password=;"

[sql MySQLTag]

Sql = "Select * From Authors Where State = 'CA'"

For more information about the RDS Default Handler, see:
Q243245, Q230680, and RDS Customization Handler articles

ASYNC –vs– SYNC Mode

By default, the RDS DataControl runs in ASYNC mode. This means that the code will not wait on the Refresh line, but will continue on while the data is fetched in the background. Therefore you must check the RDS DataControl’s ReadyState property in the OnReadyStateChange event before trying to use the RDS DataControl’s recordset. If you try to access Recordset property while in the adcReadyStateLoaded state, you will get an error.

Sub oRdc_onReadyStateChange()

Select Case oRdc.ReadyState

Case adcReadyStateLoaded
' The current query is still executing

' and no rows have been fetched.

Case adcReadyStateInteractive
' Fetching records in background

Case adcReadyStateComplete
' All records fetched

' Do something with the recordset

End Select
End Sub

To implement the event handler in VB, use the WithEvents key word. The WithEvents key word will allow you to implement all of the public events associated with the RDS DataControl.

Dim WithEvents oRdc As RDS.DataControl

To run the RDS DataControl in SYNC mode (which means that the code will wait on the Refresh line until the data is fetched), then you must set the following two properties before running the Refresh method.

oRdc.ExecuteOptions = adcExecSync

oRdc.FetchOptions = adcFetchUpFront

Checking For Errors

The onError event is called whenever an error occurs during an operation.

Private Sub oRDC_onError(ByVal SCode As Integer, _

ByVal Description As String, _

ByVal Source As String, _

CancelDisplay As Boolean)

MsgBox "Source = " & SCode & vbCrLf & _

"Description = " & Description & vbCrLf & _

"Source = " & Source

CancelDisplay = False ' This cancels the RDS error dialog

End Sub

Once the ReadyState property equals adcReadyStateComplete (in ASYNC mode), if the recordset property is equal to Nothing, then the command has “timed out” or an error occurred.

If oRdc.Recordset Is Nothing Then

MsgBox "Command timed out"

End If

If the recordset’s EOF property is True, then no records were found.

If oRdc.Recordset.EOF Then

Msgbox "No records were found"

End If

Data Binding

You can use the RDS DataControl to bind the results of a query to one or more visual controls (e.g., text box, combo box, grid, etc.).

In HTML, you must bind the control using the Datasrc tag

<OBJECT ID="GRID" WIDTH=600 HEIGHT=200 Datasrc="#oRdc"

In VB, you just set the control’s DataSource property

Set txtCompanyName.DataSource = oRdc

Viewing Records

To see how many records were found, use the RDS DataControl Recordset’s RecordCount property:

MsgBox oRdc.Recordset.RecordCount

To get a value of a column from the RDS DataControl’s recordset, use the following syntax:

MsgBox oRdc.Recordset.Fields("MyColumnName").Value

' or

MsgBox oRdc.Recordset("MyColumnName").Value

' or

MsgBox oRdc.Recordset(0).Value

To view each record in the RDS DataControl’s recordset, use the MoveNext method. The bound controls will automatically show the next record in the recordset

If Not oRDC.Recordset.EOF Then

oRdc.Recordset.MoveNext

End If

Since a Static cursor is always used for the RDS DataControl’s disconnected ADO Recordset, you can move to the beginning of the recordset by invoking the MoveFirst method without having to rerun the query.

oRdc.Recordset.MoveFirst

Sorting Records

To sort the client-side recordset, you can use either the RDS DataControl’s Sort property, or you can use the RDS DataControl Recordset’s Sort property (since all of the recordset properties and methods are available to you once the recordset has been brought over to the client).

The RDS DataControl’s Sort property is limited to one sort column at a time. Whereas the RDS DataControl Recordset’s Sort property can sort one or more columns at a time.

To use the RDS DataControl’s Sort properties:

With oRdc

.SortColumn = "au_lname"

.SortDirection = True ' Ascending

.Reset

End With

To use the RDS DataControl Recordset’s Sort property:

With oRdc.Recordset

.Fields("au_lname").Properties("Optimize") = True

.Fields("au_fname").Properties("Optimize") = True

.Sort = "au_lname ASC, au_fname ASC"

End With

Filtering Records

To filter the client-side recordset, you can use the RDS DataControl’s Filter properties. Or you can use the RDS DataControl Recordset’s Filter property.

The RDS DataControl’s Filter properties are limited in the type of filters you can perform. Whereas the RDS DataControl Recordset’s Filter property allows you to use the “LIKE” filter.

To use the RDS DataControl’s Filter properties:

With oRdc

.FilterColumn = "au_lname"

.FilterCriterion = "<"

.FilterValue = "'C'"

.Reset True ' Filter on the original rowset

MsgBox .Recordset.RecordCount

End With

To use the RDS DataControl Recordset’s Filter property:

With oRdc.Recordset

.Fields("au_lname").Properties("Optimize") = True

.Filter = "au_lname Like 'B%'"

MsgBox .RecordCount

End With

Saving Changes

To submit changes back to the remote database, you can use either the RDS DataControl’s SubmitChanges method, or you can use the RDS DataControl Recordset’s UpdateBatch method.

The RDS DataControl’s SubmitChanges method is an “all or nothing” type of submit. Either all of the records are committed to the database, or none of the records are committed to the database. If any conflicts occur, an error is raised.

Whereas the RDS DataControl Recordset’s UpdateBatch method is a “check each record status” type of submit. If one record has an update conflict, then other records can still be committed (assuming they do not have any update conflicts).

In order for the RDS DataControl to save any changes, you must set the URL property (or the Connect, Server, and SQL properties). Otherwise nothing will happen when the SubmitChanges method is called.

To use the RDS DataControl’s SubmitChanges method:

oRdc.SubmitChanges

To use the RDS DataControl Recordset’s UpdateBatch method:

With oRDC.Recordset

' Reduce the Recordset to only pending changes

.Filter = adFilterPendingRecords

' If there are any changes to be made

If .RecordCount Then

' Perform in-line error handling. Note an error is raised

' if there are conflicts when UpdateBatch is run

On Error Resume Next

' Only send changes to the server

.MarshalOptions = adMarshalModifiedOnly

' Update the remote database

.UpdateBatch adAffectGroup

' If there was a conflict in performing the UpdateBatch

If Err.Number Then

' Filter for conflicts

.Filter = adFilterConflictingRecords

' Let the user know how many conflicts occurred

MsgBox CStr(.RecordCount) & " conflict(s) occurred during save."

' Get the latest values (underlying) from the remote database

.Resync adAffectAllChapters, adResyncUnderlyingValues

' Move to the first record in the local recordset

.MoveFirst

' For each conflict found

Do While Not .EOF

' Show conflicts to user

MsgBox "The following record: " & vbCrLf & _

"'" & .Fields(0).OriginalValue & "', " & _

"'" & .Fields(1).OriginalValue & "', " & _

"'" & .Fields(2).OriginalValue & "'" & _

vbCrLf & vbCrLf & _

"has already been changed to:" & vbCrLf & _

"'" & .Fields(0).UnderlyingValue & "', " & _

"'" & .Fields(1).UnderlyingValue & "', " & _

"'" & .Fields(2).UnderlyingValue & "'"

' Handle conflicts – based on business rules

' Ask user what to do

' - Overwrite the underlying values (keep current values)

' - Keep the underlying values (discard current values)

' – Revert back to original value (discard current and

' underlying values)

.MoveNext

Loop

End If

oRDC.Refresh ' Get a new snap shot of the remote data

End If ' End if changes

End With

RDS DataSpace

The RDS Data Space is a non-visual, client-side object. It allows you to create an instance of a business object that is located on a remote server using RDS over HTTP, HTTPS, or DCOM.

The RDS DataSpace creates a client-side proxy for the business object. Client-side proxies help with the packaging, transport (marshaling), and un-packaging of disconnected ADO recordsets across process or machine boundaries.

The RDS Data Space can be used to get and maintain data on a remote server by one of the following techniques:

1) RDS DataFactory – Using the RDS DataSpace’s CreateObject method, you can create an instance of the RDS DataFactory. The RDS DataFactory allows you to perform queries and return disconnected ADO recordsets. You can then set the RDS DataControl’s SourceRecordset to the returned recordset, or you can fill controls “by-hand” using the returned recordset.

2) Custom Business Object – Using the RDS DataSpace’s CreateObject method, you can create an instance of your own custom business object (e.g. VB ActiveX DLL that resides on the remote server). Depending on the public methods, you can perform queries and return disconnected ADO recordsets. You can also pass in a client side recordset and submit changes.

Creating An Instance

To create an instance of the RDS DataSpace in ASP, use the HTML OBJECT tag.

</OBJECT>

To create a new instance of the RDS DataSpace in Visual Basic, you will first need to make a reference to the “Microsoft Remote Data Service 2.5 Library”. Then you can create the RDS DataSpace as follows:

Dim oRds As RDS.DataSpace

Set oRds = New RDS.DataSpace

Using CreateObject

Once the RDS DataSpace object has been created, you can create an instance of a Business Object on the remote server using the RDS DataSpace’s CreateObject method. You must use late binding (As Object). For VB components, the PROGID = "ProjectName.ClassName".

To create an instance of a business object using HTTP

Dim oMyObj As Object

Set oMyObj = oRds.CreateObject("PROGID","http://carl2")

To create an instance of a business object using HTTPS

Dim oMyObj As Object

Set oMyObj = oRds.CreateObject("PROGID","https://carl2")

To create an instance of a custom business object using DCOM, leave out the "http://" in front of the server name.

Dim oMyObj As Object

Set oMyObj = oRds.CreateObject("PROGID","carl2")

To create an instance of a business object in process (which assumes that both the client and server on the same machine), leave the server name blank.

Dim oMyObj As Object

Set oMyObj = oRds.CreateObject("PROGID","")

Server-Side RDS Objects

The key RDS components on the server-side are the RDS DataFactory, Custom Business Objects, and ASP web pages (used with the RDS DataControl’s URL property).

RDS DataFactory

The RDS Data Factory is Microsoft’s default business object. The RDS Data Factory provides methods for sending SQL to a DBMS and passing back the results in the form of a disconnected ADO recordset. The RDS Data Factory doesn’t contain any validation or business rules logic. If you need this type of logic, then you will have to create your own custom business object.

Creating an Instance

To create a client-side instance of the server-side RDS DataFactory using the RDS DataSpace over HTTP:

Dim oRds As RDS.DataSpace

Dim oRdf As Object

Set oRds = New RDS.DataSpace

Set oRdf = oRds.CreateObject("RDSServer.DataFactory","http://carl2")

To create a server-side instance of the RDS DataFactory from server-side code, you will need to make a reference to the “Microsoft Remote Data Service 2.5 Server Library”. You can also use early binding (rather than late binding):

Dim oRdf As RDSServer.DataSpace

Set oRdf = New RDSServer.DataSpace

Getting Data

Once an instance of the RDS DataFactory is created, you can use its Query method to execute SQL statements

' Get all of the Author records

sConnString = "Provider=sqloledb;" & _

"Network Library=DBMSSOCN;" & _

"Data Source=(local);" & _

"Initial Catalog=pubs;" & _

"Trusted_Connection=Yes;"

sSQL = "Select * From authors"

Set oRS = oRDF.Query(sConnString, sSQL)

Custom Business Object

If you want your web-based database application to perform validation or business rule logic, then you will need to create a custom business object (and not use the RDS DataFactory object).

To create a client-side instance of a Custom Business Object using the RDS DataSpace over HTTP

Dim oRds As RDS.DataSpace

Dim oRdf As Object

Set oRds = New RDS.DataSpace

Set oRdf = oRds.CreateObject("PROGID","http://carl2")

Note: for VB components, PROGID = “ProjectName.ClassName”.

You can use Visual Basic 6.0 Enterprise edition to create customer business objects (ActiveX DLLs). After you’ve created an ActiveX DLL, it must be registered on the server. In addition, you must give the DLL launch rights on your server by adding the DLL’s PROGID to the following system registry:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC\Parameters\ADCLaunch]

If the business object is called from clients using RDS over HTTP, each call to the business object will create a completely new instance of the object. No information is retained from any prior calls to the object. Hence the business object must be designed as stateless (e.g. do not use properties, collections, or static variables).

To improve performance and scalability of the business objects, you can add your component to Microsoft COM+ on Windows 2000. COM+ provides component-based transaction processing, object instance management, and automatic thread (and process) management.

ASP Web Page

If you use the RDS DataControl’s URL property, then you will need to create “separate” ASP Web pages that are able to get and maintain data on a remote server.

To get and save changes (via the ASP Web pages) using the RDS DataControl’s URL property on the client:

Dim oRdc As RDS.DataControl

Set oRdc = New RDS.DataControl

With oRdc

' Set the URL to the GetAuthorByState web page

.URL = "http://carl2/GetAuthorByState.asp?state=CA"

' Set SYNC mode

.ExecuteOptions = adcExecSync

.FetchOptions = adcFetchUpFront

' Call the ASP page (get the data)

.Refresh

' View the Author’s last name

MsgBox .Recordset(1).Value

' Now make a change to local recordset

.Recordset(1).Value = .Recordset(1).Value & "1"

' Set the URL to the SaveAuthors web page

.URL = "http://carl2/SaveAuthors.asp"

' Submit the changes to the remote database

.SubmitChanges

' Get and view the updated value

.URL = "http://carl2/GetAuthorByState.asp?state=CA"

.Refresh

MsgBox .Recordset(1).Value

End With

The corresponding GetAuthorByState.asp ASP Web page on the server would look like:

<%@ Language=VBScript %>

<% Option Explicit %>

Dim oConn

Dim oRs

Dim sSQL

Dim sState

On Error Resume Next

Const adPersistXML = 1

Const adStateOpen = 1

Const adOpenStatic = 3

Const adLockBatchOptimistic = 4

Const adCmdText = 1

' Create and open a new Connection

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "provider=sqloledb;" & _

"data source=(local);" & _

"initial catalog=pubs;" & _

"user id=sa;" & _

"password=;"

If Err.Number <> 0 Then

Response.Status = "500 " & Err.Source & ": " & Err.Description

Response.End

End If

' Create and open an updatable recordset

Set oRs = Server.CreateObject("ADODB.Recordset")

sState = Request.QueryString("state") & ""

sSQL = "Select * From Authors Where State = '" & sState & "'"

oRs.Open sSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText

If Err.Number <> 0 Then

Response.Status = "500 " & Err.Source & ": " & Err.Description

Response.End

End If

' ' Save data into IIS 4.0’s response object

   ' Set stmData = Server.CreateObject("ADODB.Stream")
   ' oRs.Save stmData, adPersistXML
   ' Response.Write stmData.ReadText

' Save data into IIS 5.0’s response object

Response.ContentType = "text/xml"

oRs.Save Response, adPersistXML

If Err.Number <> 0 Then

Response.Status = "500 " & Err.Source & ": " & Err.Description

Response.End

End If

' Clean Up

Set oRs = Nothing

If oConn.State = adStateOpen Then

oConn.Close

End If

Set oConn = Nothing

The corresponding SaveAuthors.asp ASP Web page on the server would look like:

<%@ Language=VBScript %>

<% Option Explicit %>

Dim oConn

Dim oRs

On Error Resume Next

' Create and open a new Connection

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open "provider=sqloledb;" & _

"data source=(local);" & _

"initial catalog=pubs;" & _

"user id=sa;" & _

"password=;"

If Err.Number <> 0 Then

Response.Status = "500 " & Err.Source & ": " & Err.Description

Response.End

End If

' Create and open a new recordset

Set oRs = Server.CreateObject("ADODB.Recordset")

oRs.Open Request

If Err.Number <> 0 Then

Response.Status = "500 " & Err.Source & ": " & Err.Description

Response.End

End If

' Update the database

oRs.ActiveConnection = oConn

oRs.UpdateBatch

If Err.Number <> 0 Then

Response.Status = "500 " & Err.Source & ": " & Err.Description

Response.End

End If

' Clean Up

Set oRs = Nothing

If oConn.State = adStateOpen Then

oConn.Close

End If

Set oConn = Nothing

RDS Security

RDS can communicate with an IIS Web server running in any one of the three password authentication modes: Anonymous, Basic, or NT Challenge/Response. These settings define how a Web server controls access through it, such as requiring that a client computer have explicit access privileges on the NT Web server.

If the password authentication property for your IIS Web server is set to Windows NT Challenge/Response, then custom business objects are invoked under the client's security context. When working in this mode, the login to the Web server (IIS) is not anonymous but uses the user ID and password that the client computer is running under.

If the ODBC DSN (or OLE DB Provider) connection string is set up to use Trusted Connection, then access to databases, such as SQL Server, also happens under the client's security context. But this only works if the database is on the same computer as the IIS. The client credentials cannot be carried over to yet another computer using NT4. However, with Windows 2000, the credentials will be able to carry over to another computer using the Kerberos authentication protocol.

For more info, see the following Microsoft articles:
Security and Your Web Server
ADO and RDS Security Issues in Microsoft Internet Explorer
INFO: Working with RDS Handlers

Summary

RDS allows developers to create full-featured, data-centric, web-based database applications. It provides transparent client-side caching of result sets, thus avoiding expensive data re-fetching, which improves performance.

RDS handles all data similarly on the client side, regardless of its source, by using ODBC Data Source (or OLE DB Provider) on the remote server.

RDS provides secure systems by offering support for Secure Sockets Layer (SSL) over HTTPS.

Finally, RDS is flexible in that it allows you to use custom business objects or a default Data Factory object, which allows easy access to your remote data.

Further Reading