ODBC and Remote Data Objects (RDO)

Remote Data Objects (RDOs) are used to access a remote database through ODBC. Accessing an ODBC data source using RDO is faster than accessing the same using DAO.

Remote Data Control is an ActiveX control that is used to access an ODBC data source using RDOs. This is same as a Data Control, except that the data control uses DAOs and Remote data control uses RDOs.

First let us understand RDO object model and key features of RDOs and ODBC. Then we will understand how to use Remote Data Control and RDOs to access Oracle through ODBC.

Remote Data Object (RDO) Object Model
RDO object model is very small compared with DAO object model. Here is the list of objects in RDO object model. And most of them have their counterparts in DAO.




Here is a brief description about each object in the object model.

RDO object Description
rdoEngine The base object. Created automatically when you first access RDO in your application.
rdoError Used to handle all ODBC errors and messages generated by RDO. Created automatically.
rdoEnvironment Defines a logical set of connections and transaction scope for a particular user name. Contains both open and allocated (but unopened) connections, provides mechanisms for simultaneous transactions, and provides a security context for data manipulation language (DML) operations on the database. rdoEnvironments(0) created automatically.
rdoConnection Represents an open connection to a remote data source and a specific database on that data source, or an allocated but as yet unconnected object, which can be used to subsequently establish a connection.
rdoTable Represents the stored definition of a base table or an SQL view.
rdoResultset Represents the rows that result from running a query.
rdoColumn Represents a column of data with a common data type and a common set of properties.
rdoQuery An SQL query definition that can include zero or more parameters.
rdoParameter Represents a parameter associated with an rdoQuery object. Query parameters can be input, output, or both.
Table 18.1: Objects in RDO object model.

Note: rdoPreparedStatement is obsolete. It is supported only for backward compatibility. Microsoft recommends you use rdoQuery instead.

Remote Data Objects (RDOs) vs. Data Access Objects (DAOs)
Fundamentally RDO is same as DAO and Remote data control is same as Data control. However, RDO was designed and implemented strictly for relational databases that are accessed using ODBC. RDO doesn’t have a query engine like DAO, instead it depends on the query processor of the database that it is accessing.

The following table compares RDO objects and options with DAO objects and options.

Remote Data Objects and their DAO/Jet Equivalents

RDO object
Equivalent DAO/Jet object
rdoEngine DBEngine
rdoError Error
rdoEnvironment Workspace
rdoConnection Database
rdoTable TableDef
Not Implemented Index
rdoResultset Recordset
Not implemented Table-type
Keyset-type Dynaset-type
Static-type (read/write) Snapshot-type (readonly)
Dynamic-type (none)
Forward-only – type Forward-only-type
(cursorless) (none)
RdoColumn Field
RdoQuery QueryDef
RdoParameter Parameter
Not Implemented Relation
Not Implemented Group
Not implemented User
Table 18.2 : Comparing RDO object with DAO object

Important concepts of RDO
There are three important concepts related to RDO. When you use RDO, you have to first understand these three concepts. These concepts are:

• Cursor Drive
• Dataset (ResultSet) Type
• Lock Type

The following section will discuss about each of these three concepts. Understanding them is important because even ADOs use the same concept. In fact, most of the knowledge you gain with RDOs is usable with ADOs.

Cursor Drives
Cursor is the area where records are stored in memory. The process of keeping track of location of data pointer in the dataset is called as cursor management. Which type of cursor you have to choose, it depends upon the functionality that you want. But remember that the choice you make regarding cursor will have impact on the performance.

The property used to indicate the type of cursor to be used is – CURSORDRIVER.

The options available are:

Value Driver Option Meaning
0 RdUseIfNeeded Specifies that the best cursor driver is to be used. This is the default. RDO tries to use server side cursor first. If it is not available then it uses client side cursor.
1 RdUseODBC Cursor is created on client by ODBC. Keysets of the records are stored in client’s RAM (extending to disk in case if it exceeds).
2 RdUseServer Uses cursor of the remote database to manage records.
3 RdUseClientBatch Primarily provided to support complex features, such as batch updates, multi-table updates, support for BLOB types etc.
Table 18.3: Available Cursor Driver options.

