Another day, another database..
This column appeared in the November 1998 edition of
EXE, and covered the key points of the new ADO database technology.
Each new release of Visual Basic from Microsoft seems to bring
with it a new database technology. Now that developers have had time to get
used to either DAO or RDO, or both, Microsoft have introduced ADO which is
intended to replace both of them. Not that DAO and RDO have been ditched –
they’re still there for backward compatibility. Many developers will
prefer to stay with the programming interfaces that they are familiar with for
some time to come yet. However Microsoft has invested a lot of time and effort
into OLE DB and as a result you will find that the currents of the Microsoft
tide will sweep you along into this direction before too long. In this months
column I’m outlining the basic elements of both the technology and the
programming model in order to provide a jump-start. I intend to address some of
the more complex issues in future columns.
Universal Data Access
It is quite common for Visual Basic developers to specialise
in certain areas of technology; one such area of specialisation tends to be the
database access model. Depending upon the company technology infrastructure, a
developer will normally choose either a local/workgroup based database via DAO
or a client/server database via RDO. It’s not uncommon to meet somebody
who is a specialist with one model and yet is something of a novice with the
other because although the two models are similar, they are not identical. ADO
is designed to be familiar to users of both models.
As part of the ongoing Information At Your Fingertips
initiative launched a few years ago, the Universal Data Access (UDA) approach
concentrates on connecting with a source of data wherever it resides. At the
heart of this concept is OLE DB, a set of system-level COM interfaces that
provide a uniform method of retrieving data regardless of its format. This
technology is designed from the ground up to provide an efficient method of
access to both web-based and multiple-tier client/server data sources. For each
source of data there needs to be a corresponding OLE DB provider. Examples of
such data sources are SQL Server, Oracle, NT5 directory services, email
storage, and mainframes. Probably the most useful provider at the moment is the
OLE DB Provider for ODBC Drivers (the provider is coded as MSDASQL) which, as
the name implies, provides a bridge to existing ODBC data sources. This driver
ensures the maximum leverage of pre-existing data sources, as Microsoft might
say.
While it is OLE DB that provides a heavyweight object model to
access data of all varieties, it is ADO that provides a simplified
language-independent Automation model for consumers. Visual Basic 6 was the
first version of this language to introduce an out-of-the-box installation of
ADO, although the technology itself has been around for a little while now and
is currently running at version 2.0. Some of the improvements in this newer
version include added support for Events and persistence of data between
sessions, the second of which I’ll cover a little later in this article.
Microsoft are clearly treating this whole new technology as a key component in
their current (intended) evolution to a serious player in the high-performance
operating system arena. For example the forthcoming, and eagerly awaited, SQL
Server 7 makes heavy use internally of OLE DB to the extent that its algorithms
are tuned specifically for it.
ADO Object Model
ADO 2.0 is comprised of seven primary objects, which are:
Connection: Typically used by Command and
Recordset objects to provide a session with a data source, although it is
possible to directly pass a command to the database via the Execute method.
Command: The definition of a query or statement
that is to be given to a data source. This can be an instruction to execute a
bulk operation on some data, retrieve a data extract into a Recordset object,
or to perform a management function of the database itself. A Command
definition can be either a SQL statement or an invocation of a stored procedure
or a view, if appropriate.
Error: Can be created if the OLE DB provider has
chosen to implement it, otherwise the Connection object will raise errors into
the normal Visual Basic Err object.
Field: Logically represents a column in a
Recordset object.
Parameter: implemented as a Collection, and used
to store parameters that will then be used by a Command object.
Property: A dynamic characteristic of an ADO
object that is defined by the provider. A property can either be built-in,
wherein it is actually part of the standard ADO definition, or it can be
dynamic, in which case it is specific to the providers’ implementation. An
example of a dynamic property could be whether a data source supports
transactions. Property collections exist for the Connection, Command,
Recordset, and Field objects.
Recordset: Logically contains a set of results
from a query made against a Connection object. This is the primary means of
manipulating data when using ADO.
The architectural design of ADO emphasises a degree of
independence for each ADO object from the others; a Command object, for
example, can be associated with a Connection object, executed to return a
Recordset object, connected to another Connection object, and executed again to
create another Recordset.
Cursors
The Recordset object supports four different cursor types.
I’m sure that many EXE readers will be familiar with cursors to some
degree or another but because they are part of the Microsoft Certified Solution
Developer syllabus I think it’s worth taking the time to review them
again.
Dynamic: The contents of the Resultset reflect
subsequent changes made by other users, whether they are record additions,
deletions, or modifications.
Keyset-driven: Similar to dynamic, but only
those records captured within the Recordset at the time of its creation are
visible. Therefore any changes to existing records will be seen, but records
that have been subsequently added will not be visible. Likewise, records that
have actually been deleted will also become unavailable.
Forward-only: Behaves similarly to a dynamic
cursor but will only support a forward direction through the Resultset. The
mechanism behind this approach is optimised for performance.
Static: The contents of the Resultset remain the
same regardless of what actually happens to the ‘real’ data in the
underlying table.
The type of cursor is set via the Recordset.CursorType
property; predefined constants for the above list are adOpenDynamic, adOpenKeyset,
adOpenForwardOnly, and adOpenStatic.
Listing 1 provides code fragments for the basic operation of
creating a Connection, Defining a Command, and obtaining a Recordset. This
isn’t the only way of populating a Recordset instance, for example a
Recordset can use the Open method to obtain a working set of data.
Listing 1: One way of getting data from ADO
Private Sub Command1_Click()
Dim sConnect As String
Dim adoConn As ADODB.Connection
Dim adoCommand As ADODB.Command
Dim adoRecordset As ADODB.Recordset
sConnect = "driver={SQL Server};"
_
& "server=homer;database=pubs;uid=sa;pwd="
Set adoConn = New ADODB.Connection
adoConn.ConnectionString = sConnect
adoConn.Open
Set adoCommand = New ADODB.Command
adoCommand.CommandText = "SELECT * FROM authors"
'
adoCommand.CommandType = adCmdText
adoCommand.ActiveConnection = adoConn
Set adoRecordset = adoCommand.Execute
Do While Not adoRecordset.EOF
Debug.Print adoRecordset!au_fname
adoRecordset.MoveNext
Loop
Set adoRecordset = Nothing
Set adoCommand = Nothing
Set adoConn = Nothing
End Sub
Lightweight Recordsets
Previously known as the Advanced Data Connector, a technology
that has been integrated into (and forms a subset of) ADO is the Remote Data
Service. This is used as a means of transportation for an ADO Recordsets to be
passed from a server to a client application but beware, using this technology
means that you need to clearly understand how to implement tiered – or
distributed – applications. Most of Microsoft’s documentation on the
subject inevitably submits examples that revolve around Internet-based
solutions which in itself is fine because RDS is designed to work over HTTP;
however it does also work over DCOM. The RDS technology exists in two parts: a
server component and a client component. Although these components are
available from a variety of sources (including
http://www.microsoft.com/data/ado) the server component is also installed with
Internet Information Server (IIS) 4.0, and the client components are installed
along with Internet Explorer 4.0. If you do happen to be distributing a web
based application for browsers other than IE4 then include the Msadc11.cab file
with your other files. For non-web applications that need to have the client
services installed a Microsoft Knowledgebase article (Q176874) called
Distributing RDS Client with Visual Basic Setup Wizard lists the necessary
steps to take.
To explain the mechanism with which RDS works we can consider
a standard three-tier application. When a call is made from the client tier to
the middle tier it is the job of the RDS client to marshal the request (over
HTTP, DCOM, or whatever) to the server-side RDS component. This, in turn,
passes the request to the data tier. When the server-side RDS component
receives the data that is asked for then it packs it into an ADO Recordset
object and passes it back to the client tier. The contents of the Recordset are
then cached on the client computer in order to keep the network traffic to a
minimum.
As I mentioned in the Visual Basic 6 review in the September
edition of EXE the ADO system is provided as both a full implementation (ADODB)
and a lightweight version (ADOR). The ADODB version should be installed in the
middle tier, while the lightweight version is the component that gets installed
along with the client application. This lightweight version is designed purely
for the implementation of a Recordset without the associated baggage of
Command, Connection, Error, and Parameter objects.
Error Handling
When writing an error handler for an ADO routine you need to
be aware that an error can reside in either the ADO Errors collection, or
within the Visual Basic Err object. The location depends upon how and where the
error occurred – if the problem is caused by an error with ADO itself then
the Visual Basic Err object will hold the error details. OLE DB provider
generated errors will be populated into the ADO Errors collection if the
provider has chosen to support this. Of note here is the fact that ADO stores
its errors in a Collection object, implying that there can be more than one
message. An error handler should be sure that it extracts each Error object by
using a For Each…Next construct. Only the ADO Connection object exposes
the Errors collection.
Data Persistence
The logical contents of a Recordset can be persistent between
sessions. Data is either stashed or retrieved via the Save and Open methods
respectively. The data is saved between sessions as binary ADTG (Advanced Data
TableGram) format. A simple example of this in action is shown below:
Dim myRecordSet As ADODB.Recordset
myRecordSet.Save "c:\data\mydata.adt",
adPersistADTG
myRecordSet.Close
myRecordSet.Open "c:\data\mydata.adt"
Progress
In summary, ADO is a thoughtfully designed progression from
the DAO and RDO object sets, while OLE DB is a powerful new technology that
forms an integral part of the forthcoming SQL Server 7 and will no doubt be
part of the base Windows NT specification before too long. In most cases I
would imagine that there would be no point in retro-fitting ADO into a software
development project that is already underway but it’s probably worth
making the move on the next project.
