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.

 

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.

 

Figure 18.2: Components related to ODBC.

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. See figure 18.3.

 

Figure 18.3: ODBC Data Source Administrator

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(See figure 18.4), select Microsoft ODBC for Oracle (or some other driver meant for Oracle) and click on Finish button

 

Figure  18.4: Microsoft ODBC for Oracle setup dialog

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 as shown in figure 18.4.

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 in figure 18.5.

 

Figure 18.5: 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.

 

Figure 18.6: 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”

 

Figure  18.7: 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 as shown in figure  18.8.

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.(as shown in figure 18.8)

 

Figure 18.8: Dialog displaying error message.

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 ( figure 18.9)

 

Figure  18.9:  Form of the application.

 

Figure  18.10: 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 as shown in figure 18.10.

4.      Now enter an update command into command textbox (figure 18.11).

Figure  18.11: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 (figure 18.11).

6.      Now enter an invalid command as shown in figure 18.12, and click on Execute button.  The result is shown in figure 18.12.

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.

Figure 18.12: Command executor displaying an error.

Exercises

1.      What are the options available for Locktype Property?

2.      What is DSN and what information does it contain?

3.      How do we execute an action query (Non SELECT) in RDOs?

4.      How do you find out whether the required ODBC is installed?

5.      Which property returns the no. of records deleted by Delete command?

6.      What is rdoEnviroment(0)?

7.      What is rdoQuery and what is its counterpart in DAO?

8.      What is the default ResultSet type?