Basically you have to choose between keeping server on the client or keeping it on the server. If it is to be on the server, then database server should take care of it.

ResultSet Types
A Resultset is a collection of records. The following are various types of ResultSets. Remember, not all resultsets are supported by cursor drivers.

The property used to indicate the type of result set is - RESULTSETTYPE

Value ResultSet Type Description
0 RdOpenFowardOnly Creates a forward only dataset. All members of the dataset are copied to the client. This is the default.
1 RdOpenKeyset This is an updateable dataset. New records added to the resultset will appear as part of the set. Record keys are created to point to all the members of the set. Can contain columns from one or more tables.
2 RdOpenDynamic The data reflects the changes made by other users.
3 RdOpenStatic The dataset is updateable. But new records added, updated, and deleted by other users may NOT appear as part of the set. To update ResultSet with up-to-date data you must reopen the ResultSet.
Table 18.4: Available ResultSet types.


Lock Type
This specifies the type of lock to be obtained while making changes to the ResultSet. The following are the available options.

The property used to indicate locking type is – LOCKTYPE.

Value Lock Type Description
0 rdConcurReadOnly Cursor is readonly and no updates are allowed. This is the default option. Use this if you do not have to modify the dataset.
1 rdConcurLock Provides pessimistic locking. Each page touched by the current row in the resultset is locked. This is used only in special cases.
2 rdConcurRowver Provides optimistic locking based on internal row id. Page is locked only when Update method is being executed. Records are not locked even between Edit and Update.
3 rdconcurvalues Same as rdCoucurRowver, but it is based on the values of the columns and not the row id.
4 rdconcurBatch Optimistic lock used for batch updates.
Table 18.5: Available locking types

Note: If you want to modify data in a Resultset, change the default setting of LockType. Because by default LockType is set to readonly, you can not make any changes to ResultSet.

Note: Whether a single row is locked or an entire page is locked and whether lock will be escalated etc, are database dependent. For example, databases like Oracle can lock a single row, whereas Jet engine deals with page locking (a page is 2 KB).

Before we use RDOs to access an ODBC Data source, we have to understand ODBC. The following sections will discuss what is ODBC and how to access Oracle using ODBC.

What is ODBC?
ODBC (Open Database Connectivity) is an interface through which you can access data in heterogeneous environments. For example, using ODBC you can access data of DB2 running on Mainframe and you can access data of Oracle running on Sun Sparc and so on. ODBC is an interface that was promoted by Microsoft. First let us understand why we need ODBC and its importance in the industry.

Why do we need ODBC?
Assume you want to access data stored in Oracle from a program written in Visual Basic. For this, you use a set of functions supplied by Oracle called as APIs (also called as Oracle Call Interface (OCI)) to access Oracle database. In the same way each database vendor supplies a set of functions using which you can access the database. But the problem is, each set of functions is different from others. That means if you want to access Oracle using OCI, you have to learn how to use these functions. But tomorrow if you have to access Sybase, you have to use functions provided by Sybase to access its database. That not only makes the life of programmer tough (as he has to learn a new set of functions again), it also necessitates great amount of changes to your programs. In brief, each DBMS provides its own set of functions to access its database. So your program becomes database dependent. That means a change in the type of database (say from Sybase to Oracle) needs the program to be modified to a larger extent.

When things were getting more heterogeneous and database independent programs were much desired, Microsoft designed a new interface called Open Database Connectivity (ODBC). What is so great about ODBC. Well, in nutshell, it makes your program database independent. That means whether you access Oracle or Sybase or DB2, you write the program and in the same manner you can shift your database from one to another.

How does ODBC Function?
To understand how does ODBC function. Each database vendor provides a program called as ODBC driver, which takes standard ODBC calls and translates them into the language the database can understand. So the application uses ODBC calls (called as ODBC API) either directly or indirectly (for example RDOs calling required ODBC calls) to access the database. And these ODBC calls are translated by ODBC driver of the specific database to the required native language. As a result the program uses the same ODBC calls irrespective of the database it is accessing and the ODBC driver takes care of converting the standard calls to the native calls.

