ODBC and Remote Data Objects (RDO)

ODBC and Remote Data Objects (RDO)

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.

Figure 18.1: RDO object model

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, please have a look at figure 18.2 . 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.