| Downloads |
|---|
| 99419.zip |
|
Executive Summary: Learn how to use Microsoft ActiveX Data Objects (ADO) to access, filter, sort, and retrieve data from Microsoft SQL Server and Microsoft Access databases from VBScript scripts.
|
VBScript applications often access data in Microsoft SQL Server or Microsoft Access databases. An easy way to facilitate that access is to use Microsoft ActiveX Data Objects (ADO). ADO is a set of COM objects that act as an interface between VBScript and the OLE DB provider specific to SQL Server or Access. Even if you don’t use VBScript and ADO in your current scripting projects, existing production scripts might still use VBScript and ADO. COM-based scripting languages are still the primary focus in some organizations, and Microsoft continues to support ADO access in SQL Server 2008 and Access 2007.
By using ADO, you can connect to an instance of SQL Server or to an Access database file, issue an SQL command against database objects, and retrieve and modify data. The ADO objects let you access both types of data sources —SQL Server and Access—in a consistent manner. The primary difference between the two is in how you define the connection string.
This article will explain how to use ADO objects in SQL Server and Access databases to retrieve data; sort, filter, and find data; and work with large text and binary data fields. Although this article focuses on accessing SQL Server and Access data in VBScript, you can use ADO in other programming languages and scripts and use ADO to access other data sources. If you’re new to ADO, I recommend that you read the article "Introduction to ADO" for general ADO information.
The ADO Object Model
Before I get into the details of how to use ADO to interact with SQL Server and Access, let’s first look at the ADO object model. Figure 1 shows the primary ADO objects. They include the following types:
• Connection—defines the connection to the specific data source; you must pass the connection string as an argument when you open the connection
• Command—defines the command executed against the data source; for SQL Server and Access, the command includes an SQL statement that acts upon a database object, such as a table or stored procedure
• Parameter—defines a parameter that's passed to a stored procedure or parameterized query; each Parameter object is part of the Parameters collection associated with the Command object
• Recordset—contains the result set returned by your query; when the result set is returned, you can view and manipulate the data in the recordset; if you change the data, you can propagate those changes back to the SQL Server or Access database
• Field—contains a field (column) included in the Recordset result set; each Field object is part of the Fields collection; a Fields collection is associated with both the Recordset and Record objects
• Record—contains data retrieved from a hierarchical data source, such as a file and directory structure; this object generally doesn't apply to relational database sources such as SQL Server and Access; I’ve included it here so you'll understand its distinction from the Recordset object should you come across it in other documentation
• Stream—exposes a set of methods that let you work with large text and binary data fields, such as those used to store the binary data for documents and images
• Property—stores dynamic property information specific to the database system and provider; each Property object is part of the Properties collection that's available to most ADO objects
Although Figure 1 shows the objects hierarchically, it's a loose hierarchy and you don't necessarily need to create one type of object before you can create another type. For example, you can create a Recordset object without creating a Connection object. An important resource to keep in mind when implementing ADO is the ADO API Reference on the MSDN website.
Retrieving SQL Server Data
Thanks to the flexibility of the ADO object model, you can often take different approaches to achieve the same results. For example, one approach you can take to retrieve data is to first create a Connection object, then a Command object, and finally a Recordset object. You can also retrieve data by creating only a Recordset object.
In this section, I take the first approach to show you how to use many of the ADO objects. As you progress through the article, you’ll learn about other, less formal approaches. You can also refer to "Introduction to ADO" for additional samples that show how to retrieve data and for a discussion about the differences between these approaches.
For many of the SQL Server examples in this article, I created the Production.Inventory table in the AdventureWorks sample database that ships with SQL Server 2005. If you didn’t install the database when you set up SQL Server, you can download it at CodePlex, where you'll also find a link to simplified instructions for manually attaching the database.
Listing 1 shows the SQL code I used to create the Production.Inventory table. I then imported the table into an Access database and named the table Inventory. For information about importing data into Access 2007, see the Microsoft article “Import or link to SQL Server data” or go to the Office website.
Now let’s take a look at Listing 2, which shows how to retrieve data from a SQL Server database. The script begins by declaring the variables necessary to define the Connection, Command, and Recordset objects, along with a supporting FileSystemObject object. You should also define any constants necessary to pass in as ADO property and method arguments (e.g., the adStateOpen constant). For specific details about these constants, refer to the ADO API Reference page.
After you define the necessary variables and constants, you can create the ADO objects. As the code in callout A in Listing 2 shows, the first object is Connection (ADODB.Connection), which in this example I assign to the conSqlServer variable. Next, define the connection string and assign it to the appropriate variable (csSqlServer, in this case).
The connection string for a SQL Server connection, at its most basic, should define the following:
• the OLE DB provider, which for SQL Server is SQLOLEDB (Provider='sqloledb')
• the SQL Server instance (Data Source='ws04'); if you run this script or any of the other scripts that access SQL Server, be sure to change the data source name to the server where you have SQL Server installed
• the security type; in this case, I’m using integrated security and the Security Support Provider Interface (SSPI), a common API for obtaining integrated security services (Integrated Security='SSPI')
• the database (Initial Catalog='AdventureWorks')
Note that instead of using the Integrated Security property to specify the security type, you can use the User ID and Password properties to pass account information.
After you define the connection string, use the Connection object’s Open method to open the connection. As an argument to the method, pass in the connection string (csSqlServer).
Now you’re ready to define the command and its text. First, create the Command object (ADODB.Command), then set the object’s ActiveConnection property to the Connection object, as shown in callout B. Next, define the command text, which is the SQL statement you’ll use to retrieve data from the Production.Inventory table. Assign the text to the appropriate variable (ctInventory, in this case). Finally, set the Command object’s CommandText property to the command text.
After you’ve defined the Connection and Command objects, you can create the Recordset object (ADODB.Recordset) and assign it to the appropriate variable, which in this case is rsInventory, as you can see in callout C. Then use the object’s Open method and pass in the Command object as an argument. When you open the Recordset object, you’re essentially running the command text query and retrieving the result set. That’s all there is to retrieving the data—after defining the Connection, Command, and Recordset objects, you can access the data in the Recordset object by using the Fields collections and Field objects.
In callout D of Listing 2, you'll see that I’ve implemented a Do Until loop. Notice that in defining the loop, I use the Recordset object’s EOF property so that the loop runs only till it reaches the last row of data in the recordset. I also use the object’s MoveNext method to iterate through each row in the recordset.
Now let’s look at what the loop does. When accessing a Field object, you call the Fields collection and then specify the name of the field. For example, in the If statement, I check whether the value of the Color field is null. Notice that I include the field name in quotes and parentheses, followed by the Value property. For rows in which the Color field is null, I add the Name field and Product Number field to the SqlServerInventory.txt file. If the Color field isn't null, I also include the Color value in the file. If you plan to run this script, be sure to create the C\Info folder or change the name of the folder within the script to an existing folder.
Retrieving Access Data
As you can see, using ADO objects to retrieve SQL Server data is a straightforward process. And the process to retrieve data from an Access database is just as easy. If you refer to Listing 3, you’ll find that it's nearly identical to Listing 2. The most important difference is the connection string. For Access, you use the OLE DB provider Microsoft.Jet.OLEDB.4.0, rather than SQLOLEDB. In addition, for the Data Source property, you specify the full path name of the Access file, rather than the SQL Server instance. For this example, I used an Access file named Inventory.mdb. Note that I connected to a pre-Access database, which you can see by the .mdb file extension). If you want to connect to an Access 2007 database (.accdb extension), you need to modify the connection string as follows:
csAccess = "Provider=Microsoft.ACE.OLEDB.12.0;" & _"Data Source=C:\Info\Inventory.accdb;"
Another difference between Listing 2 and Listing 3 is the command text. For Access, I specify the table name as Inventory rather than Production.Inventory. Other than the table name in the command text and the connection string, everything else about the script is exactly the same as in Listing 2.
As you can see, the process of retrieving data from SQL Server is almost identical to retrieving data from Access. In fact, the remaining examples in this article retrieve data only from SQL Server. However, if you want to try them on Access, simply modify the connection string and command text, as I’ve done in Listing 3.
One other thing to note about these two listings is the clean-up I do at the end of the script. Because I opened the Recordset and Connection objects, I specifically close them. To do so, I first determine whether the State property equals adStateOpen, and then I set all the variables to Nothing to free up memory. If I don’t check the status first, I’ll receive an error if I try to close something that is already closed. Now let’s look at some of the ways you can manipulate the data in the recordset.
Sorting, Filtering, and Finding Recordset Data
When working with recordset data, you might want to sort that data, filter out certain records, or retrieve a specific record. For this reason, the Recordset object supports the Sort and Filter properties, as well as the Find method. These features can help you avoid making unnecessary calls to the database, particularly when you’re using Filter and Find.
The Sort property uses field names to order the records in the recordset. For example, the code at callout A in Listing 4 sorts the data in ascending order based on the Name field. In this example, I’ve set the Sort property to the field name and specified ASC to indicate that the data should be sorted in ascending order. Because ASC is the default, you don't have to specify it. If you want to sort the records in descending order, specify DESC. That’s all there is to using the Sort property.
There are a few other features to point out in callout A. One line of code sets the CursorLocation property of the Recordset object to adUseClient. This property determines whether the result set will be stored server side (the default) or client side. For SQL Server server-side cursors, the database engine manages the result set. For Access server-side cursors, the OLE DB provider manages it. However, the ADO cursor engine manages all client-side recordsets, and you can use the Sort property only with client-side recordsets. Client-side recordsets are generally recommended over server-side ones because client-side recordsets scale better and—as you’ll see in a moment—better support properties and methods such as Sort, Filter, and Find.
Now take a look at the next line of code in callout A. Notice that the Recordset object’s Open method includes several arguments. The first argument (ctInventory) is the command text. Rather than creating a Command object, I simply pass in the command text to the Open method. If I pass in command text rather than the Command object, I must also specify a connection as the second argument. In this case, the second argument is simply the connection string. I can specify either the connection string or a Connection object. By passing in the command text and connection string in this way, I simplify my code. However, this approach is recommended only if you’re creating a script that doesn’t require multiple calls to the database or special settings on the Connection and Command objects.
The third argument in the Recordset object’s Open method is the adOpenStatic constant, which refers to the cursor type. The adOpenStatic constant supports forward and backward scrolling but prevents you from seeing other users' changes to the source data. However, this is the only cursor type you can use for client-side recordsets. After you set the CursorLocation property and call the Open method to open your recordset, you simply sort the data, as Listing 4 shows.
The Filter property is a little different from the Sort property. You can use Filter with server-side recordsets, but it’s generally designed for the client side. That’s because ADO can build temporary indexes on client-side recordsets, but not server-side ones; the same condition applies to the Find property. Now let’s look at Listing 5, which is the same as Listing 4 except that at callout A I’ve added a line of code to filter the recordset. In this case, I specify that the Quantity field must be greater than 1500. As a result, the recordset will display only those records that satisfy this condition.
Now suppose you want to retrieve only a specific record from a recordset. That’s where the Find method comes in. As callout A in Listing 6 shows, when I call the Find method, I pass in an expression as an argument that specifies that the ProductID value must be 317. Now the recordset will display only those records with the correct ProductID value; because ProductID values are unique, only one record will be displayed. After you use Find to locate your record, you can then access it through the Fields collection. In this case, I simply display the product details in a message box.
One other tip: Instead of writing the output to a message box, you can write the results to the console by setting your default scripting engine to cscript. To do so, run the following command at the command prompt:
cscript //h:cscript
Then, instead of creating a FileSystemObject object, simply create your Do Until loop using the following code:
Do Until rsInventory.EOF
WScript.Echo(rsInventory.Fields("Name").Value &_
" (" & rsInventory.Fields("ProductNumber").Value & ") - " &_
rsInventory.Fields("Quantity").Value)
rsInventory.MoveNext
Loop
Retrieving Text and Binary Data from Large Fields
The examples so far have all used a combination of the Connection, Command, Recordset, and Field objects to retrieve and work with the data. However, another important object is Stream, which can be useful when you’re working with large text and binary data fields. By using the Stream object, you don’t have to work with the data in chunks, as you did in earlier versions of ADO.
To use the Stream object, you must first define your Recordset object, as Listing 7 shows. In this example, I retrieve data from the Production.ProductModel table in the AdventureWorks database for the row with a ProductModelID of 7. The Instructions column in this table contains XML data that I want to save as an XML file.
After you open the Recordset object, you should create the Stream object (ADODB.Stream), as callout A in Listing 7 shows. Next, set the object’s Type property to adTypeText. This indicates that you’ll be retrieving text data (as opposed to binary data). Finally, use the object’s Open method to open the object, and then use the WriteText method to retrieve the data from the Instructions Field object, which you access through the Recordset object. After you retrieve the data into the Stream object, you can use the object’s SaveToFile method to save the XML data to a file. Note that you can open the XML file in Internet Explorer to easily see the XML hierarchy.
Retrieving binary data is very similar to retrieving text data, as you can see in Listing 8. In this case, the example retrieves data from the Production.ProductPhoto table for the row with a ProductPhotoID value of 79. The table includes the LargePhoto column, which is configured with the VARBINARY(MAX) data type. As you saw in the previous example, you must create a Recordset object and then a Stream object. However, this time you should set the Stream object’s Type property to adTypeBinary. Next, open the Stream object, then use the object’s Write method (rather than the WriteText method) to retrieve the data from the LargePhoto field. Finally, call the SaveToFile method to save the data as a .gif file. As you can see, the Stream object makes it quite easy to retrieve information from large data fields, whether they’re text or binary fields.
Working with ADO
As you continue to work with the ADO objects, you’ll find that they offer a great degree of flexibility when retrieving data from SQL Server and Access databases. And the great advantage to ADO is the consistent manner in which you access either type of data source (the primary difference being in how you define the connection string). In the second part of the article, you’ll learn how to use the ADO objects to modify SQL Server and Access data. As you’ll see, you’ll be using the same objects to modify data that you used to retrieve the data.