But how does system know which ODBC driver to use?. Where are these drivers? Where is the information regarding these drivers? Who supplies ODBC drivers? We have to answer to these questions now.

First of all, each database vendor, such as Oracle corporation, Microsoft , IBM and so on, provides ODBC driver for its database. Remember if you do not have ODBC driver you cannot access the database using ODBC interface. It should also be noted, that there are some third party ODBC drivers. There are companies that are specialized in creating ODBC drivers, for example Intersolv.
You have to load ODBC drivers for database that you need to access. For example, if you want to access Oracle, you need to load ODBC driver for Oracle. When you load ODBC driver, Windows OS stores the details of the driver in System Registry (a part of Windows where important information is stored).

ODBC driver manager which is a part of Windows OS loads the required ODBC driver and passes the calls to driver and takes results from driver and pass the result to Application.

ODBC driver manager comes to know about the driver to be used and the database to be accessed through using Data source name (DSN) used by the application program.

Data Source Name
An ODBC data source is accessed using DSN. Data source name is a name that identifies the following:

• The name of the database to be used
• The type of the database and the ODBC driver to be used to access the database

DSN is created using ODBC Data Source Administrator, which is a program supplied by Windows OS. It is available in Control panel. When an application intends to access ODBC data source, it will create ODBC data source name (DSN) and accesses database through DSN.

So let us summarize the entire process.

• Application sends ODBC calls to ODBC Driver Manager
• ODBC Driver Manger sends ODBC calls to the appropriate ODBC Driver
• ODBC Driver converts ODBC calls to the native calls and accesses the database.

Accessing Oracle Database using ODBC
We have just understood various pieces involved in accessing an ODBC data source. Now let us access Oracle database through ODBC. For this purpose we could use either DAOs or RDOs, but when it comes to accessing ODBC data source, RDO is the obvious choice.

Here are the steps in accessing Oracle:

• Makes sure Oracle is installed in your system and it is up and running. You can check that using SQL*Plus. If you are successfully connected to Oracle using SQL*Plus that means Oracle is accessible to you.
• Makes sure your system contains ODBC driver for Oracle. This can be done with ODBC Data Source Administrator.
• Create a DSN for Oracle database. Use ODBC Data Source Administrator.
• Use DSN in Visual Basic application to access Oracle database.
To create DSN to access Oracle:

1. Start ODBC data source administrator by running ODBC (32 Bit) program from Control Panel of Windows OS.
2. Select Driver tab and check whether you have ODBC driver for Oracle. It may have the name “Microsoft ODBC for Oracle”. If no ODBC driver for Oracle is existing, you have to load one before proceeding.
3. Select User DSN tab and click on Add button.
4. When Create New Data Source dialog is displayed, select Microsoft ODBC for Oracle (or some other driver meant for Oracle) and click on Finish button
5. In Microsoft ODBC for Oracle setup dialog enter Oracle as Data Source Name, Oracle 7.3 database as Description.
6. If you are using Personal Oracle, leave remaining blank. If you are using Oracle Server (Client/Server Oracle) then enter Oracle service name (the one that you enter as Host String in Sql*plus) as Server.
7. Click on Ok.
8. You should see a new entry in the list of User Data Sources.

That’s all you have to do to create a DSN to access oracle.

Using Remote Data Control to access Oracle
Let us first understand how to access Oracle using remote data control.

Follow the steps given below to use Remote data control to access EMP table of user Scott in Oracle database.

1. Create a new project of Standard Exe type
2. Select Project -> Components and select Microsoft RemoteData Control 6.0 to load it into project.
3. Place remote data control on the form.
4. Change the following properties of Remote data control

Property Value
DataSourceName Oracle (or whatever name you have given as DSN)
UserName Scott
Password Tiger
Prompt rdDriverNoPrompt
Caption EMP Table From Oracle
SQL Select Empno, Ename from Emp

