ADO, a data-access technology from Microsoft, offers a less strict data object model than its ancestors, DAO and RDO--which gives the programmer a great deal more flexibility and reduces development time. David Sussman gives a tour of the various objects and what to do with them. (From ADO Programmer's Reference by David Sussman, published by Apress, ISBN 1590593421).
Like all other data-access technologies from Microsoft, ADO has a distinct object model that defines the objects and interfaces, and how the objects relate to each other. If you’ve done any database programming before, you are probably familiar with the general layout of these objects, because they lean heavily on the lessons learned from DAO and RDO. ADO has a good object model, but it is not as strict as its ancestors, so it gives the programmer a great deal more flexibility, thereby reducing development time. For example, in ADO you can create a recordset of data by using just a single line of code.
This has been achieved by flattening the object model. It’s still shown in the documentation as a hierarchy (with a single object at the top), but some of the lower objects can exist in your code in their own right, without the need to create higher-level objects explicitly. This means that a programmer can use the object most suitable for a particular task without having to create many other objects that aren’t really required in the program. If ADO requires these objects, it creates them and uses them behind the scenes—and you need never know that they are there.
The ADO Object Model
Versions prior to 2.5 have three main objects: the Connection, the Command, and the Recordset. No hierarchy exists among the three main objects, and you can create them independently of each other. Versions 2.5 and higher make two new objects available: the Record and the Stream. This diagram shows the relationships among these objects in the ADO model:
The objects shown as shaded are collections. Each collection can comprise zero or more instances of its associated object, as shown in the following table:
Collection
Associated Object
Errors
Error
Parameters
Parameter
Fields
Field
Properties
Property
Note that although the main five objects all can exist independently in our code, the collections must be derived from their parent objects. So (for example) a Recordset contains a Fields collection, which in turn contains zero-or-more Field objects.
Enumerating the objects in a collection is the same for all collections; you just have to use a loop variable of the appropriate type. For example, in Visual Basic the following construct will step through each and every object in the Errors collection:
Dim objErr As ADODB.Error For Each objErr In objConn.Errors Debug.Print objErr.Description Next
The same method can be used in VBScript, although you don’t give the object variable a specific type, because VBScript variables are Variants. For example, in ASP you could use:
Dim objFld For Each objFld In objRec.Fields Response.Write objFld.Name & "<BR>" Next
In JScript you could use the Enumerator object. For example, in ASP you could use:
The Connection is actually the hub of ADO, because it provides the methods that allow us to connect to a data store. Note that when you create a Recordset or Command object, it is not necessary to create a connection first, because a Connection object is automatically created for you behind the scenes. What this means is that you can actually create a recordset or run a command with just a single line of code. For example:
This creates a recordset on the authors table from the pubs database, based on the ODBC Data Source Name (DSN) pubs. Although this example is using a Recordset object, it actually creates a connection underneath. In fact, you can create a separate and explicit Connection object from the one automatically created by ADO :
Set conPubs = objRec.ActiveConnection
This sort of mix-and-match approach to connection creation frees the programmer from a rigid structure and allows you to write the code in a way that is naturally easier.
Although this looks simple, you create a new connection every time you use a connection string to establish a connection to a data store (subject to connection pooling—see Chapter 3). If you will be running several commands or creating several recordsets, I recommend that you create a Connection object directly:
conPubs.Open "DSN=pubs; UID=sa; PWD="
or:
conPubs.Open "Provider=SQLOLEDB; Data Source=Piglet; " & _ "Initial Catalog=pubs; User Id=sa; Password="
You can then use this Connection throughout your code without worrying about the number of connections you create. Not only is this more efficient, but it’s faster too; once a connection to a data store is established, it can be reused.
One area where you shouldn’t try to use a single connection is in the Session or Application scope in ASP pages. Creating an ADO object and storing it in a Session or Application variable has serious performance penalties if you want your ASP pages to scale. You can, however, store the connection string in Session or Application scope.
Once you have decided upon the provider, the properties of the Connection object allow you to see what kinds of facilities are available from the data provider. For example, picking the SQL Server provider allows you to see some of the properties for that provider, even before the connection is opened:
conPubs.Provider = "SQLOLEDB" For Each objProp in conPubs.Properties Print objProp.Name Next
Because ADO is designed to run against different data stores, it’s often worth checking that a particular function is supported before you call it. You do this by examining the Properties collection of the Connection object. This is particularly useful when using ADO as part of a development or query tool that gives users the ability to connect to different data stores. You could also, for example, use some of the properties to define a schema if manually creating XML from the data.
The Connection object isn’t just about connecting to, and holding information about, a data store. You can actually run commands and create recordsets with a Connection object too. For example:
This would run a SQL UPDATE query. Alternatively, to run a command and return a read-only recordset:
Set rsAuthors = _ objConn.Execute ("SELECT * FROM authors", , adCmdText)
So, not only can you use a Recordset object to connect to a data store, but you can also use a Connection object to create recordsets. It’s all part of the “code as you want” strategy.
An ADO operation may generate more than one error, and these are stored in the Connection object’s Errors collection, which is discussed in more detail in Chapter 8. Although you might think this breaks the flexibility of the object model, it gives a central place to find error information. Because the Recordset and Command objects have a connection underneath, it is extremely simple to access this error information without creating a Connection object.
The Command Object
The Command object is designed to run SQL statements (assuming the provider supports the Command object), especially those that require parameters. This is an important point, because the use of stored queries and procedures is a great way to improve speed and segment your application, and it fits naturally with Windows DNA.
Like the Connection object, the Command object can be used to run action queries that don’t return a recordset, and for creating recordsets. Unlike the Connection, however, you’ll need a couple more lines of code, because you cannot specify the command text in the same line as the line that specifies the command type:
The greatest difference in command execution comes when using the Parameters collection, because this allows you to use stored procedures, queries, and template and parameterized queries saved in the data store. This removes the SQL code from your program, making it faster and easier to maintain. Using stored procedures, you can have SQL statements in a compiled form that perform a set of actions using the values you supply as parameters.
With a Command—if the data provider supports this facility—you can also save the command details for execution at a later date. This is known as a prepared statement, and it allows the precompiled command to be run many times within the same connection. Once you’ve finished with your connection, the saved commands are deleted automatically. You can also use a Command object more than once, and on different connections if you require (assuming that the command can be run on another connection). Just change the ActiveConnection property of the command to point to the new connection, then run the command again. If you wish to reuse the Command object, it’s wise to close any active recordsets that were created as a result of the command’s execution. This ensures that memory is deallocated correctly.
Another feature allows you to turn the command into a method of the Connection object, allowing you to build up a Connection object that contains many new methods. This is quite useful in client/server scenarios, because you could attach different commands to the connection depending upon user privilege. This is explained in more detail in Chapter 4.
Unlike the Connection and Recordset objects, which must be supported by every OLE DB provider, a provider’s decision to support the Command object is entirely optional. Consequently, not all OLE DB Providers support the Command object.
The Recordset Object
The Recordset object is probably the most frequently used object in ADO; consequently, it has more properties and methods than other objects. Like the Command object, a Recordset can exist on its own or be attached to a Connection:
This opens a recordset using a new connection. To use an existing connection, simply substitute the connection string for the existing Connection object:
This is the preferred method if you are creating several recordsets, because it means that the connection to the data store doesn’t have to be established each time you create a recordset.
Another important point about the Recordset object is that it’s the only way to specify the recordset’s cursor type and lock type. If you use the Execute method of the Connection or Command object, it gives you the default cursor type, a read-only, forward-only cursor (often referred to as a firehose cursor, because the data is just “squirted” to the recordset). If you need to use a Command and require a different cursor type, then you can specify the Command object, instead of the table name or query text, as the source of the Recordset.
You use recordsets to examine and manipulate data from the data store. The Recordset object gives you facilities to move about through the records, find records, sort records in a particular order, and update records. You can perform updating in two modes: directly (changes are sent back to the data store as they are made) or in batches (changes are saved locally and then sent back to the data store all at once).
In client/server applications, you can also pass recordsets between the business-logic tier and the user-interface tier, where they can be manipulated locally. This saves database resources, which can be critical on large-scale applications, and it minimizes network traffic. These recordsets are called disconnected recordsets.
The Record Object
The Record object was introduced in version 2.5, and is used in conjunction with a new type of OLE DB Provider, called Document Source Providers. Document Source Providers are designed to enable access to data stores of semistructured data, such as file or mail systems. It was necessary to create a new object because of the way data is stored. In relational data stores, such as SQL Server or Access, we work using set-based data, where every row is in a rowset and has the same structure as the other rows. Semistructured data, on the other hand, doesn’t follow this pattern, because each row might have a different structure. If you think about the structure of a Web site, then you’ll start to understand how this works. A Web site generally consists of directories and files. If you look at the top-level directory for a site, you might see something like this:
File Name
Type
default.htm
File
Images
Directory
menu.xml
File
All three items here have some common properties, such as a name. However, they also have different properties; the directory doesn’t have a size, but must indicate that it contains other files. These different properties are why we need a different object to cope with this sort of data. When dealing with semistructured data, the Record object maps onto individual files, and the Recordset object is mapped onto a collection of files.
To access a directory through a Record object, use the following syntax:
Dim recRoot As New ADODB.Record recRoot.Open "", "URL=http://localhost" recRoot.Close
Specifying URL= tells ADO to use the Internet Publishing Provider.
The Record object not only allows access to files, but also gives management over them too. The CopyRecord, MoveRecord, and DeleteRecord methods give the ability to control files remotely.
The Stream Object
The Stream object is a wrapper around a memory manager (in fact it’s based around the COM IStream interface) and gives control over the contents of the memory used to store an object. This might sound rather complex, but it has wide-ranging uses and is simple to use.
You can use a Stream in several ways. One way is in conjunction with a Document Source Provider (such as the OLE DB Provider for Internet Publishing) to provide access to file contents on a Web server. For example:
Dim stmFile As New ADODB.Stream Dim sContents As String stmFile.Open "URL=http://localhost/postinfo.html" stmFile.Charset = "ascii" sContents = stmFile.ReadText stmFile.Close
This simply opens a file and reads the contents into a string. You also get the ability to write data out to the stream, where it is saved automatically to the file. This gives the ability to perform editing on remote files.
Stream objects have other roles. They can be used when saving and opening recordsets, and in conjunction with the Microsoft XML Parser.
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
A Field object contains details about a single field (or column) in a recordset. Such information includes the data type of the field, its width, and so on.
The Fields collection contains a reference to an ADO Field object for each field in a recordset, and there are two ways in which you can use this. The first is with existing recordsets, where you can examine each field, see its type, check its name, and so on. This is quite useful when you are unsure what exactly the recordset contains, and it can be useful in scripting when showing tables dynamically. Using the Fields collection allows you to find the name of each field. For example, in ASP script you could do this:
Response.Write "<TABLE>" For Each objFld In objRec.Fields Response.Write "<TR><TD>" & objFld.Name & "</TD></TR>" Next Response.Write "</TABLE>"
This creates an HTML table showing which fields are in the recordset objRec. In fact, using a similar method, you could easily create a simple script routine that accepts any recordset and builds an HTML table from it. You could use the Fields collection to supply the table header, showing the column names, and the recordset to fill in table details.
Another use of the Fields collection is for creating programmatic, or fabricated, recordsets. In the past, arrays often were used when it was necessary to store several related items. The trouble with arrays is that you must manage the array manually; you can’t add data to a new array element without first creating room in the array. Then came collections, to which you could add your own objects; but collections are also limited to objects and basic data types. So if you want to store some related items (records), but each item has different attributes (fields), you can use a fabricated recordset.
For example, imagine that you need to store details from a questionnaire. You could create a recordset like so:
The good thing about this method is that you can save the contents of this disconnected recordset to a file, and you can use it as a parameter for functions in business objects (just like recordsets obtained from OLE DB Providers).
The Error Object and the Errors Collection
An Error object contains details of a single error (or warning) returned from a data provider. Details include a description of the error, an error number, and the source object that raised the error. When a provider encounters an error or wishes to return information to the consumer, the details are placed in an Error object, and this is appended to the Errors collection.
The Errors collection holds all of the errors when a data provider generates an error or returns warnings in response to some failure. This is provided as a collection because a failure can generate more than one error. For example, the following statement generates two errors because X and Y are unknown columns:
objConn.Open "DSN=pubs" Set objRec = objConn.Execute "SELECT X, Y FROM authors"
You could check the errors generated here by looping through the collection:
For Each objErr In conPubs.Errors Print objErr.Description Next
If you don’t have a predefined Connection object, you can use the ActiveConnection property of the Command or Recordset:
rsAuthors.Open "SELECT X, Y FROM authors", "DSN=pubs", _ adOpenStatic, adLockReadOnly, adCmdTable For Each objErr In rsAuthors.ActiveConnection.Errors Print objErr.Description Next
As with the Fields collection, you could easily build a simple routine that centralizes error handling.
There is always an Errors collection, but it will be empty if no errors have occurred. Error objects remain in the collection until the next data access error occurs, when they are cleared and replaced by the new error details.
The Parameter Object and the Parameters Collection
A Parameter object contains details of a single parameter for a Command object. Some of these details include the name, data type, direction, and value of the parameter. A parameter can be one of the following types:
An input parameter, which supplies values to the statement represented by the Command object
An output parameter, which supplies values from the statement represented by the Command object
An input and output parameter
A return value, which supplies the return value from the statement represented by the Command object
The usage of the various parameter types is discussed in more detail in Chapter 4.
The Parameters collection is unique to the Command object and is one way of allowing you to pass parameters into and out of stored queries and procedures. When using parameters, you have two options. First, you can ask the data provider to fetch the parameters from the data source, and the Parameters collection will be filled in automatically for you:
objCmd.Parameters.Refresh
The downside of this is that it requires a trip to the server, which may cause your program to perform poorly, especially if you do this often. However, it’s a great way of finding out what type and size the provider expects your parameters to be, and is very useful during development and debugging. You should note that not all OLE DB Providers (or ODBC Drivers) support the Refresh method.
The second option is to add the parameters to the collection manually, like so:
Set objParam = objCmd.CreateParameter ("ID", adInteger, _ adParamInput, 8, 147)
This creates an input parameter called ID, which is an integer (length 8 bytes) with a value of 147. Once created, the parameter can be appended to the Parameters collection:
objCmd.Parameters.Append objParam
Another good use of the Refresh method is, when first connecting to the data store, to cache the parameters details locally, perhaps in an array or in a user collection. This sort of approach allows you to write generic routines that process stored procedures.
The Property Object and the Properties Collection
A Property object contains provider-specific information about an object. We know that objects have a fixed set of properties, but one of the fundamentals of OLE DB and ADO is that it can talk to a variety of data providers, and these providers often have different ways of working, or they support different properties. If all providers had to support a fixed functionality, then OLE DB and ADO wouldn’t really be very flexible; they would constitute the lowest common denominator of functionality. The solution is to provide a Property object for each provider-specific property, and a Properties collection to store all provider-specific properties.
For a Connection, the Properties collection contains a large amount of information about the facilities that the provider supports, such as the maximum number of columns in a SELECT statement, and what sort of outer join capabilities are supported. For a Recordset, there’s just as much information, ranging from the current locking level to the asynchronous capabilities supported by the provider.
You can examine or set the value of an individual property by just using its name to index into the collection. For example:
Print conPubs.Properties("Max Columns in SELECT")
To examine all the properties, you can use a simple loop:
For Each objProp In conPubs.Properties Print objProp.Name Next
You may well find that you never use this collection, unless you are writing an application that supports multiple data providers, in which case you may need to query the provider to examine what it supports.
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
The ADOX library contains extensions for Data Definition Language (DDL) and security, providing a way to access schema creation and modification and for accessing security credentials for the schema. Many data stores allow access to schema and security information, but they often differ in the command language used. ADOX abstracts these provider-specific details into a common set of objects, allowing a single code set to be used regardless of provider-specific syntax.
The ADOX (for DDL and Security) object model observes more hierarchy than does the ADO object model, but this is because of the nature of the objects it contains. This means there is a parent object and several child objects, as this diagram for the ADOX object model illustrates:
The Catalog Object
The Catalog object is a central repository, and is really just a container for all of the other objects. The Catalog object allows you to connect to a data store and examine the components comprising it. With the Catalog object, you can use the ActiveConnection property to specify the data store to which you want to connect, and then use the collections to obtain more detailed information. It’s also possible to obtain and set the owner of various objects without using the collections.
To use a catalog, you just point to an existing Connection object or a Connection string. For example:
Dim objCatalog As New ADOX.Catalog Set objCatalog.ActiveConnection = objConn
A Table object represents a single table, containing information such as the table name and the last change date, in a catalog. It is also a container for the Indexes, Keys, and Columns collections (described shortly).
The Tables collection contains Table objects—one for each table in the catalog. I provide an example of the Tables and Columns collections later in this section.
The Index Object and the Indexes Collection
An Index object contains the details for a single index on a table. It identifies attributes such as the index name, whether the index is unique, whether the index allows nulls, and so on. Using the Index object’s Columns collection, you can see which columns comprise the index.
The Indexes collection contains all the Index objects for a particular Table. For example, the following code prints the indexes on a table and the columns that make up each index:
Dim objIndex As ADOX.Index Dim objColumn As ADOX.Column For Each objIndex In objTbl.Indexes Print objIndex.Name For Each objColumn In objIndex.Columns Print objColumn.Name Next Next
The Key Object and the Keys Collection
A Key object contains information regarding a table key. It identifies such items as its name, whether it is a primary or foreign key, the related table, and so on. Like the Index object, the Key object contains a collection of Column objects, identifying the columns that make up the key.
The Keys collection contains a list of keys for a table. For example, the following prints all the keys and columns for a table:
Dim objKey As ADOX.Key Dim objColumn As ADOX.Column For Each objKey In objTabl.Indexes Print objKey.Name For Each objColumn In objKey.Columns Print objColumn.Name Next Next
The Column Object and the Columns Collection
A Column object represents an individual column from a Table, Index, or Key object. In many respects it is similar to the ADO Field object, but rather than storing the details of a field, it holds the details of a stored column, such as its name, its data type, and so forth. For Key columns it contains details of the related columns, and for Index columns it contains details of the clustering and sorting. There is just one Column object, but what it contains depends upon its parent (Table, Index, or Key).
The Columns collection contains all the columns for a particular Table, Key, or Index object. For example, to see all the columns in a particular table, you would use this:
Dim objTbl As New ADOX.Table Dim objCol As ADOX.Column Set objTbl = objCatalog.Tables(0) For Each objCol In objTbl.Columns Print objCol.Name Next
The syntax is very similar when looping through all of the Column objects associated with an Index or a Key object.
The Group Object and the Groups Collection
A Group object identifies a security group. It contains a list of users in the catalog. Its main role is to allow the retrieval and setting of permission for a named group or for allowing access to the Users collection, which contains a list of all users belonging to this group.
The Groups collection contains a list of Groups that belong to a particular catalog, or a list of Groups that a particular user belongs to. For example, the following enumerates the Groups in a Catalog, and then the Users in a Group:
Dim objGroup As ADOX.Group Dim objUser As ADOX.User For Each objGroup In objCatalog.Groups Print objGroup.Name For Each objUser In objGroup.Users Print objUser.Name Next Next
Not all providers support the Group object and Groups collection. At the moment, the Jet provider gives the most comprehensive support.
The User Object and the Users Collection
A User object contains details of a single user of the data store and contains properties to retrieve or set the user name and the user’s unique ID, and methods to read and write permissions. The User object also contains a Groups collection, which is a list of groups to which the user belongs. Be careful about writing recursive procedures that traverse the Users and Groups collections, because they point to each other.
The Users collection contains a list of all users in a catalog. For an example, refer to the preceding description of the Group object.
Not all providers support the User object and Users collection. At the moment, the Jet provider gives the most comprehensive support.
The Procedure Object and the Procedures Collection
A Procedure object identifies a stored procedure in a catalog. The Procedure object has very few properties, mainly because one of these properties represents an ADO Command object. The Procedure object identifies the details about the stored procedure (such as its name and when it was last modified), and the Command object identifies the internal details of the procedure (such as the SQL text).
The Procedures collection contains a Procedure object for each stored procedure in the Catalog. For example, to access a stored procedure and its SQL text, you could use the following code:
Dim objCmd As ADODB.Command Set objCmd = objCat.Procedures(" proc_name").Command Print objCmd.CommandText
This uses the Command property of the Procedure object to reference a standard ADO Command object and print the text (command string) of the Command.
The View Object and the Views Collection
A View object identifies a single view in a Catalog, which is a set of records or a virtual table. Like the Procedure object, the View contains a Command object, allowing access to the view command.
The Views collection contains a list of View objects, one for each view in the catalog.
For example, to obtain all views in a catalog, you could do this:
Dim objView As ADOX.View For Each objView In objCat.Views Print objView.Name Next
Like the Procedure object, the View object also supports an ADO Command as a property. So, to access the details of the command, you could use similar code:
Dim objCmd As ADODB.Command Set objCmd = objCat.Views(" view_name").Command Print objCmd.CommandText
This object and collection is also provider-dependent.
The Property Object and the Properties Collection
The Property object and Properties collection are identical to their ADODB equivalents.
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
ADOX is not supported by all providers, including those supplied by Microsoft. Currently, only the OLE DB Provider for Microsoft Jet fully supports ADOX. For the other Microsoft OLE DB providers, all features are supported except for those listed here:
Microsoft OLE DB Provider for SQL Server
Object/Collection
Feature Not Supported
Catalog Object
Create method
Tables Collection
Properties for existing tables are read-only (properties for new tables can be read/write)
Views Collection
Not supported
Procedures Collection
Append method, Delete method, Command property
Keys Collection
Append method, Delete method
Users Collection
Not supported
Groups Collection
Not supported
Microsoft OLE DB Provider for ODBC
Object/Collection
Feature Not Supported
Catalog Object
Create method
Tables Collection
Append method, Delete method; Properties for existing tables are read-only (properties for new tables can be read/write)
Views Collection
Append method, Delete method, Command property
Procedures Collection
Append method, Delete method, Command property
Indexes Collection
Append method, Delete method
Keys Collection
Append method, Delete method
Users Collection
Not supported
Groups Collection
Not supported
Microsoft OLE DB Provider for Oracle
Object/Collection
Feature Not Supported
Catalog Object
Create method.
Tables Collection
Append method, Delete method; Properties for existing tables are read-only (properties for new tables can be read/write)
Views Collection
Append method, Delete method, Command property
Procedures Collection
Append method, Delete method, Command property
Indexes Collection
Append method, Delete method
Keys Collection
Append method, Delete method
Users Collection
Not supported
Groups Collection
Not supported
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
I’ve already mentioned that ADO can connect to many different data providers, and this is because of OLE DB. Remember that ADO is just a layer sitting on top of OLE DB to hide the complexity. It seems sensible, though, to look at some of these OLE DB data providers in more detail. You need to see their differences, for although most of the ADO usage will be the same, OLE DB providers don’t always support the same facilities. This is natural, because some of them are fundamentally very different. Most of the relational database providers, for example, will provide similar facilities, but other providers (such as for Internet Publishing) might not work the same way.
When you install MDAC, you are supplied with the following OLE DB providers:
Provider
Description
MSDASQL
Microsoft OLE DB Provider for ODBC: allows connection to existing ODBC data sources via either a System DSN or dynamically provided connection details. See Chapter 1 for how ODBC Drivers relate to OLE DB.
Microsoft.Jet.OLE DB.4.0
Microsoft OLE DB Provider for Jet: allows connections to be established directly to Microsoft Access databases, including Access 97 and Access 2000.
SQLOLEDB
Microsoft OLE DB Provider for SQL Server: allows connections to be established directly to Microsoft SQL Server databases.
MSDAORA
Microsoft OLE DB Provider for Oracle: allows connections to be established to Oracle databases.
MSIDXS
Microsoft OLE DB Provider for Index Server: allows connections to be established to Microsoft Index Server.
ADSDSOObject
OLE DB Provider for Microsoft Directory Services: allows connections to be established to Directory Services such as the Windows 2000 Active Directory and the Windows NT 4 Directory Services.
MSDataShape
Microsoft OLE DB Data Shape Provider: provides the ability to create hierarchical recordsets.
MSPersist
Microsoft OLE DB Persistence Provider: allows recordsets to be saved or persisted and later reconstructed.
MSDAOSP
Microsoft OLE DB Simple Provider: allows connections to be established to custom OLE DB Providers that expose simple (relational) data.
MSDAIPP.DSO.1
Microsoft OLE DB Provider for Internet Publishing: allows connections to be established to DAV-compliant servers to aid publishing data on the Internet.
These are just the defaults supplied, some of which may not appear, depending upon your installation options. For example, Index Server does not act as a remote provider, and you can connect to only local Index Servers. Therefore, if you do not have Index Server on your machine, the MSIDXS may not be installed. Installation of versions later that 2.6 on top of an earlier version will not remove any deprecated providers, and these will not be installed on a clean MDAC 2.8 install.
You can find out which providers are available on your system by creating a new Data Link. This is similar to an ODBC DSN, but for OLE DB connections. By using Windows Explorer, you can create one in any directory simply by right-clicking (or selecting the File menu), picking New, and selecting Microsoft Data Link. This creates a file with a .udl extension, and you can then view its properties. If this option isn’t available, you can create a text file and rename the extension to .udl. Open the UDL file by double-clicking it. From the Provider tab in the Data Link Properties window, you can see the available providers, as shown here:
In fact, using the Data Link is a good way to create connection strings if you are unfamiliar with them; you can even use them instead of a DSN within connection strings (see the next section). You can create a Data Link file, filling in the details onscreen, and then examine the resulting .udl file in Notepad.
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
One of the major differences among the various OLE DB providers is found in the connection string: Different providers require different information to be able to make the connection to the data store.
ADO recognizes only four of the arguments in the connection string; the remaining arguments are passed on to the provider. The main argument you are interested in is Provider, which identifies the OLE DB provider to be used. The second argument is File Name, which can be used to point to an existing Data Link file. If you use a Data Link file, you can omit the Provider argument, because the Data Link file contains this information. The other two arguments (Remote Provider and Remote Server) relate to Remote Data Services; I describe them in detail in Chapter 9.
OLE DB Provider for the ODBC Drivers
The OLE DB Provider for ODBC is the default provider, so if you don’t specify which one to use, this is what you’ll get. If you do specify this provider explicitly in your connection string, you must give the rather obscure name MSDASQL. When using the OLE DB Provider for ODBC, you have three choices: use an existing ODBC System DSN, a DSN-less connection string, or an ODBC File DSN.
For a DSN-based connection, simply specify the data source name:
For a DSN-less connection, the connection string varies with the database you are connecting to. It follows the same conventions as an ODBC connection string; in the Control Panel, you can see the parameters in the ODBC applet. One important option is the same for all ODBC connections, because it specifies the ODBC driver to use:
Provider=MSDASQL;Driver=
The name of the driver specified in the Driver attribute of the connection string will be one of those shown on the Drivers tab of the ODBC Control Panel applet. Your list of drivers may differ from those listed in this screen:
On Windows 2000 and Windows XP the ODBC Data Source Administrator, known as simply Data Sources (ODBC) on this platform, can be found under the Administrative Tools group. Regardless of platform, the list of ODBC Drivers installed on a given system can also be found in the system registry at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers .
You should enclose the driver name in curly braces. Let’s look at some examples.
Microsoft Access
To connect to Microsoft Access, your connection string would start like this:
Note: An interesting fact has recently come to light that causes the following error with the OLE DB Provider for ODBC:
Microsoft OLEDB Provider for ODBC Drivers error ‘80004005’
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
This seems to occur if you leave a space after the first semicolon in the connection string shown in the preceding code example, just before the Driver. So, this works:
The second is by specifying the sheet name with a row and column area:
objRs.Open "Select * from `Sheet1$A2:C4`", oConn, _ adOpenStatic, adLockBatchOptimistic, adCmdText
The third is by specifying a range name:
objRs.Open "Select * from myRange1", oConn, adOpenStatic, _ adLockBatchOptimistic, adCmdText
To use the range method, you must first make sure that a range has been specified in the spreadsheet. This range should enclose all the data that you wish to select, and it equates to the recordset. You can have any number of ranges in a spreadsheet. To create a range, you select the cells in the spreadsheet and enter the name in the range box.
You then specify the range as the Source parameter of the Recordset’s Open method:
objRec.Open "Authors", objConn
In all three cases, the connection string must specify Microsoft Excel as the ODBC driver and the spreadsheet name as the data store name:
Text files are slightly different from Excel files in the following respect: In the connection string, you specify the directory where the text file resides, rather than the text file itself:
Provider=MSDASQL;Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\directory_name
You then specify the text file as the name of the recordset to open:
objRec.Open "TextFile.txt", objConn
OLE DB Provider for Jet
When using the provider for Jet, you must specify only the database name in the Data Source attribute of the connection string:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\mdb_name.mdb
If you have a system database, you can use the Properties collection to set this before opening the connection, but you must specify the provider first:
When establishing connections to SQL Server databases using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB), the Data Source attribute is used to specify the name or address of the SQL Server. Consequently, an additional attribute named Initial Catalog is used to specify the name of the database for which the connection will be made:
Provider=SQLOLEDB; Data Source= server_name; Initial Catalog= database_name; User Id= user_id; Password= user_password
For example:
Provider=SQLOLEDB; Data Source=Tigger; Initial Catalog=pubs; User Id=sa; Password=
OLE DB Provider for Index Server
For Index Server you need to specify only the provider name, unless you have multiple catalogs in use under Index Server. In this case you use the Data Source to specify the required catalog:
Provider=MSIDXS; Data Source=catalog_name
OLE DB Provider for Internet Publishing
The Internet Publishing provider allows you to connect to servers that support either the Microsoft FrontPage Server Extensions or DAV (also known as WebDAV or HTTP-DAV) protocol. This allows you to use ADO to query the servers for directory contents, resources, and so on, as well as to update these resources. You use the Data Source attribute to specify the name of the Web server:
Provider=MSDAIPP.DSO.1; Data Source=http://web.server.name
Alternatively, you can add URL= to the front of the Data Source you are opening, which tells ADO that the Internet Publishing provider is being used. For example:
recRoot.Open "", "URL=http://web.server.name"
Data Link Files
A Data Link file can contain connection details for any OLE DB Provider. Data Link files have a .udl suffix and allow the connection details to be stored in a file rather than being embedded in an application. Data Link files can be created by using Windows Explorer. Under Windows 2000 and Windows XP, you should create a Text File, and then rename the suffix to .udl. Double-clicking this file opens the Data Link Properties dialog. Under previous versions of Windows (and ADO), you can select New Data Link File from the Explorer context menu.
To use a Data Link file as a connection string, simply set the File Name option to point to the .udl file:
objConn.Open "File Name=C:\temp\pubs.UDL"
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
Asynchronous processing was a new feature in ADO 2.0 and allows commands to be executed at the same time as other commands. This is particularly useful when creating very large recordsets or running a query that may take a long time, because you can continue with another task and allow ADO to tell you via an event when the command has finished. You can also cancel a long-running command if you wish. Events can also be used to perform preprocessing and postprocessing around certain ADO operations, such as opening a Recordset or establishing a Connection.
Note that ASP scripting languages do not support events on the server. Client-side script does support events.
Events
For ADO, only the Recordset and Connection objects support events. There are generally two types of events:
Will… events—those called before an operation starts
…Complete events—those called after it has completed
A few other events are called after an event has completed, but they are not part of the Will… and …Complete event pairs.
A Will… event is called just before the action starts. It gives you the chance to examine the details of the action, and to cancel the action if you decide not to run it. The …Complete event is called just after the action completes, even if the operation was cancelled. If it was cancelled, the Errors collection is filled with details of why it was cancelled.
The Will… events generally have an argument, adStatus, indicating the status of the event. You can set this argument to adStatusCancel before the method ends, to cancel the action that caused the event. For example, suppose a connection generated a WillConnect event; setting adStatus to adStatusCancel will cancel the connection. The ConnectComplete event will then be called, with a status indicating that the connection failed.
The nature of events means that there can be several Will… events that could result from a single action. Although all of these will be called, there is no guaranteed order in which this will happen.
Event Pairings
The way the Will… and …Complete events are generated can often be confusing, especially in terms of what happens when you wish to cancel the operation that raised the event. The action triggers the Will… event, which has its own event procedure. This code runs, and then the action itself is run. After the action is finished, the …Complete event is triggered, which runs its event procedure. Once that has completed, execution will continue at the line after the action. The following diagram shows this using Connection events when opening a connection:
In the WillConnect event procedure, you can decide whether you want this action (that is, the objConn.Open) to occur or not. The event procedure has a parameter called adStatus, which you can use. If you set this to adStatusCancel before the event procedure finishes, the action is cancelled. The ConnectComplete event will still run, but its parameters will indicate that an error has occurred. For example, the following diagram shows how this can be trapped:
The important thing to note is that the ConnectComplete event is still generated, but the connection does not actually take place. Some actions may generate more than one event, so what happens then? Well, the Will… event is always generated before its associated …Complete event, but when two Will… events exist, the order in which they are generated is not guaranteed. If you cancel the action from within an event procedure, and this happens to run as the first event procedure, then the second set of event procedures (both the Will… and the …Complete) are never generated. For example, imagine a recordset where you have events to detect when fields and records change, as shown here:
Here the action has been canceled in the WillChangeRecord event procedure. The RecordChangeComplete event procedure runs, but indicates that an error occurred, and the actual action, that of setting the field value is not executed. The WillChangeRecord and RecordChangeComplete event procedures are not run.
These diagrams use Visual Basic, but language choice is not crucial; it’s the order of events that’s important.
Events in Visual Basic
To use events within Visual Basic, you declare a variable by using the WithEvents keyword. This must be a module/form/class/control level or global variable, because WithEvents is invalid within procedures:
Private WithEvents m_rsAuthors As ADODB.Recordset
Note that the New keyword is omitted when using this syntax, because you can’t instantiate objects at the same time as declaring the variable with events. You must do this separately:
Set m_rsAuthors = New ADODB.Recordset
Once you have declared the variable, the object will appear in the objects list in the code combo and in a list of events in the event drop-down list (for a selected object). Selecting an event will create the event procedure.
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.
BeginTransComplete is called after a BeginTrans method has completed.
CommitTransComplete is called after a CommitTrans method has completed.
RollbackTransComplete is called after a RollbackTrans method has completed.
WillConnect is called just before a connection is established.
ConnectComplete is called after a connection is established.
Disconnect is called after a connection has disconnected.
WillExecute is called before an Execute method is run.
ExecuteComplete is called after an Execute method has completed.
InfoMessage is called when the provider returns information messages.
An example of the …Complete events could be to indicate to users the status of their connection. The individual events for a Connection are discussed in more detail in Chapter 3.
Recordset Events
The Recordset has more events than the Connection:
FetchProgress is called periodically during an asynchronous recordset creation.
FetchComplete is called when the recordset has been fully populated with its records.
WillChangeField is called before an action causes a field to change.
FieldChangeComplete is called after an action caused a field to change.
WillMove is called before an action causes the current record to change.
MoveComplete is called after an action caused the current record to change.
EndOfRecordset is called when there is an attempt to move beyond the end of the recordset.
WillChangeRecord is called before an action causes the data in the current record to change.
RecordChangeComplete is called after an action caused the data in the current record to change.
WillChangeRecordset is called before an action causes a change to an aspect of the record-set (such as a filter).
RecordsetChangeComplete is called after an action caused the recordset to change.
The Will… events can be used to notify the user that an action may change data or may change the underlying records in the recordset.
A word of caution when using the new ADO Data Control in Visual Basic 6: This automatically updates records when using the video-style buttons to move around the records. This differs from the standard Data Control. If you change a field, then the WillChangeField event is called, and if the value is not correct, you can cancel the action. However, it is the Update that is cancelled, not the move. It’s not a major problem, but something to bear in mind.
The individual events for a Recordset are discussed in more detail in Chapter 5.
Object Usage
Some people are confused about when to use which object. After all, the Connection, Command, and Recordset objects are all capable of returning a recordset of data. There are only a few hard-and-fast rules, but in general, here’s what you should do:
If you only ever need to run queries that don’t return a recordset, and those queries are not parameterized statements (such as stored procedures) with output parameters, then use the Connection object and the Execute method.
If you need to use stored procedures with output parameters, you must use the Command object.
If you need to specify the cursor type or lock type, then you must use a Recordset object.
If you are creating only one or two recordsets of data, then you can use the Recordset to implicitly establish a connection to a data store by passing in a connection string in the ActiveConnection parameter of a Recordset object’s Open method.
If you are creating two or more recordsets, then explicitly create a Connection object first, then reuse the Connection object when opening the Recordset objects. Remember that each time you connect to a data store with a connection string, a new connection is opened or retrieved from the connection pool.
Summary
This chapter has introduced the ADO object model, examined how you go about connecting to data stores, and looked at the connection strings for a variety of OLE DB providers. It has also briefly introduced asynchronous processing and the use of events, the details of which are covered in the Chapters 3 and 5, where the Connection and Recordset objects are discussed in more detail.
Most of the rest of the book is concerned with the properties, methods, and events of the various ADO objects and related libraries, such as ADOX, ADOMD, and Jet Replication Objects (JRO).
This chapter is from ADO Programmer's Referencep,by David Sussman. (Apress Books, 2004, ISBN: 1590593421). Check it out at your favorite bookstore today.