Part one discusses the following implementations of ADO applications across Microsoft development languages and environments:
For each language or implementation discussion, a rudimentary ADO sample program demonstrates how to open and close a Recordset object.
Manipulating an Automation object takes two steps:
Each scenario discusses implementation-specific reasons why you might want to break out each step into a separate line of code or combine them into a single statement.
Requirements for the Samples
Most of the code examples and code listings require that you have the Microsoft Data Access 2.0 SDK installed on your computer (which installs ODBC 3.51, OLE DB 2.0, ADO 2.0, and data access components). One code example requires Microsoft SQL Server™ for invoking a stored procedure. Other applications may be required to support demonstration of various code listings (such as Visual C++).
To use the code examples, you must install the Microsoft Access Open Database Connectivity (ODBC) Driver and create a Data Source Name (DSN) called "AdoDemo" for the Access .mdb (AdoDemo.mdb) file provided with the examples. To create a DSN, use the ODBC Data Source Administrator in Control Panel.
The AdoDemo.mdb file contains a single table, Authors, which was extracted from the bibliography sample database that ships with Visual Basic. Most of the sample code in this article refers to AdoDemo.mdb, with a few exceptions: Some reference the Authors table in the PUBS database that ships with SQL Server.
For each of the samples, the following connection string opens AdoDemo.mdb, using the default userID and password for an Access database.
DSN=AdoDemo;uid=admin;pwd=;
Each sample's connection string uses the OLE DB Provider for ODBC to retrieve data from the .mdb file via the Microsoft Access ODBC Driver. You can specify a different OLE DB provider by adding "PROVIDER=…" to the connect string.
Microsoft Visual Basic for Applications
The easiest way to use ADO is within the context of a development environment that exposes Microsoft Visual Basic for Applications (VBA), such as Visual Basic or Access. Several features eliminate the complexities of ADO development and let you focus just on ADO:
See the sections, "Benefits of Reading the ADO Type Library" and "COM Data Types" in Part Two to learn techniques you can use to keep your COM skills sharp while still taking advantage of the strengths of VBA with COM.
The following ADO code snippet demonstrates how to open a Recordset object in VBA.
Listing 1: Opening an ADO Recordset in VBA
Dim Source As String
Dim Connect As String
Dim Rs1 As New ADODB.Recordset
Source = "SELECT * FROM Authors"
Connect = "DSN=AdoDemo;UID=admin;PWD=;"
Rs1.Open Source, Connect
Rs1.Close
Set Rs1 = Nothing
MsgBox "Success!"
In one line, the Dim statement defines a variable Rs1 as the Recordset object and ensures implicit instantiation of an instance of the Recordset COM object with the New attribute. Two strings are defined for both the query statement and the connection string. ADO requires Bstrs for these two arguments of Recordset.Open; however, VBA automatically converts its String type into the Bstr required by ADO. After the Recordset object is opened, it is closed and the Recordset variable is released, deallocating the actual ADO object that was created by the New clause.
Defining and Instantiating ADO Objects with Visual Basic for Applications
To create an Automation variable and instantiate an object for that variable, you can use two forms of syntax: the CreateObject method or the Dim statement. With the CreateObject method, each statement is a discrete action. However, you can both define a variable and assign it to an instantiated object in one step, as shown in Listing 2.
Listing 2: Instantiating an ADO Connection object within VBA
' Technique #1: Use CreateObject() to create Connection object.
' Declare variable.
Dim conn1
' Implicit instantiation.
Set conn1 = CreateObject("ADODB.Connection.2.0")
' Technique #2A: Use Dim ... as ... to create Connection object
' for a pre-defined variable.
' Declare variable.
Dim conn2 As ADODB.Connection
' Implicit instantiation.
Set conn2 = New ADODB.Connection
' Technique #2A: Use Dim ... as ... New to create Connection object.
' Declare variable & implicit instantiation.
Dim conn3 As new ADODB.Connection
CreateObject is slower than using the Dim statement, is not strongly typed, and is not supported by command-line completion. However, it does not require you to specifically reference ADO in your project, and it can instantiate a specific version of an ADO object (assuming that you have provided a valid PROGID that can be found in the Windows registry).
The Dim statement is only successful if you have already added the ADO type library to your project's references. You can use the Dim statement with Visual Basic because it has integrated COM support, including command-line completion. In VBScript, you can only use the Dim statement to declare objects of type Variant, but CreateObject is compatible with VBScript syntax.
Using the "ADODB.<object>" syntax is not strictly necessary with the Dim statement. It is a good idea, however, because using the "ADODB.<object>" prefix ensures that you won't have naming conflicts if you reference other type libraries whose objects might be the same as an object in the ADO type library.
ADO in VBA with Visual Basic 5.0
To create an ADO project in Visual Basic 5.0
ADO in VBA with Microsoft Access 97
You can manipulate ADO just as easily within Access 97 as in Visual Basic 5.0 because both use the same VBA engine. However, adding ADO to an Access application does not let you use the same features of Access with ADO as it exposes for Data Access Objects (DAO).
Note If you have code that uses SQL syntax to make changes in a datastore, and you migrate that code from DAO to ADO, you might have to change some of the syntax, particularly double quotation mark characters. For more information, see "Single vs. Double Quotation Marks in SQL Statements" in Part Three.
To create an ADO project in Microsoft Access
Microsoft Visual C++
There are three ways to manipulate ADO within Visual C++: #import, the ClassWizard in MFC OLE, and COM in the Windows API. Of the three, #import is the most powerful and allows you to generate code that is nearly identical to Visual Basic for Applications (VBA) in syntax.
Before you can invoke an ADO object, you must initialize OLE. Listing 3 shows a global declaration you can add to one of the C-language source files in your project to ensure that OLE is properly initialized and terminated.
Listing 3: Automatic initialization and termination of OLE
struct InitOle {
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;
ADO in Visual C++ with #import
When you provide #import with the path or name of a file containing a type library, it generates definitions for globally unique identifiers (GUIDs), class wrappers for each ADO object, and enumerated types based on what it finds in the type library. For any type library you reference, Visual C++ generates the following two files at compile time:
For example, if you generate #import on msado15.dll, Visual C++ creates msado15.tlh and msado15.tli.
The #import directive also makes use of a new class, _com_ptr_t, also known as a smart pointer. Smart pointers automatically perform the COM QueryInterface, AddRef, and Release functions. In this respect, using #import on a COM object model begins to generate code similar to that which you see in Microsoft Visual Basic for Applications (VBA). The _com_ptr_t class and the other helper classes available with #import are discussed in greater detail later in this article.
Failed HRESULT results returned by ADO cause #import to raise an exception. The _com_error class raised in the exception automates the task of querying the IErrorInfo interface to acquire details of the error. If you do not want exception handling in your code, you can override #import. For details, see the Knowledge Base article q175784 (INFO: Over-riding #import's Exception Raising Mechanism).
The #import directive generally does not support default values or optional arguments. This is actually a design limitation, because #import allows you to use default values if the argument is of type Variant. However, very few methods where default values would most likely be used in ADO have arguments of type Variant.
Early releases of the exception handling mechanism in #import have a bug wherein the mechanism does not correctly release the IErrorInfo interface when an error is raised through or by ADO. A three-line fix implemented in the ImportErrorEx() function within the Rosetta Stone code examples corrects this bug. For details, see the Knowledge Base article q73645 (BUG: Access Violation in Msdaer.dll with _com_error Exceptions).
The following code demonstrates how to use #import to add support for ADO in your application:
#import "c:\program files\common files\system\ado\msado15.dll" \
rename ( "EOF", "adoEOF" )
Renaming the end-of-file (EOF) is required, because in a typical Visual C++ application EOF has already been defined as a constant (-1) using #define. However, EOF is also defined within ADO as a property of type Variant_Bool. Without the rename attribute redefining the string EOF for what #import generates on ADO's type library, you would get a compiler error, because it would replace the name of the property EOF with –1. The compiler does not accept this as a valid variable name and returns compiler error messages as a result.
You could achieve the same results with this line of code:
#import <msado15.dll> rename ( "EOF", "adoEOF" )
This technique is the more modern approach and requires that you already added the path "c:\program files\common files\system\ado" to one of your PATH, INCLUDE, or LIB environment variables (assuming the operating system has been installed on the C: drive). Or you can add the path to the options in Visual C++, as demonstrated in the section below, "Creating an ADO Project with #import."
This alternate syntax eliminates the need for the rename attribute:
#undef EOF
#import <msado15.dll>
This assumes you will not actually need the EOF definition. You could alternately assign EOF to a temporary variable, undefine EOF, import ADO, redefine EOF using the temporary variable you saved, and then undefine that temporary variable.
Defining and Instantiating ADO Objects with #import
Manipulating an Automation object takes two steps:
With #import you can accomplish both steps in a single line of code, using the smart pointer's (_com_ptr_t) constructor to pass in a valid CLSID, or PROGID. You could also declare an instance of the object, and then use the _com_ptr_t::CreateInstance() method to instantiate the object. Both techniques are demonstrated in Listing 4.
Listing 4: Instantiating an ADO Connection object with #import
#import <msado15.dll> rename( "EOF", "adoEOF" )
...
struct InitOle {
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;
...
// Method #1: Declaring and instantiating a Connection object
_ConnectionPtr Conn1( __uuidof( Connection ) );
// Method #2: Declaring and instantiating a Connection object
_ConnectionPtr Conn1 = NULL;
HRESULT hr = S_OK;
hr = Conn1.CreateInstance( __uuidof( Connection ) );
The recommended technique is the second one because the constructor of _com_ptr_t does not return a failed HRESULT if something goes wrong. The first method is flawed because it cannot test if the creation of the ADO Connection object succeeded or failed.
In both cases, use the Visual C++ extension __uuidof( Connection), which in this case retrieves a GUID defined by #import in the .tlh file corresponding to the ADO Connection object. By passing it to CreateInstance, you create a valid ADO Connection object for the Connection smart pointer. There are other forms you could pass into either the constructor or CreateInstance to reference the ADO Connection object and accomplish the same result. For more information, see the Visual C++ documentation for topics about #import.
The only flaw with the code above is that it assumes you have imported only ADO. If you import multiple libraries and one or more of those libraries have an object with the same name, you must provide some differentiation between the two. For example, both ADO and DAO contain an object named Recordset.
Another attribute of #import, no_namespace, prevents the compiler from qualifying the classes in a namespace, which is to say the name of the library the type library defines. In the case of ADO, this is ADODB. Using no_namespace means you don't have to reference the namespace when initializing or defining variables whose types are defined by what #import generates. However, if you have many type libraries imported into your application, it is safer to omit the no_namespace attribute.
Listings 5 and 6 show the difference in your code with and without the no_namespace clause. While it may be more work to omit no_namespace, it does ensure your code will be more robust in case it uses other Automation servers whose objects might share the same name as an object found in ADO.
Listing 5: Using #import with the no_namespace attribute
#import <msado15.dll> no_namespace rename( "EOF", "adoEOF" )
void main()
{
HRESULT hr = S_OK;
_ConnectionPtr Conn1 = NULL;
hr = Conn1.CreateInstance( __uuidof( Connection ) );
Conn1 = NULL;
}
Listing 6: Using #import without the no_namespace attribute
#import <msado15.dll> rename( "EOF", "adoEOF" )
void main()
{
HRESULT hr = S_OK;
ADODB::_ConnectionPtr Conn1 = NULL;
hr = Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
Conn1 = NULL;
}
Creating an ADO Project with #import
The following ADO code snippet demonstrates how to get started with #import.
Listing 7: Opening an ADO Recordset in Visual C++ with #import
#include <windows.h>
#import <msado15.dll> rename("EOF", "adoEOF")
... Init Ole ...
void main()
{
HRESULT hr = S_OK;
ADODB::_RecordsetPtr Rs1 = NULL;
_bstr_t Connect( "DSN=AdoDemo;UID=admin;PWD=;" );
_bstr_t Source ( "SELECT * FROM Authors" );
hr = Rs1.CreateInstance( __uuidof( ADODB::Recordset ) );
Rs1->Open( Source, Connect,
ADODB::adOpenForwardOnly,
ADODB::adLockReadOnly, -1 );
Rs1->Close();
Rs1 = NULL;
::MessageBox( NULL, "Success!", "", MB_OK );
Disciplined COM developers may find the idea of setting a smart pointer to NULL to force an implicit release of the COM object somewhat disconcerting. Smart pointers make this unnecessary, but you may find it reassuring to explicitly release your objects rather than just setting them to NULL or letting them go out of scope.
The code declares an instance of the ADO Recordset smart pointer named Rs1, but doesn't instantiate the actual ADO Recordset object until later with the CreateInstance method. Note that #import generates the smart pointer definitions in the .tlh file with the _COM_SMARTPTR_TYPEDEF macro, as follows:
_COM_SMARTPTR_TYPEDEF(_Recordset, __uuidof(_Recordset));
To determine the name of any smart pointer that #import generates for use in your code, examine the list of _COM_SMARTPTR_TYPEDEF macros generated in the .tlh file and add a "Ptr" to have the name of the smart pointer class created by the macro. Thus, _Recordset in this definition equates to the smart pointer derived class _RecordsetPtr. The use of an underscore preceding an ADO object is not required; it depends on how the object was defined in the ADO type library.
The code defines two Bstrs for both the query statement and the connection string using the #import helper class, _bstr_t. #import also offers a helper class for managing Variants: _variant_t. The samples based on #import use _variant_t extensively; it will be discussed in depth later in the article. After the Recordset object is opened, it is closed and the Recordset variable is released, deallocating the actual ADO object that was created by the CreateObject method.
Finally, there is the fact that CreateInstance is the one time you could receive a failed HRESULT from #import. Anytime you call a method of an object wrapped by a smart pointer, it raises a Visual C++ exception with an instance of the _com_error class. The following macro combines the fact that robust code should already be checking for exceptions raised by #import and eliminates the need to specifically test the HRESULT:
#define CREATEINSTANCE(sp,riid) \
{ HRESULT _hr =sp.CreateInstance( __uuidof( riid ) ); \
if (FAILED(_hr)) _com_issue_error(_hr ); }
Usage for this macro is as follows:
CREATEINSTANCE( conn1, Connection )
To create a Visual C++ 5.0 application that can utilize ADO with the #import precompiler directive
C:\program files\common files\system\ado
This assumes you installed the Windows directory on your C: drive. This directory should contain the msado15.dll file, which contains the ADO type library. Click OK to close the Options dialog box after adding this path.
#import <msado15.dll> rename( "EOF", "adoEOF" )
ADO in Visual C++ with MFC OLE
MFC OLE, like #import, generates class wrappers for a type library. Unlike #import, MFC OLE does not generate enumerated types from the type library, but it does implement ADO cleanly. The CString and COleVariant MFC classes hide Bstrs and Variants. However, your application carries along all of MFC as overhead (the mfc42.dll), as opposed to the much leaner #import. Each class wrapper built by MFC OLE is derived from the ColeDispatchDriver class, and failed HRESULTS generated by ADO are wrapped inside the ColeDispatchException class.
Creating an ADO Project with MFC OLE
The following ADO code snippet demonstrates how to get started with MFC OLE. Listing 8 assumes you've already used the MFC ClassWizard to generate classes against ADO 2.0.
Listing 8: Opening an ADO Recordset in Visual C++ with #MFC OLE
AfxOleInit();
...
_Recordset Rs1;
COleException e;
COleVariant Connect( "DSN=AdoDemo;UID=admin;PWD=;" );
COleVariant Source ( "SELECT * FROM Authors" );
Rs1.CreateDispatch( "ADODB.Recordset.2.0", &e );
Rs1.Open( (VARIANT) Source, (VARIANT) Connect, 0, 1, -1 );
Rs1.Close();
Rs1.ReleaseDispatch();
AfxMessageBox("Success!");
The AfxOleInit() function is an MFC helper that initializes COM. The code declares an instance (Rs1) of the ADO Recordset class wrapper derived from COleDispatchDriver, but doesn't instantiate the actual ADO Recordset object until later with the CreateDispatch method. The name of a class wrapper generated by the ClassWizard is identical to the name of the object in the type library. Note the use of an underscore preceding an ADO object is not universally required; if the object is defined with an underscore in the ADO type library, the smart pointer class will require one.
The code defines two ColeVariants for the Connect and Source variables. ColeVariant has an operator that can cast to the Bstr type required by ADO for the Recordset.Open method. After the Recordset object is opened, it is closed and the Recordset variable is released, deallocating the actual ADO object that was created by the CreateDispatch method.
To create a Visual C++ 5.0 application that can utilize ADO with the MFC OLE ClassWizard
C:\program files\common files\system\ado
This assumes you installed the Windows directory on your C: drive. This directory should contain the msado15.dll file, which contains the ADO type library. Click OK to close the Options dialog box.
The ClassWizard generates two files, msado15.h and msado15.cpp, with wrapper classes derived from COleDispatchDriver for each ADO class you selected in the Confirm Classes dialog box.
ADO in Visual C++ with COM Functions
Both #import and MFC OLE provide wrapper classes around a given Automation object, which are derived from a root class, _com_ptr_t or COleDispatchDriver, respectively. You could, though, bypass these wrappers and directly manipulate the Windows API to utilize ADO. Using _com_ptr_t or ColeDispatchDriver is COM programming, but both classes encapsulate and hide much of the more common invocations of COM APIs, such as CoCreateInstance, or use QueryInterface, AddRef, and Release on a given COM interface. This section discusses what it would take to bypass these wrapper classes and use the Win32 API directly to manipulate a COM object.
To use ADO with COM directly, you need two header files (adoid.h and adoint.h) provided by the OLE DB 1.X SDK (for ADO 1.0 and 1.5) or the Microsoft Data Access SDK 2.0 (for ADO 2.0). These two files define the CLSIDs, interface definitions, and enumerated types that you need to manipulate the ADO type library. Though you do not link to a library, you must also use a #include statement on the <INITGUID.H> header.
Creating an ADO Project with COM API
The following ADO code snippet demonstrates how to get started with COM API. Listing 9 assumes you installed the OLE DB 1.X SDK or the Microsoft Data Access SDK on your computer.
Listing 9: Opening an ADO Recordset in Visual C++ with COM API
#include <windows.h>
#include <initguid.h> // Include only once in your application
#include "adoid.h" // ADO GUID's
#include "adoint.h" // ADO Classes, enums, etc.
... INIT OLE ...
void main()
{
HRESULT hr = S_OK;
ADORecordset* Rs1 = NULL;
VARIANT Source;
VARIANT Connect;
VariantInit( &Source );
VariantInit( &Connect );
Source.vt = VT_BSTR;
Source.bstrVal = ::SysAllocString( L"SELECT * FROM Authors");
Connect.vt = VT_BSTR;
Connect.bstrVal = ::SysAllocString( L"DSN=AdoDemo;UID=admin;PWD=;" );
hr = CoCreateInstance( CLSID_CADORecordset,
NULL,
CLSCTX_INPROC_SERVER,
IID_IADORecordset,
(LPVOID *) &Rs1 );
if( SUCCEEDED( hr ) ) hr = Rs1->Open( Source,
Connect,
adOpenForwardOnly,
adLockReadOnly,
-1 );
if( SUCCEEDED( hr ) ) hr = Rs1->Close();
if( SUCCEEDED( hr ) ) { Rs1->Release(); Rs1 = NULL; }
if( SUCCEEDED( hr ) ) ::MessageBox( NULL, "Success!", "", MB_OK );
Listing 9 demonstrates the inclusion of <INITGUID.H> in order for the application to run with msado15.dll. Prior to ADO 1.5, an .lib file was provided to ensure your application would use ADO; however, it is unnecessary when you use 1.5 and include <INITGUID.H>. You can only include <INITGUID.H> once, or you will get multiple LNK2005 errors when building your application.
The code declares a Recordset interface variable and sets it to NULL, instantiating an ADO object with the call to CoCreateInstance(). ADORecordset is a definition provided by adoint.h. The code defines two Variants and assigns to them Bstr values for the query and connection information. After opening the Recordset object, the code closes it, releases the ADO Recordset object, and sets the interface variable to NULL.
Testing for a failed HRESULT is a significant difference between this code and #import or MFC OLE, which relies upon an exception being raised. See the section "Error Handling with ADO" later in this article for more information.
To create a Visual C++ 5.0 application that can utilize ADO with the COM API
C:\msdasdk\include\ado
This assumes you installed the Microsoft Data Access SDK 2.0 on your C: drive.
Java
There is more than one way to implement ADO within a Java application using Microsoft Visual J++™. The first is to use the Java Type Library Wizard that comes with Visual J++ 1.x. The second is to use the Microsoft SDK for Java. Both mechanisms read the ADO type library and generate classes that you can instantiate within your application. Both mechanisms generate those classes in the same location: \<windows directory>\Java\trustlib\msado15. The third choice is to use the new Windows Foundation Classes (WFC) within Visual J++ version 6.0.
The Java Type Library Wizard is included with Visual J++ 1.x, integrated into the Tools menu of the integrated development environment (IDE), and is very easy to use. However, you cannot step into the class wrappers it generates. The summary.txt file, located in the directory where the classes were generated, shows the class definitions it generated.
The Microsoft SDK for Java is available for use with Visual J++ 1.x and can be downloaded from www.microsoft.com/Java. The JactiveX.exe utility generates classes from a type library but can only be invoked on the command line. This feature is not integrated with the Visual J++ IDE. Unlike the classes generated by the Java Type Library Wizard, you can step into the class wrappers created by the SDK. This is useful for debugging how your code uses the ADO wrapper classes.
For Visual J++ 6.0, shipping within Visual Studio 98, ADO has been extended to work with WFC in the following ways. First, a set of Java classes have been implemented that extend the ADO interfaces and create notifications interesting to the Java programmer; the Java classes also expose functions that return Java types to the user. To improve performance, the Java class directly accesses the native data types in the OLE DB Rowset object, and returns them to the Java programmer as Java types without first converting them to and from a Variant. ADO has also been extended to work with event notifications in the WFC framework.
ADO in Java with the Java Type Library Wizard
The Java Type Library Wizard converts enumerated types found in any given type library to type INT (integer). It also defines an interface that corresponds to each enumerated type in the type library. You can reference the values of an ADO enumerated type with the following syntax:
msado15.<Enum Name>.<constant Name>
An example of this is shown in the following code fragment for setting the CommandType property of a Command object:
Cmd1.putCommandType( msado15.CommandTypeEnum.adCmdStoredProc );
You could also inherit, in the class using the enumerated type, from the wrapper that the Java Type Library Wizard generated for that enumerated type without the msado15.<enum name> syntax. Your class would need to inherit from each Java object and enumerated type interface that it references to completely eliminate the need to reference msado15.* in front of your ADO objects and enumerated values.
The following ADO code demonstrates how to get started with the Java Type Library Wizard.
Listing 10: Opening an ADO Recordset in Java with the Java Type Library Wizard
public static void main( String args[])
{
msado15._Recordset Rs1 = new msado15.Recordset();
Variant Source = new Variant( "SELECT * FROM Authors" );
Variant Connect = new Variant( "DSN=AdoDemo;UID=admin;PWD=;" );
int LockType = msado15.CursorTypeEnum.adOpenForwardOnly;
int CursorType = msado15.LockTypeEnum.adLockReadOnly;
int Options = -1;
Rs1.Open( Source, Connect, LockType, CursorType, Options );
Rs1.Close();
Rs1 = null;
System.out.println( "Success!\n" );
}
The code declares an instance of the ADO Recordset class wrapper and initializes it all on the same line of code. Further, it declares variables for each of the arguments in the Recordset.Open() method, especially for LockType and CursorType (because Java doesn't support enumerated types). It opens and closes the Recordset object. Setting Rs1 to NULL merely schedules that variable to be released when Java performs its systematic and intermittent release of unused objects.
To create a Visual J++ application that can utilize ADO with the Java Type Library Wizard
ADO in Java with the Microsoft SDK for Java
Creating an ADO application in Java using the Microsoft SDK for Java is fundamentally identical, from the perspective of source code, to using the Java Type Library Wizard. The only real difference is in how you generate the wrapper classes in the first place, as demonstrated in the steps below.
To create an ADO project with the Microsoft SDK for Java
\<path to DevStudio>\<path to Java SDK>\bin\JactiveX.exe /Javatlb "C:\program files\common files\system\ado\msado15.dll"
jvc /g:t c:\<windows>\Java\trustlib\msado15\*.Java
Scripting
ADO and the Remote Data Service (RDS) were first released with Microsoft Internet Information Server (IIS) and serve as mechanisms for manipulating databases within the scripting environment for either client or server.
Within an intranet or Internet environment, ADO is an excellent mechanism for server-side scripting to expose data. In this scenario, ADO, the underlying OLE DB provider it utilizes, and any other components needed to reference a given datastore are installed on a server running IIS. Using Active Server Pages (ASP), ADO is a component referenced in a script that generates static HTML, which is passed over an intranet to a client Web browser. Through the use of scripting, the Web page can send actions back to the server-side script, allowing the user to update, traverse, or view specific data. With session or environment variables, you can maintain persistence of ADO objects, although this will incur some overhead.
RDS is the right solution for remoting a recordset onto the client in the scripting environment. RDS consists of two parts, client- and server-side components. On the server, RDS sits on top of ADO and allows you to generate a recordset and marshal it to the client. On the client side, RDS contains just enough dynamic-link libraries (DLLs) to allow you to traverse that recordset in a disconnected state. You can still scroll through the recordset and even post changes made to that data with minimal impact on the network and back-end server. You should use RDS to provide a recordset to the client if client-side scripting is absolutely necessary (although further discussion of RDS and client-side scripting is beyond the scope of this article).
While Remote Data Objects (RDO) and DAO can be used with server-side scripting, neither have ADO's native support (via RDS) for remoting a recordset across the Internet or an intranet. Note that for the following discussion of server-side scripting, you should use Internet Information Server version 3.0 or later.
ADO in VBScript (on the Server)
Some of the differences between Visual Basic and VBScript are the supported data types, the scope of data, and how constants are created and used.
To see a list of Visual Basic features that are not supported by VBScript, see the Microsoft Visual InterDev™ documentation, in particular the topic titled, "Visual Basic for Applications Features Not in VBScript."
One notable difference between scripting and nonscripting ADO code samples discussed so far is the ODBC data source. For nonscripting applications, you created a User DSN in the ODBC Data Source Administrator. For scripts that are running under an IIS, you must create a system DSN, or your scripts won't be able to recognize the data source you created. This is true of any application using ODBC via Microsoft Windows NT® service.
Creating an ADO Project with VBScript
The following ADO code demonstrates VBScript using server-side scripting.
Listing 11: Opening an ADO Recordset in VBScript
<% @LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!--#include File="adovbs.inc"-->
<HTML>
<BODY BGCOLOR="White" topmargin="10" leftmargin="10">
<!-- Your ASP Code goes here -->
<%
Dim Source
Dim Connect
Dim Rs1
Source = "SELECT * FROM Authors"
Connect = "DSN=AdoDemo;UID=admin;PWD=;"
Set Rs1 = Server.CreateObject( "ADODB.Recordset.2.0" )
Rs1.Open Source, Connect, adOpenForwardOnly
Rs1.Close
Set Rs1 = Nothing
Response.Write("Success!")
%>
</BODY>
</HTML>
The <% and %> tags ensure that you have a server-side script running in an Active Server Page under Internet Information Server 3.0 or later. The type of a given variable is not declared; all three are implicitly Variant. Use Server.CreateObject to create an ADO Recordset object (and for a specific version of ADO). Conversions between VBScript's Variant and the data types ADO expects (such as Bstr for Recordset.Open) are implicit. Note that you must include adovbs.inc or constants such as adOpenForwardOnly will not be defined.
Note For the following demonstration, you will not actually create any kind of project.
To create a VBScript that can utilize ADO with server-side scripting
ADO in JScript (on the Server)
JScript is to Java what VBScript is to Visual Basic for Applications (VBA). It allows you to use Java syntax in a scripting environment. The following sample ADO code demonstrates JScript using server-side scripting.
Listing 12: Opening an ADO Recordset in JScript
<%
var Source;
var Connect;
var Rs1;
Source = "SELECT * FROM Authors";
Connect = "DSN=AdoDemoASP;UID=admin;PWD=;"
Rs1 = Server.CreateObject("ADODB.Recordset.2.0")
Rs1.Open( Source, Connect );
Rs1.Close();
Rs1 = null;
Response.Write("Success!")
%>
The <% and %> tags ensure that you have a server-side script running in an Active Server Page under Internet Information Server 3.0 or later.
Note For the following demonstration, you will not actually create any kind of project.
To create a JScript that can utilize ADO with server-side scripting
The Rosetta Stone Samples
The files accompanying this article includes implementation of an ADO sample that demonstrates the following:
Figure 1 shows the interface for the ADO Rosetta Stone samples ported to Visual Basic for Applications (VBA) within Visual Basic 5.0. While the exact appearance may vary from implementation to implementation, the functionality and rough appearance of the sample remains the same.
Figure 1. The ADO Rosetta Stone samples implemented with Visual Basic 5.0
Within the source code, the structure of the ADO Rosetta Stone samples is standardized so file names, methods, functions, procedures, and variables share the same names and, as much as possible, the same layout of comments and white space. Within most of the Rosetta Stone samples, you will find files with the following names:
The ADO Rosetta Stone samples have been ported to the following implementations:
For the ADOVJ sample, because there are no global variables and to ease readability, the empty Variant/Bstr and other variables were moved into the ADOCore class, which contained the event handlers. In addition, in order to support exception handling and graceful cleanup, the Connection and Recordset objects were explicitly created at the start of each method before entering the try/catch block.
Return a Recordset from a Parameterized Query
Return a Recordset from a Parameterized Query is implemented in a method named Access (or some mild derivation of such) for each of the Rosetta Stone samples.
Listing 13: Return a Recordset from a Parameterized Query
Set Cmd1 = New ADODB.Command
Set Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "SELECT * FROM Authors WHERE AU_ID < ?"
Set Param1 = Cmd1.CreateParameter(, adInteger, adParamInput, 10)
Param1.Value = 10
Cmd1.Parameters.Append Param1
Set Param1 = Nothing
Set Rs1 = Cmd1.Execute()
This VBA text takes advantage of optional arguments in the CreateParameter method, skipping the first argument altogether.
Dumping Built-In and Dynamic Properties
For each property you set on an ADO object, or each argument in a method, one or more dynamic properties in a properties collection will be initialized. ADO tests the underlying provider to ensure that the behavior you have requested, especially opening a Connection or Recordset object, is actually supported. Based on these tests, the corresponding dynamic or built-in property is initialized. Unlike other object models, ADO only initializes those properties it needs to test in order to complete a given action you specify in the source code. Therefore, the results you see in the properties collection of an object vary depending on how you have manipulated that object.
In addition, if ADO can't perform the action you requested (that is, one or more of the properties needed from the underlying data provider is not supported), it downgrades your request and attempts to complete the action. This happens most frequently when you try to open a cursor that the underlying provider doesn't support. A cursor is defined by the combination of three properties: CursorLocation, CursorType, and LockType. ADO may downgrade either CursorType or LockType if the requested cursor is not available. Usually ADO 1.5 updates the property that was changed internally to reflect what you actually received. Examining the properties collection can give clues as to why the cursor was not exactly what you expected.
Exposing dynamic and built-in properties for each properties collection is implemented in a method named ProviderProperties (or some mild derivation of such) for each of the Rosetta Stone samples.
Listing 14: Dumping built-in and dynamic properties
Public Sub Props()
Dim Prop As ADODB.Property
Dim Conn1 As New ADODB.Connection
Conn1.ConnectionString = "DSN=AdoDemo;UID=admin;PWD=;"
Conn1.Open
For Each Prop In Conn1.Properties
Debug.Print "Property " & Prop.Name
Debug.Print vbTab & " Type = " & GetType(Prop.Type)
Debug.Print vbTab & " Value = " & Prop.Attributes
Debug.Print vbTab & " Attributes = " & _
GetPropertyAttributes(Prop.Attributes)
Next Prop
End Sub
Sample Code Template Error Handling
Robust error handling is implemented in a method named CodeTemplate (or some mild derivation of such) for each of the Rosetta Stone samples. This method provides minimal error handling and demonstrates it works by attempting to open a connection without providing any connection information. Each of the Rosetta Stone samples uses the same error handling in each of the ADO routines. CodeTemplate merely provides an abbreviated version.
Because error handling itself varies so dramatically from language to language, consult the discussion in part two, "Error Handling with ADO," for more information.
Invoking a Stored Procedure Output and Return Parameters
This routine is written for the SQL Server ODBC Driver shipping with ODBC version 3.0 and later. Prior to version 3.0, the SQL Server ODBC Driver let you view return and output parameters while the Recordset object was still open. With ODBC 3.0 (and later), the driver contained several bug fixes that changed the driver's behavior to require that the Recordset object returned by the stored procedure be closed prior to viewing the output or return parameters.
Opening a stored procedure that returns both a Recordset object and output and return parameters is implemented in a method named SQLServer (or some mild derivation of such) for each of the Rosetta Stone samples.
Listing 15: Retrieving output and return parameters
Set Cmd1 = New ADODB.Command
Set Cmd1.ActiveConnection = Conn1
Cmd1.CommandText = "sp_AdoTest"
Cmd1.CommandType = adCmdStoredProc
Cmd1.Parameters.Refresh
Cmd1.Parameters(1).Value = 10
Set Rs1 = Cmd1.Execute()
'… Process Rs1
Rs1.Close
Debug.print "RetVal Param = " & Cmd1.Parameters(0).Value
Debug.print "Input Param = " & Cmd1.Parameters(1).Value
Debug.print "Output Param = " & Cmd1.Parameters(2).Value
WinDiff and the Rosetta Stone Samples
Because all the Rosetta Stone samples are similar, you can use the WinDiff utility that ships with Visual Studio and the Platform SDK to compare source code to observe the syntactic and logical differences between the samples. WinDiff lets you compare two individual files or all the files contained in two directories.
Figure 2. Comparing ADO in Visual C++ via #import to ADO in Java via Java Type Library Wizard
In Figure 2, WinDiff compares the #import Visual C++ and Java Type Library Wizard versions of the same ADO code. Areas in yellow (Java) or red (#import) show the differences between the two files, and areas with a white background are identical between the two files.
To use WinDiff to compare different sections of the Rosetta Stone samples