SQL property of Remote data control identifies the SQL command that is to be executed.

5. Place two labels and two text boxes
6. Change the following properties of these controls.

Control Property Value
Label1 Caption Employee Number
Label2 Caption Employee Name
Text1 DataSource MSRDC1
DataField Empno
Text2 DataSource MSRDC1
DataField Ename
Form Caption Details of Employees

7. Run the project using F5.
8. The form at run time should match the form

First row of EMP table displaying through Remote Data Control.

Using Remote Data Objects to Access ODBC Data source
We have seen how to use Remote Data Control to access data in Oracle database through ODBC interface. Now let us access the same database using Remote Data Objects.

To access ODBC data source using RDOs, follow the steps given below:

• Establish a connection using Connection object
• Create a ResultSet using Connection object
• Manipulate the ResultSet created in step 2.
Now let us develop the sample application, which allows you to access any ODBC data source and access the data from any table based on the given condition. This is a quite general program that can be used with any ODBC data source.

Here is what the application is supposed to do.

• When you start the application it prompts the user to enter DSN, username and password.
• If a valid connection is established then it will display the main form from where user can enter any query and get the result displayed on the form.
• It is also possible to execute commands other than Select. In this case the status of the executed command will be displayed.

Various methods of accessing ODBC Data Source from Visual Basic.

Now, let us create the application.

1. Create a new project of Standard Exe type
2. Use Project->References to load Microsoft Remote Data objects
3. Use Project-> Add Form and select ODBC Log In as the type of the form to be added to the project.
4. Visual Basic adds a new form with the name frmODBCLogon. This form is used to take ODBC DSN, username and password. ODBC Logon form has a lot of code written by Visual Basic to get the list of Data source names etc. We will modify a part of the code later.
5. Change PasswordChar of txtPWD textbox to “*” so that * is displayed instead of the actual characters entered.
6. Change Caption of the form (frmODBCLogon) to “Command Executor – ODBC Logon”


ODBC Logon form.

1. Select Project-> Properties and select frmODBCLogon as the startup object
2. Change the name of Form1 to frmMain.
3. Declare a public object of type rdoConnection in General/declaration of frmMain as follows


Public con as rdoConnection

Remove the code written for cmdOK_Click event procedure of frmODBCLogon and write the following code.

Private Sub cmdOK_Click()
Dim res As Integer
Dim cs As String
On Error GoTo errlbl
' form connect string
If cboDSNList.ListIndex > 0 Then
cs = "DSN=" & cboDSNList.Text & ";"
cs = cs & "Uid=" & txtUID.Text & ";"
cs = cs & "pwd=" & txtPWD.Text
' connect to database
Set frmmain.con = rdoEngine.rdoEnvironments(0).OpenConnection("", rdDriverNoPrompt, True, cs)
frmmain.Show
End If
Unload Me
Exit Sub
errlbl:
res = MsgBox("Unable to connect. Do you want to try again", vbYesNo + vbQuestion, "Error")
If res = vbYes Then
Exit Sub
Else
Unload Me
End If
End Sub
Listing 18.1 : Code for OK button in ODBC Logon form.

Note: You many not need all the functionality of ODBC Logon form. But it doesn’t harm having it. So you need not modify either the code or control in ODBC Logon form. However, if you ever need to change it, you are free to do so. Because it is after all a form in which Visual Basic has placed required controls and required code.

As you see in cmdOk_Click event, when user clicks on OK button, we take the required information – DSN, username, and password - from controls and create connect string. Then we have used connect string to connect to the database. If connect fails we prompt user to specify whether he wants to try again.

If connection is successful then we display main form (frmMain) and unload ODBC logon form.

Working with the main Form
Now let us concentrate on the main form. Remember by the time you enter into main from, Con object of main form is having a valid connection to the required database.

Place controls on the form

Change the properties of the controls as follows.

Control Property Value
Label1 Caption Command
Text1 Name txtCommand
Text “”
Multiline True
Text2 Name txtresult
Text “”
Multiline True
Scrollbars 3-both
Command1 Name CmdExecute
Caption &Execute
Command2 Name CmdQuit
Caption &Quit

