Editor's note: This article is the fifth part of a 12-part series about Active Directory Service Interfaces (ADSI). The series started in the January 1999 issue. Refer to previous installments for definitions and background information.
Microsoft's ADO technology features fast algorithms that let you quickly scan for database objects that match certain criteria, conduct database searches, and retrieve the results of searches (i.e., result sets). These same fast algorithms also let you update the retrieved data and return it to the database. Because Microsoft created an ADO database connector for ADSI (i.e., the ADO OLE DB connector), you can use ADO's database query technology on valid ADSI providers, such as Lightweight Directory Access Protocol (LDAP), and Novell Directory Services (NDS).
However, the ADO OLE DB connector is currently read-only, so many of the useful ADO methods for updating data aren't available. Until this situation changes, you can use ADO only for searching and retrieving. Despite the read-only limitation, using ADO technology is still a boon. Without it, you would have to recursively search a database for objects.
The ADO object model consists of seven objects (Command, Connection, Error, Field, Parameter, Property, and Recordset) and four collection objects (Errors, Fields, Parameters, and Properties). However, some of these objects aren't useful if you're using the ADO OLE DB connector. For example, the Parameter object lets you pass parameters to stored procedures, but this object is of little use because the ADSI provider doesn't support stored procedures.
In this article, I show you how to use some of these objects to perform a basic search. Suppose you want to build an ADO query to search and display the ADsPaths of all users in the Windows 2000 (Win2K) Active Directory (AD). Listing 1, page 2, contains the script that performs this search. You can create this script in six steps.
Step 1. Define the Constants and Variables
For the script in Listing 1, you need to define one constant and two variables. The constant is adStateOpen, which you set to 1. If you're using Visual Basic Script (VBScript), you use this constant later to determine whether you made a successful connection to the database. If you're using Visual Basic (VB), you don't have to include this constant because VB has already defined it. The two variables are adoConnection (an ADO Connection object that lets you connect to the AD database) and adoRecordset (an ADO Recordset object that lets you hold the retrieved result set).
The inclusion of the Option Explicit statement at the beginning of the script is optional. However, I recommend that you include it. This statement forces the script to declare variables, so you can quickly spot errors.
Step 2. Establish an ADO Database Connection
To perform an ADO query, you need to establish an ADO connection. Before you can establish this connection, you must create an ADO connection object to use. You create this object the same way in which you create a file system object. You use VBScript's CreateObject method, with "ADODB.Connection" as a parameter. You use the ADODB prefix to create all ADO objects, and Connection is the top-level object in the ADO object model.
Just as you use different programmatic identifiers (ProgIDs—e.g., WinNT:, LDAP:) to tell ADSI which directory to access, you use different OLE DB providers to tell ADO which query syntax to use. (An OLE DB provider implements OLE DB interfaces so that different applications can use the same uniform process to access data.) ADSI's ADO OLE DB supports two forms of syntax: the SQL dialect and the ADSI dialect. Although you can use the SQL dialect to query the ADSI namespace, most scriptwriters use the ADSI dialect because Microsoft defined it specifically for ADO queries to directory services. However, the default for the Connection object's read/write property, adoConnection.Provider, is "MSDASQL", which specifies the use of the SQL syntax. Because you want to use the ADSI provider, you need to set adoConnection.Provider to "ADsDSOObject", which specifies the use of the ADSI syntax. By setting this specific provider, you force the script to use not only a specific syntax but also a specific set of arguments in the calls to the Connection object's methods.
Step 3. Open the ADO Connection
You open a connection to the directory by calling the Connection::Open method. When describing the methods and property methods of COM interfaces in text, the established notation is to use a double colon (::) separator. So, for example, Connection::Open specifies the Open method of the Connection object.
As callout A in Listing 1 shows, the Open method takes three parameters. (The underscores specify that the command continues on the next line.) The first parameter is the ConnectionString parameter, which contains information that the script needs to establish a connection to the data source. The second and third parameters contain authentication information.
In Listing 1, you're authenticating with a username (the second parameter) and that username's password (the third parameter). You can leave the first parameter blank. Here's why: In ADO, you can perform the same task many ways because the Command, Connection, and Recordset objects heavily interrelate. If you set the properties of one object, you can use those same properties to open the connection of another object as long as you're not setting any new options. Such is the case in Listing 1; you're opening the connection without setting any new options. In other words, you leave the first parameter blank because that information already exists.
You then use an IF THEN ELSE statement to see whether the Open call worked. If the call succeeded (i.e., the connection state has a value of 1), the script prints the message Authentication Successful and proceeds to the query. If the call didn't work (i.e., the connection state has a value of 0), the script prints the message Authentication Failed and quits, setting the returned error code to 1.
Step 4. Execute the Query
You use the Connection::Execute method to perform the query. You pass Connection::Execute a parameter that is a string containing four arguments separated by semicolons. If the string contains spaces before or after the semicolons, the code will fail. If you forget to remove the spaces, you might have trouble debugging the problem because the error just states that a parameter is invalid. Thus, you must remember to use semicolons with no preceding or trailing spaces. You must also remember to enclose the parameter in parentheses because you're passing the Execute method's result to a variable.
The four arguments for any LDAP query you want to execute are search base, filter, attributes, and scope. The search base argument specifies the point in the directory from which the search is to start. You must use a full ADsPath to specify the search base and enclose the ADsPath in angle brackets (< >). In Listing 1, you're starting from the directory's root (i.e., LDAP://dc=windows,dc=mycorp,dc=com).
The filter argument defines which objects match the query. You must enclose this argument in parentheses. You must also use a special format that Request for Comments (RFC) 1960 defines. (For more information about this format, see the article "Understanding ADO Search Filters in LDAP Queries," page 5.) The filter in Listing 1 specifies that you're looking for the object class of users.
The attributes argument is a comma-delimited list of attributes to return. You must specify each attribute individually. Unlike the IADs::Get method, which executes an implicit GetInfo call to obtain all attributes, this ADO search returns only the specified attributes in the result set. In this case, the ADO search will return the Name and ADsPath attributes. The ADsPath is a useful attribute to retrieve because it lets you use ADSI to bind to that object. You can then perform an explicit GetInfo to obtain all the attributes for that object.
The optional scope attribute is important only in hierarchical directories, where you can specify how far down from the query's starting point you want to search. You can specify one of three string constants: Base, OneLevel, or Subtree. If you set scope to Base, the ADO search checks only objects directly within that container. If you set scope to OneLevel, the ADO search checks any object directly under the root and any objects directly under containers within the root. If you set scope to Subtree, as Listing 1 does, the ADO search checks every container in the tree.
Step 5. Navigate Through the Result Set
The adoRecordset variable holds the result set. Recordset objects have a table-like structure. The structure's columns are fields, and the rows are records. Fields correspond to the attributes you want to return and assume the titles of those attributes (e.g., Name or ADsPath). ADO also numbers the fields from left to right, starting with 0. Thus, you can access fields using attribute names or index numbers. Records correspond to the values of those attributes.
To manage the members of adoRecordset, you can use one of the Recordset object's many methods and properties. The simplest approach is to use the Recordset::MoveNext method (which navigates to the next record in the result set) with the Recordset::EOF (end of file) property method (which tests whether you're at the end of the result set). As callout B in Listing 1 shows, you're using these two methods in a simple WHILE loop to move through each record. If Recordset::EOF returns a value of false (i.e., you're not at the end of the result set), the script prints the contents of the record for each field and moves on to the next record. If Recordset::EOF returns a value of true (i.e., you're at the end of the result set), the script exits the WHILE loop.
Callout B in Listing 1 can look complicated at first glance. You're using adoRecordset.Fields, which is a Fields collection object. As with all collections, Fields has a method called Item. The Fields::Item method takes an argument that equates to either the name of the field or its index number. The Fields::Item method returns a Field object. You then use that object's Value property method to return the value of the record for that field. In other words, the code
is telling the script to return the value of the individual field called Name from the collection of all possible fields.
Step 6. Close the ADO Connection
You use the Connection::Close method to close the connection to the directory. You then set the Recordset object to nothing to make sure you don't mistakenly reuse it. This line isn't mandatory because you've finished the script. However, I recommend that you use it. That way, if you later add code to the end of the script, you can't mistakenly reuse the now-defunct adoRecordset variable without reinitializing it first.
Only a Scratch
So far, I've only scratched the surface of ADSI access options that are available through the various objects provided in ADO. In the future, I'll delve further into ADO, looking at the SQL dialect and the extra parameters you can set to control the data that you receive from your searches. Next month, however, I'll cover the IADsUser interface in depth, showing you how to write simple scripts and utilities that programmatically create users.
This article is adapted from Alistair G. Lowe-Norris' forthcoming book about the Windows 2000 Active Directory (O'Reilly and Associates).