Visual Basic Banner Exchange

Febuary 1998

Return to Article Index

Using Remote Data Objects to Access Stored Procedures in an ODBC Database
by Jeffrey Hasan (Client Server Specialists, Inc.)


Introduction

With the release of Visual Basic 5.0, accessing data in remote (ODBC) databases has never been easier. Remote Data Objects (RDOs) are programmable objects that are similar to the Microsoft Jet Data Access Objects (DAOs). Using DAOs you can control almost every aspect of database management, such as constructing queries, deleting records, adding tables and defining primary keys. However, a Jet database is based on a very different set of database concepts compared to a non-Jet database. This is why RDOs were developed.

If you are reading this article I am going to assume a few things: one, that you have already programmed using DAOs; two, that you understand the DAO hierarchy (very important!); and three, that you understand ODBC and how to define a connection. I was inspired to write this article after a recent project where I needed to access data from a remote Sybase database. Due to security concerns, the DBA would only provide me access to stored procedures, not access to the database itself. Stored procedures must be provided with input parameters from the user in order to extract records. My challenge then became how to program a remote, parameter-based query: RDOs were the answer.

Remote Data Access

Although the principles of remote data access are beyond the scope of this article, I need to briefly bring up two concepts: cursor drivers and dataset types. Cursor drivers are used to define the way in which you move within a remote dataset. You must define a cursor driver, otherewise, the server will only send you a single record at a time. We’ll be using the "rdUseOdbc" setting, which instructs the RDOs to use the client-side ODBC cursor driver to keep track of the data pointer in the dataset.

The dataset type option specifies the type of dataset that is returned by the remote data source. (Analogous dataset types in DAO include dynasets and snapshots). For my project I used the "rdOpenStatic" option, which means that the dataset membership is kept static, and there is no constant refresh from the remote data source. This type of connection was appropriate because I was accessing stored procedures.

There are 2 ways to access an ODBC database: using a "DSN connection", or using a so-called "DSN-less" connection. ODBC connections must be written to the Registry to be valid, and Windows 95 provides a "32bit ODBC Manager" in Control Panel to do this for you. I would recommend this method of pre-defining your ODBC DSN because it will speed up your application. If you must use a DSN-less connection, define it using the "rdoRegisterDataSource" method in Visual Basic.

Establishing the remote connection

Start a new Visual Basic project and place a single command button on the form. Name the button "ODBC_Connect". Place the following code inside its Click event:
Private Sub Command1_Click()

Dim cn As New rdoConnection
Dim qd As rdoQuery
Dim rs As rdoResultset
Dim cl As rdoColumn

cn.Connect = "uid=jeffhasan; pwd=mypass; DSN=MyDSN;"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt

End Sub
The code assumes that I have already defined a DSN called "MyDSN", that my logon ID is "jeffhasan", and that my password is "mypass". The DSN connection is defined using the "Connect" method, and the connection is physically made using the "EstablishConnection" method. That’s all there is to it, you’re connected!

Accessing the stored procedure

I am going to assume that you have access to a stored procedure (a remote query) which I’ll call "mysales". This hypothetical procedure accesses a Sales & Marketing database that lists salespeople, and the total value of their sales for a given time period. Stored procedures are inflexible, since they are written to extract specific information. You just need to provide it with the necessary query parameters. The parameter options for the "mysales" stored procedure are:
Parameter 1:  Salesperson’s last name, e.g., Smith, Brown, Lewis,...
Parameter 2  Time period, e.g., QTD, YTD, MTD, ...
This stored procedure returns the dollar sales amount for that salesperson for the time period you requested. For example, the QTD sales amount of Mr. Lewis, which we’ll say is $100,000.

Add the following code to your project:

Private Sub Command1_Click()

Dim cn As New rdoConnection
Dim qd As rdoQuery
Dim rs As rdoResultset
Dim cl As rdoColumn

cn.Connect = "uid=jeffhasan; pwd=mypass; DSN=MyDSN;"
cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt

sSQL = "{ call mysales (?,?) }"  ‘SQL parameter syntax

set qd = cn.CreateQuery("", sSQL)

‘Define parameter vbType and Value
qd.rdoParameters(0).Type = rdTypeVARCHAR
qd.rdoParameters(1).Type = rdTypeVARCHAR

qd.rdoParameters(0).Value = "Lewis"  ‘Salesperson
qd.rdoParameters(1).Value = "QTD"  ‘Time Period

‘Open the resultset
Set rs = qd.OpenResultset(rdOpenStatic)

‘Enumerate the resultset
For Each cl In rs.rdoColumns
    
        If IsNull(cl.Value) Then
            Debug.Print  cl.Name; "0.00" 'Error trap for null fields
        Else
Debug.Print  cl.Name; cl.Value
        End If	
Next

cl.Close
rs.Close
qd.Close
cn.Close

Set cn = Nothing
Set qd = Nothing
Set rs = Nothing
Set cl = Nothing

End Sub

The stored procedure is called using the CreateQuery method.
The syntax: "{ call mysales (?,?) }" tells the rdoQuery object to expect that 2 parameters will be defined for the "mysales" stored procedure. The rdoParameters method is used to define two properties of each parameter: the type and value properties. It is very important to define the parameter type, because a stored procedure that expects a string may crash if you send it an integer. For example, a procedure may require a single digit number parameter to be passed as "09" (a string) as opposed to just 9 (an integer).

Parameter definitions use zero-based syntax, so the first parameter, "Lewis", is indexed as qd.rdoParameters(0), and the second parameter, "QTD" ", is indexed as qd.rdoParameters(1). "Lewis" and "QTD" are the value properties of their parameters, while rdTypeVARCHAR is the type property of each parameter.

Just as you define an "OpenRecordset" method in DAO, you use the equivalent "OpenResultset" method in RDO. Whereas a DAO query returns a Field, an RDO Query returns an "rdoColumn", the collection of which is called "rdoColumns". Since the "mysales" stored procedure returns just a single value, there is really only a single rdoColumn, however, in the code I have chosen to enumerate the resultset by scanning through the rdoColumns collection. This is the more flexible approach to take, since you may not know how much data will be returned to you.

The rdoColumn object has 2 properties: name and value. The value of the object is $100,000 in the case of "mysales", and the name of the object is the field it occupies in the database. Let’s say it’s "Sales_Total_QTD" for argument’s sake. The contents of your Debug window would now show:


Sales_Total_QTD  100000

Finally, in the interest of good memory management, you should clear your variables by setting them to "Nothing". Remember, the only limit on the size of these RDO variables is your imagination (within reason!), and you could drag your application speed way down if an unused RDO variable is taking up unneeded space.

Conclusions

Remote Data Objects provide a powerful means for interacting with remote ODBC database, and the simple project outlined in this article barely scratches the surface of these objects. For further information on programming using RDOs, I recommend consulting Visual Basic Books Online. I also recommend 2 articles in the Visual Basic Programmer’s Journal: "Data Access Made Easy" (November 1997) and "Migrate to RDO for Faster Data Access" (January 1998).

For a thorough overview of database programming I highly recommend "Database Programming with Visual Basic 5" by Sams Publishing (1997). It provides clear explanations of complex database concepts. Remember, don’t rush into using data access objects until you properly understand the concepts behind them!


Copyright 1995-1998 VB Online. All rights reserved.