Write the following code for click event of cmdExecute.

Private Sub cmdExecute_Click()
Dim fword As String

' Take the first word and compare it with SELECT. If it is SELECT then execute select command and get
‘ the rows and display them txtResult text box

fword = Mid(txtcommand, 1, InStr(txtcommand, " ") - 1)
If UCase(Trim(fword)) = "SELECT" Then
' get selected rows and display them in textbox
ExecuteSelect
Else
‘ Execute the given command and display the number of rows effected by the command
ExecuteNonSelect
End If

End Sub
Lisgting 18.2: Code for click event of Execute command button.

UCase function is used to convert the word to upper case and Trim function is used to remove any leading spaces.

ExecuteSelect and ExecuteNonSelect are two userdefined procedures. Here is the code for both the procedures.

Public Sub ExecuteSelect()

Dim rs As rdoResultset
Dim col As rdoColumn
Dim result As String

On Error GoTo errlbl
' create a resultset
Set rs = con.OpenResultset(txtcommand.Text)

result = ""
Do Until rs.EOF
For Each col In rs.rdoColumns
If (IsNull(col.Value)) Then
result = result & " NULL "
Else
result = result & col.Value & " "
End If
Next
result = result & vbCr & vbLf
rs.MoveNext
Loop

txtresult.Text = result

rs.Close
Set rs = Nothing
Exit Sub

Errlbl:
‘ Invoke procedure to display error message
DisplayError

End Sub

Public Sub ExecuteNonSelect()
Dim status As String

On Error GoTo errlbl
' Execute nonselect command
con.Execute txtcommand.Text
' displays the status
status = "Rows Effected = " & con.RowsAffected & vbCr & vbLf
txtresult.Text = status
Exit Sub

errlbl:
‘ Display error messages
DisplayError

End Sub
Listing 18.3: User-defined procedures - ExecuteSelect and ExecuteNonSelect.

In both the procedures we have error handling, which invokes DisplayError procedure to display error messages.

DisplayError procedure uses rdoErrors collection to get the list of messages and displays the error messages in txtResult text box. Here is the code for DisplayError procedure.

Public Sub DisplayError()

Dim status As String
Dim err As rdoError
Dim nl as string

Nl = vbcr & vblf

status = "Error occurred:" & nl
status = status & "============================" & nl

For Each err In rdoEngine.rdoErrors
status = status & err.Description & nl
Next

txtresult.Text = status

End Sub
Listing 18.4: DisplayError user-defined procedure.
And the code for Quit button is as follows:

Private Sub cmdQuit_Click()
con.Close
Set con = Nothing
Unload Me
End Sub
Listing 18.5: Code for Quit button.

Test Run
We have completed the application. Let us run the application to test whether it does what we want.

1. Run the project using F5.
2. You get ODBC Logon form. Select Oracle73 from DSN combo box.(or whatever DSN is applicable in your machine)
3. Enter Scott as username and Tiger as password.
4. Click on Ok.
5. At this stage, the application is trying to connect to the given database with the given username and password. If it is not successful you get a message asking whether you want to retry.

1. If connection is successful, it invokes main form and closes ODBC Logon form. If you have given username and password correctly you should see main form

Command executor after a successful Select command.

2. Enter “SELECT * FROM EMP“ in Command textbox and click on Execute button.
3. The application should respond by displaying all the rows of EMP table
4. Now enter an update command into command textbox .

Command Executor after a successful nonselect command.

5. If the given command is successfully executed then you get the number of rows effected by the command .
6. Now enter an invalid command , and click on Execute button. .
7. As the name of the column (DEPT) is not valid, “Invalid column name” error is displayed.

We have seen how to use RDOs to access ODBC data source in the Command Executor application. This application should give you an idea regarding Remote data objects such as Connection, Resultset, Columns, and Errors. However, remember using RDO resembles using DAO. So if you are good in DAO, you could pick up RDOs quite fast. This is equally true of ADOs, which we will discuss in the next chapter.