A Searching Sample Using ADO and VBScript

In my opinion, the best way to teach is by example. I'll begin with a utility program that is similar to a program that was popular when I first started working at Microsoft. It pays homage to the association between Active Directory and a more traditional directory—a phone book.

This sample simply takes a last name, searches the directory for people with the same last name, and returns the full name and telephone number of the people found. For simplicity, this version is written in VBScript and is included on the companion CD. A version written in Visual Basic is also included on the companion CD.

Phone Sample

Listing 5-1 shows code from the Phone.vbs sample. Let's look at the entire program and then break it down.

 ` PHONE - Display telephone number for a specified last name
'
' Check to see if there is a command-line argument
Set objArguments = WScript.Arguments
 If (objArguments.Count = 1) Then
     ` Treat the command-line argument as the name to filter on
    strPerson = objArguments(0)
 Else
    ` Check to see if script is running in console
    strExecutable = LCase(WScript.Fullname)
     If InStr(strExecutable, "cscript") > 0 Then
         ` Prompt user for name
        WScript.StdOut.Write "Name to lookup (enter * for all):"
         ` Use Standard in to get name
        strPerson = WScript.Stdin.ReadLine
    Else
        ` GUI mode, use InputBox to prompt user
        strPerson = InputBox( _
            "Enter the last name of the person to lookup" & vbCrLf & _
            "(Use * to search for all people)", _
            "Lookup Telephone number")
    End If
 End If
 If strPerson <> "" Then
' Input box is not empty and Cancel button was not clicked     ` Build the query string
    ` Active Directory OLEDB Provider format has four parts separated
    ` by semi-colons:
    ` Root: which is the starting point for the search
    ` Filter: conditions to search on, using RFC 2254 format
    ` Attributes: attributes to return
    ` Scope: base, onelevel, or subtree for entire directory partition
     `  Specify the search base.
    `  We'll use the global catalog for performance reasons since the
    `  Name and Telephone number attributes are available from the GC
     ` First, need to discover the local global catalog server
    Set objADsRootDSE = GetObject("GC://RootDSE")
     ` Form an ADsPath string to the DN of the root of the 
    ` Active Directory forest
    strADsPath = "GC://" & _
        objADsRootDSE.Get("rootDomainNamingContext")
     ` Wrap the ADsPath with angle brackets to form the base string
    strBase = "<" & strADsPath & ">"
     ` Release the ADSI object, no longer needed
    Set objADsRootDSE = Nothing
     ` Specify the LDAP filter
    ` First, indicate the category of objects to be searched 
    ` (all people, not just users)
    strObjects = "(objectCategory=person)"
     ` If user enters "*", then filter on all people
    If (strPerson = "*") Then
        strName = "(sn=*)"
    Else
        strName = "(sn=" & strPerson & "*)"
    End If
     ` Add the two filters together
    strFilter = "(&" & strObjects & strName & ")"
     ` Set the attributes we want the recordset to contain
    ` We're interested in the common name and telephone number
    strAttributes = "cn,telephoneNumber"
     ` Specify the scope (base, onelevel, subtree)
    strScope = "subtree"     ` Create ADO connection using the ADSI OLE DB provider
    Set objADOConnection = CreateObject("ADODB.Connection")
    objADOConnection.Open "Provider=ADsDSOObject;"
     ` Create ADO commmand object and associate it with the connection
    Set objADOCommand = CreateObject("ADODB.Command")
    objADOCommand.ActiveConnection = objADOConnection
     ` Create the command string using the four parts
    objADOCommand.CommandText = strBase & ";" & strFilter & ";" & _
        strAttributes & ";" & strScope
     ` Set the number of records in the recordset logical page
    objADOCommand.Properties("Page Size") = 20
     ` Set the maximum result size
    objADOCommand.Properties("Size Limit") = 20
     ` Sort the results based on the cn attribute
    objADOCommand.Properties("Sort On") = "cn"
     ` Execute the query for the user in the directory
    Set objADORecordset = objADOCommand.Execute
     If objADORecordset.EOF Then
        WScript.Echo "No records were found."
    Else
        ` Loop through all the returned records
        While Not objADORecordset.EOF
             ` Display the row using the selected fields
            strDisplayLine = objADORecordset.Fields("cn") & vbTab
             ` Check to see if telephone number field is null
            If IsNull(objADORecordset.Fields("telephoneNumber")) Then
                strDisplayLine = strDisplayLine & "(number not listed)"
            Else
                ` Retrieve telephone number and add to line
                strDisplayLine = strDisplayLine & _
                    objADORecordset.Fields("telephoneNumber")
            End If
             ` Display the line
            WScript.Echo strDisplayLine
             ` Advance to the next record
            objADORecordset.MoveNext         Wend
    End If
     ` Close the ADO connection
    objADOConnection.Close
End If 

Listing 5-1 Phone.vbs uses ADO to search Active Directory.

You can run this sample directly from the command prompt (Cmd.exe) by typing cscript phone.vbs. Figure 5-2 shows an example of using Phone.vbs from the command prompt.

CScript is the command prompt version of Windows Script Host. Scripts executed by CScript will display their output using the command prompt window. If you include the /nologo command-line option, CScript suppresses version and copyright information. WScript is the Windows-based version of Windows Script Host and will display output using dialog boxes. If you run Phone.vbs using WScript, each WScript.Echo statement will generate a dialog box.

Figure 5-2 Using Phone.vbs from the command prompt.

If you just run the Phone.vbs file, it will prompt you for a name using the dialog box shown in Figure 5-3. Results are displayed, one at a time, as shown in Figure 5-4.

Figure 5-3 Phone.vbs prompting for input.

Figure 5-4 Phone.vbs displays each result in a dialog box.

In the following sections I'll break down this sample and explain how it connects to Active Directory and searches for information.

Gathering Input

Before getting into the details of ADO, I want to spend a moment showing off a feature of Windows Script Host. Batch files have always had the ability to examine command-line arguments, and Windows Script Host provides similar features using the WScript.Arguments object.

The first section of the Phone sample checks to see whether the user passed a name to search for on the command-line. Here's the code:

 ` Check to see if there is a command-line argument
Set objArguments = WScript.Arguments
 If (objArguments.Count = 1 ) Then
     ` Treat the command-line argument as the name to filter on
    strPerson = objArguments(0) 

The Arguments object, stored in the objArguments variable, is a collection of all the command-line arguments that the user enters. The Count property of the Arguments object indicates the number of arguments. If Count equals 1, indicating that one argument was entered after the program name on the command-line, the argument value is copied to the strPerson variable. Note that the Count property is 1-based (1, 2, 3, ...), whereas the index of the Arguments object is 0-based (0, 1, 2, ...).

If no argument was entered on the command-line, the sample proceeds to gather the name to be searched for by presenting a prompt to the user. If the sample is run from the Windows user interface, a dialog box is displayed (refer back to Figure 5-3) using the InputBox function of VBScript. While this is convenient, the InputBox function always uses a dialog box even if the sample is launched from the command line. However, you can detect the environment the sample is running under and adapt to that environment. Here's the code that does this.

With Windows Script Host, some objects and functions are provided by the VBScript or JScript engines while others are provided by Windows Script Host itself. For example, WSH provides the Arguments collection whereas VBScript provides the InputBox function. InputBox is not available with the JScript engine.

 Else
    ` Check to if script is running in console
    strExecutable = LCase(WScript.Fullname)
     If InStr(strExecutable, "cscript") > 0 Then
         ` Prompt user for name
        WScript.StdOut.Write "Name to lookup (enter * for all):"
         ` Use Standard in to get name
        strPerson = WScript.StdIn.ReadLine
    Else
        ` GUI mode, use InputBox to prompt user
         strPerson = InputBox( _
            "Enter the last name of the person to lookup" & vbCrLf & _
            "(Use * to search for all people)", _
            "Lookup Telephone number")
    End If
 End If 

By checking to see whether the host is CScript.exe, you can then use the standard in (StdIn) and standard out (StdOut) streams for interaction with the user. Streams are used to redirect input and output to and from a program. Streams only work under the CScript version of Windows Script Host. If you attempt to use the StdIn or StdOut streams from WScript, Windows Script Host will report an invalid handle error. The StdOut stream in WSH displays output to the command prompt window. The command WScript.StdOut.Write "Name to lookup (enter * for all):" is similar to the MS-DOS batch file command ECHO Name to lookup (enter * for all):, both of which display text in the command prompt window. The statement WScript.StdOut.WriteLine will display text and append a new line character as well. Similarly, by using WScript.StdIn.ReadLine, you can get input from the command window without using a Windows interface.

The Windows-based version of Windows Script Host is sufficient for most uses, however, I wanted to highlight the ability to truly customize your program to the environment it's running under.

---

Using Redirection with Streams

CScript supports the StdIn, StdOut, and StdErr streams. StdErr is the stream used to display error messages generated by the program being run. All the streams can be redirected from the command line using special characters. Some examples:

CScript Phone.vbs >Output.txt	Sends the output of Phone.vbs into the Output.txt file
CScript Phone.vbs >>Output.txt	Appends data to Output.txt
CScript Phone.vbs < Input.txt	Redirects input from the command prompt window and uses the text in Input.txt instead
CScript Phone.vbs 2>Errors.txt	Redirects error messages from the screen to the Errors.txt file
CScript Phone.vbs >Output.txt 2>&1	Redirects all output, including error messages, to the Output.txt file

---

Query Statement

The next portion of the sample creates the various parts of the query statement that will be submitted to the server. The query statement is a string that tells the server exactly what you are looking for, where to start looking for it, and what to return when it finds a match.

Given that we're using an LDAP-based directory technology combined with SQL-based data access technology, we have a choice of dialects to use when creating the query statement: LDAP or SQL.

As you would expect, the LDAP dialect is optimized toward directory data. The following is an example of a query that uses the LDAP dialect:

 <LDAP://DC=coppersoftware,DC=com>;
(&(objectClass=user)(CN=Bob*));
ADsPath;
subTree 

This string consists of four parts: the search base, the LDAP search filter, the attributes to return, and the scope of the search. I'll discuss each of these parts in detail in the following sections. The query statement above requests that the LDAP server for the coppersoftware.com domain examine all the user class objects and check to see whether the cn (Common-Name) attribute of the object starts with "Bob". It then returns the ADsPath attribute of all the objects meeting those criteria.

The SQL dialect, on the other hand, should look familiar to you if you've done any SQL programming. The following SQL expression is equivalent to the previous LDAP dialect query:

 SELECT ADsPath
    FROM `LDAP://DC=coppersoftware,DC=com'
    WHERE objectClass=`user' AND CN=`Bob*'
    ORDER BY sn 

Again, this string asks the server for all the user objects that begin with "Bob". Using the SQL dialect, you can also specify how you want the results sorted. In this case, I used the ORDER BY clause to indicate that the results should be sorted by the sn (Surname) attribute. While you cannot specify a sort order directly in an LDAP dialect query string, there are other ways to get a sorted result, which I'll describe later in the section "Sorting."

Which dialect should you use? If you are using ADO or OLE DB, you can use either SQL or LDAP query statements. Just use whichever you prefer. If you are experienced with SQL queries, you might find it more convenient than the rigid LDAP search filter syntax.

Since we're talking about Active Directory, and many fine books and references already exist on SQL, I'll concentrate on the LDAP dialect for query statements in this book. However, I encourage you to use SQL if you prefer to. If you want to learn more about accessing data using SQL and ADO, Microsoft Press has a number of excellent books on Microsoft SQL Server, ADO, and database programming, including Microsoft SQL Server 2000 Programming Step by Step, by Rebecca Riordan, and Programming ADO, by David Sceppa.

If you are using IDirectorySearch or the LDAP ldap_search function directly, your only choice is to use the LDAP dialect queries.

Search Base

The first part of the LDAP query string is known as the search base. This ADsPath string specifies the starting point of the search. You can use whatever path you want, although it generally makes sense to reference a container object. For example, to search a particular organizational unit, you could specify the following search base:

 LDAP://OU=users,DC=coppersoftware,DC=com 

This statement would begin a search at the Users organizational unit and search only within that container. Other containers that are siblings of the Users container would not be searched.

More often, you want to search the entire directory, exclusive of the schema and configuration partitions. To do this, you should specify the root object of the directory partition. As I discussed in Chapter 4, various directories use different objects for the root object. For example, the University of Michigan's LDAP server uses the following distinguished name for the root:

 O=University of Michigan,C=us 

Normally, an Active Directory root object is the domain object and is expressed as follows:

 DC=coppersoftware,DC=com 

You don't need to guess the actual values, however; the RootDSE object provides this information in the defaultNamingContext and rootDomainNaming-Context attributes. The former will provide the distinguished name of the current domain directory partition, whereas the latter will provide the distinguished name of the forest domain directory partition.

Always try to use the RootDSE object to avoid hard coding ADsPaths and server names into your applications.

Because the Phone sample merely looks up phone numbers of users—and the phoneNumber attribute is a member of the partial attribute set stored in the global catalog—the sample instructs ADSI to connect to the global catalog rather than any domain controller. Doing so ensures that all the objects in the forest are searched in the most efficient manner.

 ` First, need to discover the local global catalog server
Set objADsRootDSE = GetObject("GC://RootDSE") 

Then the sample retrieves the rootDomainNamingContext property of the RootDSE object, which is the distinguished name of the directory partition for the entire forest, not just the current domain.

 ` Form an ADsPath string to the DN of the root of the Active Directory forest
strADsPath = "GC://" & objADsRootDSE.Get("rootDomainNamingContext") 

At this point in the sample, the variable strADsPath contains the root ADsPath that is used as the basis of our search. This ADsPath will be the actual root object of Active Directory, known as the domain object.

In the LDAP API, the search base is a distinguished name string. However, when using the ADSI OLE DB provider, you actually provide an ADsPath, not a distinguished name. Remember that an ADsPath string contains the ADSI provider name, GC: in this case, and includes a distinguished name.

To indicate to ADO that a moniker or URL (which is what an ADsPath mimics) is being passed, I wrap the ADsPath with angle brackets.

 ` Wrap the ADsPath with angle brackets to form the base string
strBase = "<" & strADsPath & ">" 

There's no need to stay bound to the RootDSE object anymore, so I explicitly release the object by setting the object to the VBScript keyword Nothing. This removes the object from memory.

 ` Release the ADSI object, no longer needed
Set objADsRootDSE = Nothing 

You don't need to do this for the other objects because objects are destroyed when they go out of scope. However, if you use an object early in a program and no longer need it, it's good practice to release it.

For programmers using C or C++, keep in mind that Visual Basic and the scripting languages do a lot of cleanup behind the scenes that must be performed manually in a C or C++ application. When working with COM objects in C and C++, always call the object's Release method to instruct COM to unload the object when you are finished with it.

Search Filter

The LDAP search filter is the most complex portion of the query statement. In this part you specify the exact criteria for the search. You can use a broad range of criteria to perform extremely flexible searches. While not as readable as an equivalent SQL query, the LDAP search filter syntax is just as powerful.

This section covers the basic syntax for LDAP search filters. For more information, check out the definition in RFC 2254.

The LDAP search filter contains one or more strings grouped together by parentheses. Basically, the syntax goes like this:

 ((<filtercomp1>)(<filtercomp2>) ... (<filtercompn>)) 

The <filtercomp> string is the combination of one or more <filter> strings and possibly a Boolean operator. The syntax for <filter> looks like this:

 <filter1>
&(<filter1>)(<filter2>) ... (<filtern>)
|(<filter1>)(<filter2>) ... (<filtern>)
!(<filter1>) 

Table 5-1 lists the Boolean operators.

Boolean Operator	Description
&	AND
|	OR
!	NOT

Table 5-1 Boolean operators available for LDAP search filters.

The <filter> string has the following syntax:

 <attribute><comparisonoperator><value> 

In this syntax, <attribute> is any attribute that might be found in the directory and <value> is the actual value to search for. Remember that you are searching for LDAP attributes, not ADSI properties. While these often have the same name, an attribute is the label of the data found in the directory, while a property is the label of an ADSI interface member. Keep in mind that if you misspell an attribute, the server cannot distinguish between it and an attribute that simply wasn't found. The search will be conducted, but it won't return any matches and no error will be generated.

Table 5-2 lists the comparison operators for <comparisonoperator>. Note that while the greater than or equal to (>=) and less than or equal to (<=) comparison operators are present, you cannot use greater than (>) or less than (<).

Comparison Operator	Description
=	Equal to.
~=	Approximately equal to. Active Directory ignores this operator and treats it the same as the Equal to (=) operator.
>=	Greater than or equal to.
<=	Less than or equal to.

Table 5-2 Comparison operators available for LDAP search filters.

The LDAP search filter syntax allows for wildcard matching using the present operator (=*) and the any operator (*). Filters that use these operators have the following syntax:

 <attribute>=*
<attribute>=<value>*
<attribute>=<value>*<value>
<attribute>=<value>*<value>* ... 

Search filter examples  Table 5-3 shows examples of search filter strings.

Sample Filter	Description
(objectClass=*)	Retrieves all objects. Since every object in Active Directory has an objectClass attribute, using the present (=*) operator will match all objects. This is the easiest way to find all the objects within a particular search scope.
(cn=Charles Oppermann)	Returns the object or objects that have a cn (Common-Name) attribute equal to "Charles Oppermann."
(!(cn=John Beach))	Returns all the objects that are not named "John Beach".
(sn=Oppe*)	Using the any operator (*) for wildcard matching within a string, returns all objects whose sn (Surname) attribute starts with "Oppe". This would match "Opperman," "Oppermann," and "Oppenheimer." I like this because my last name is always misspelled!
(&(objectClass=contact) (|(givenName=Bob*)(givenName=Robert*)))	Combines two Boolean operators, AND and OR. Returns all the contact objects that havea first name of "Bob" or "Robert".
(&(objectCategory=person) (!telephoneNumber =*))	Combines AND and NOT, and returns all the objects representing people who do not have a telephone number recorded.
(showInAdvancedViewOnly=TRUE)	Uses a Boolean value to find all the objects that have the showInAdvancedViewOnly attribute set to TRUE.

Table 5-3 Example LDAP search filter strings.

Special characters  Sometimes you need to use special characters in your search, characters that are part of the syntax. For example, the following search filter string contains parentheses around the 800. If a search filter string needs to use any special characters, you might get a run-time error in your code or the search might not work as expected:

 (telephoneNumber=[phone]) 

The characters shown in the table below are considered special characters in the LDAP search filter string.

Character	Hex Value
*	2A
(	28
)	29
\	5C
NUL	00

To use a special character, put a backslash (\) before it and then substitute the two-digit hex value. Using this method, the previous query would be appear as:

 (telephoneNumber=\28800\29555-1212) 

This method will work for any character, including nonprinting characters such as tab (\09) and linefeed (\0A). Whether the hex digits are uppercase or lowercase does not matter: \0a and \0A are equivalent.

Getting back to the Phone sample, I want to search all the objects in the directory that match the given last name, or surname. However, if there is someone named Bob Printer working for me, and I type Printer as the name to search for, I don't want all the printQueue objects to be returned. So in the first comparison of the filter I specify that I want to search only for objects that represent people. The objectCategory attribute in Active Directory provides a convenient way to specify this.

 ` Specify the LDAP filter.
` First, indicate the category of objects to be searched 
` (all people, not just users)
strObjects = "(objectCategory=person)" 

Then I add a filter for the surname. If the user chooses to search for all people by entering an asterisk (*), I use the present operator (=*), otherwise I create a filter for the specified name. People without a surname would not be returned.

 ` If the user enters "*", then filter on all people
If (strPerson = "*") Then
    strName = "(sn=*)"
Else
    strName = "(sn=" & strPerson & "*)"
End If 

Finally, I combine the two filters into one expression using the AND operator (&).

 ` Add the two filters together
strFilter = "(&" & strObjects & strName & ")" 

What this results in is a complete LDAP search filter string. If a user types Doe at the command line or when prompted, the variable strFilter will contain the following:

 (&(objectCategory=person)(sn=Doe*)) 

---

The LDAP Search Filter

The following is the exact LDAP search filter definition from RFC 2254. It uses the Augmented Backus-Naur Form (ABNF) notation specified in RFC 2234.

 Filter ::= CHOICE {
    and                [0] SET OF Filter,
    or                 [1] SET OF Filter,
    not                [2] Filter,
    equalityMatch      [3] AttributeValueAssertion,
    substrings         [4] SubstringFilter,
    greaterOrEqual     [5] AttributeValueAssertion,
    lessOrEqual        [6] AttributeValueAssertion,
    present            [7] AttributeDescription,
    approxMatch        [8] AttributeValueAssertion,
    extensibleMatch    [9] MatchingRuleAssertion
}
SubstringFilter ::= SEQUENCE {
    type    AttributeDescription,
    SEQUENCE OF CHOICE {
        initial        [0] LDAPString,
        any            [1] LDAPString,
        final          [2] LDAPString
    }
}
AttributeValueAssertion ::= SEQUENCE {
    attributeDesc   AttributeDescription,
    attributeValue  AttributeValue
}
MatchingRuleAssertion ::= SEQUENCE {
    matchingRule    [1] MatchingRuleID OPTIONAL,
    type            [2] AttributeDescription OPTIONAL,
    matchValue      [3] AssertionValue,
    dnAttributes    [4] BOOLEAN DEFAULT FALSE
}
AttributeDescription ::= LDAPString
AttributeValue ::= OCTET STRING
MatchingRuleID ::= LDAPString
AssertionValue ::= OCTET STRING
LDAPString ::= OCTET STRING 

The following ABNF notation is the exact string representation of the LDAP search filter definition from RFC 2254.

 filter     = "(" filtercomp ")"
filtercomp = and / or / not / item
and        = "&" filterlist
or         = "|" filterlist
not        = "!" filter
filterlist = 1*filter
item       = simple / present / substring / extensible
simple     = attr filtertype value
filtertype = equal / approx / greater / less
equal      = "="
approx     = "~="
greater    = ">="
less       = "<="
extensible = attr [":dn"] [":" matchingrule] ":=" value
             / [":dn"] ":" matchingrule ":=" value
present    = attr "=*"
substring  = attr "=" [initial] any [final]
initial    = value
any        = "*" *(value "*")
final      = value
attr       = AttributeDescription from Section 4.1.5 of RFC 2251
matchingrule = MatchingRuleId from Section 4.1.9 of RFC 2251
value      = AttributeValue from Section 4.1.6 of RFC 2251 

---

Attributes to Return

The next part of the LDAP query statement specifies which attributes to return for the objects that match the search filter. Using a single asterisk (*) character, you can instruct the directory to return all the attributes of the matching objects. However, this is inefficient and wastes server and network resources. Avoiding this inefficiency is particularly important when you're working with the global catalog server because it contains only a partial set of the full attributes associated with an object. In the Phone sample, all that's required is the name of the person and their phone number.

 ` Set the attributes for the recordset to contain
` We're interested in the common name and telephone number
strAttributes = "cn,telephoneNumber" 

The attributes are separated with commas. While it's not required for the Phone sample, if you are planning to modify any of the objects that are returned, you should specify that the ADsPath property be returned so you can easily bind to the object using ADSI and make any changes.

I've mentioned several times the differences between attributes of a directory entry and the properties of an interface. I might appear to be contradicting myself above when I say that you should ask for the ADsPath property in the list of attributes to return. The ADsPath property is not an attribute for any object in Active Directory, so how can you ask for it to be returned by the directory? The code that implements the ADSI OLE DB provider and the IDirectorySearch interface makes an exception for ADsPath and creates the value for you when the object is returned. This is only true of the ADsPath property of the IADs interface. Other properties will not be recognized and the search will report errors.

Search Scope

The last piece of the LDAP query statement is also the simplest. It specifies the scope, or the depth, of the search. Where the search base specified the starting point, the scope indicates how deep in the hierarchy the server should go when looking for objects that match the search filter. Search scope has three possible values: base, onelevel, or subtree.

The default value is subtree. It indicates that the server should search all the objects starting with the one specified in the search base. If any container objects are found, the server will also search all the objects in each container.

The onelevel value specifies that the server search only the children of the base object, but not the base object itself. This option is useful when you want to exclude a container from matching the filter criteria. Likewise, the base value specifies that the server will search only the object referenced by the search base. At most, only one object would be returned—the base object—if it matches the search filter criteria. This option is often used to verify the existence of an object.

The Phone sample, like most cases, needs to search the base object and all its containers, so it specifies the subtree option.

 ` Specify the scope (base, onelevel, subtree)
strScope = "subtree" 

Note that the search scope portion of the query string is the only optional portion. If it is not provided, the ADSI OLE DB provider will default to the subtree option. I believe it's good coding practice to specify parameters, even default ones, because it makes your code clearer.

Using ADO

Now let's dig into ADO and treat Active Directory as a database. ADO defines several objects, as shown in Figure 5-5.

Figure 5-5 ADO object model.

The four main objects (Connection, Command, Recordset, and Record) control a search and contain the results. Each object contains a collection of other objects to hold options and various pieces of returned data.

Creating an ADO Connection

To begin a search using ADO, the Phone sample must first create an instance of the ADO Connection object that represents a unique session with the Active Directory server. I use the VBScript CreateObject function to create the Connection object.

 ` Create ADO connection using the ADSI OLE DB provider
Set objADOConnection = CreateObject("ADODB.Connection")
objADOConnection.Open "Provider=ADsDSOObject;" 

JScript does not have a CreateObject function. If you prefer developing with JScript, you can create a new instance of any Automation object by using the ActiveXObject object supplied by the JScript engine. For example adoConnection = new ActiveXObject("ADODB.Connection");

The Open method of the Connection object, referenced in our application with the variable objADOConnection, indicates which OLE DB provider should be used. A string is passed to the Open method that specifies the ADsDSOObject provider, which is, again, the programmatic name of the ADSI OLE DB provider that is used to communicate with Active Directory. The Open method can also accept alternative credentials, which I describe later in the section "Search Options."

Next the sample creates a new, empty ADO Command object that will contain the specific search options to be used. The Connection object is then associated with the Command object that will be used to carry out the search.

 ` Create ADO commmand object and associate with the connection
Set objADOCommand = CreateObject("ADODB.Command")
objADOCommand.ActiveConnection = objADOConnection 

Specifying the Query

Now we are ready to use the LDAP query statement I created earlier. I purposely kept the four query statement sections separate and in their own string variables in order to better explain how to use them. Now it's time to "string" them together.

The ADO Command object has a CommandText property to hold the string to be passed to the data source provider. The ADSI OLE DB provider defines the format of the CommandText string. It requires that the four portions be separated with semi-colons.

 ` Create the command string using the four parts
objADOCommand.CommandText = strBase & ";" & strFilter & ";" & _
    strAttributes & ";" & strScope 

Just to briefly review, the four portions of the LDAP query statement are:

• Root  The ADsPath of the object, usually a container, where a program should start searching
• Filter  Matching criteria expressed using the LDAP search filter syntax
• Attributes  A comma-separated list of attributes to be returned for matching objects
• Scope  A string with a base, onelevel, or subtree value that specifies the depth of the search (optional)

Refining the Search

The CommandText property specifies four options in one string. There are also additional search options that can be specified. One that's common is the Page Size property. The Page Size property is a member of the IDirectorySearch interface and is exposed by the ADSI OLE DB provider as a custom ADO property. To reference it, you must use the Properties collection of the ADO Command object.

 ` Set the number of records in the recordset logical page
objADOCommand.Properties("Page Size") = 20 

A feature of the Phone sample is that if an asterisk is specified, the search filter contains (sn=*). This instructs the server to return the name and phone number of all directory objects that represent people. Now imagine what happens if you do that against a directory with 100,000 names or more?

For starters, it might take a while for the server to go through the entire directory, collecting the attributes you've requested. While the server processes this query, the user is left waiting for a response. Obviously, it's a huge burden on the server as well. The server processing the search must go through all the objects, building the result set in memory with the requested attributes to return. At the same time, it might be processing requests from other clients.

Databases use the concept of pages to allow the server and client to work in chunks. By setting a page size of 20, the server pauses after finding 20 objects that match the search criteria and returns the results to the client. Using this setting makes the application more responsive because the client can display results before the search is fully completed, significantly reducing the workload on the server.

Active Directory by default imposes a 1000-record limit on searches that do not specify a page size. This is a common problem for programmers and IT professionals when starting out with Active Directory. A simple program or database tool uses a broad search that should return many hundreds of objects but only returns 1000. This 1000-result limit is an LDAP policy set on search servers and prevents inefficient or rouge clients from monopolizing the server and its processing resources.

You should use paging wherever possible to avoid hitting the limit. However, if using a database tool, particularly one that understands SQL queries, there is currently no way to set the page size parameter for the ADSI OLE DB provider. In a future release of ADSI, there will be a provision to use the SQL stored procedure sp_addlinkedserver to set the page size.

If the number of returned records could be potentially large, you could use the Size Limit property. Size Limit specifies the maximum number of records returned. When I used Size Limit in conjunction with the Page Size property, it sometimes did not work as expected, so you might need to experiment with this property. The following code shows how to set Size Limit to 20.

 ` Set the maximum result size
objADOCommand.Properties("Size Limit") = 20 

Keeping the user's response time in mind, you can also set the Time Limit property. While I didn't use Time Limit in the Phone sample, it would be coded like this:

 ` Set the number of seconds for the server to spend searching
objADOCommand.Properties("Time Limit") = 5 

This statement instructs the server to search for 5 seconds and then return whatever results it gathers in that time. The user will have to wait only a brief period to be shown a set of matching results.

We're only covering some of the several options available while searching. Other options are discussed in the section "Search Options" later in this chapter.

The 1000-record limit is part of Active Directory's LDAP Policies, specifically the MaxPageSize policy. The maximum amount of time that Active Directory will spend searching, the MaxQueryDuration policy, is set at 120 seconds. The best way to change these limits is with the Ntdsutil command-line tool, which is included with Windows 2000 Server. Documentation for this utility is included with the Windows 2000 Server Resource Kit. Only domain administrators should use this tool. If you write efficient code, you will not have to worry about these default limits. Also, consider that some networks may be particularly busy and may have policies that are more restrictive than the defaults.

Sorting

The next line of code instructs Active Directory to sort the results for us based on the cn attribute:

 ` Sort the results based on the cn attribute
objADOCommand.Properties("Sort On") = "cn" 

You can add more attributes to sort by to the list, separating them with commas. Asking for sorted results is more work for the server, but if you sort on attributes marked as indexed attributes in the directory, the server can perform the sort as it is collecting the results; otherwise it must wait until all the results are available and then sort them. Note that if you specify to sort the results, the Page Size setting might be ignored.

Executing the Query

I've used a couple dozen lines of code preparing to search the directory. I have all the criteria and options set, and now it's time to use ADO to have Active Directory actually perform the search. Can I get a drum-roll please?

To start the search operation, I call the Execute method of the ADO Command object. The Execute method returns a reference to a Recordset object, which we assign to the variable objADORecordset. I'll discuss the Recordset object in more detail shortly.

 ` Execute the query for the user in the directory
Set objADORecordset = objADOCommand.Execute 

The Execute method passes the CommandText property along with the specified properties to the ADSI OLE DB provider. In turn, the provider calls IDirectorySearch, which eventually executes an LDAP search operation of the specified Active Directory server.

In a synchronous search (which is what we are performing now; I'll describe asynchronous searches later on), the Execute method returns to the application when the server has finished searching all the objects within the scope or when the number of results exceeds any specified limits, such as page size or size limit. At this point the Recordset object is ready to be examined.

Keep in mind, however, that if the search does not result in any matching records, the Recordset object is still valid. An empty result may not be intended, but it is not considered an error condition to the search engine.

Processing the Results

The ADO Recordset object contains an end of file (EOF) property that is True when there are no records or no more matching records. If the returned Recordset object is not empty, the next section of code starts a loop that will be executed once for each matching object. As long as the EOF property is not True, we have not reached the end of the results and have valid results in the Recordset object.

 If objADORecordset.EOF Then
    WScript.Echo "No records were found."
Else
    ` Loop through all the returned records
    While Not objADORecordset.EOF 

At this point in the sample, I've created and executed a query that searched Active Directory's global catalog. If any objects matching the search criteria are found, the code enters a loop to retrieve each of the returned attributes.

Rows and Record Sets

As mentioned earlier, a Recordset object represents the results of a search. Think of the data in a Recordset object as a table. Each object that matches the search criteria is represented as a row. The columns contain the individual pieces of returned data. The columns are stored in a collection of Field objects that contain the attributes of the directory objects.

To retrieve a particular attribute from the current Recordset object, you must access its Fields collection. You specify the field using the attribute name, as shown here:

         ` Display the row using the selected fields
        strDisplayLine = objADORecordSet.Fields("cn") & vbTab 

The goal is to produce a single string containing the name and telephone number of the current record and display that to the user. In the code above, I retrieve the cn attribute, append a tab character, and store it in a string variable. Using a tab will separate the name from the next column, which will be the telephone number.

Something to consider in your own applications is that even though in the Phone sample we requested that the telephoneNumber attribute be returned, it's entirely possible that a particular object does not have a telephoneNumber attribute (or whatever attribute you request). This isn't because the person is a technophobe; maybe they simply didn't want to list it, or you may not have permission to access it.

What this illustrates is the need for good error checking in your code. Take, for example, the following code:

 WScript.Echo objADORecordset.Fields("telephoneNumber") 

If this line of code is executed on a Recordset object that has an empty value for the telephoneNumber attribute, it would generate a Type Mismatch error because it will return NULL and the WScript.Echo method doesn't handle null data. Using the Visual Basic or VBScript function IsNull we can check for that case and supply an alternative string.

         ` Check to see if telephone number field is null
        If IsNull( objADORecordset.Fields("telephoneNumber") ) Then 
            strDisplayLine = strDisplayLine & "(number not listed)"
        Else
            ` Retrieve telephone number and add to line
            strDisplayLine = strDisplayLine & _
                objADORecordset.Fields("telephoneNumber")
        End If 

Finally, with the strDisplayLine variable containing the string I want to display, I call the Echo method of the WScript object. If the program is run from the command prompt, this call will display a single line of text. If the program is executed from the Windows user interface, the text will be displayed in a dialog box. (You saw this in Figure 5-4, shown earlier.)

         ` Display the line
        WScript.Echo strDisplayLine 

Now the sample moves to the next matched object using the MoveNext method of the Recordset object. Since the MoveNext method updates the EOF property, it's a convenient place to complete the loop.

         ` Advance to the next record
        objADORecordset.MoveNext
    Wend 

Cleaning Up

If you've moved past the last record, the EOF property will be set to True and the loop will terminate. Now it's time to clean up the application before exiting.

I call the Close method of the ADO Connection object to let ADO know that this search is finished and it can free any system resources, but it does not destroy the objects. ADO will also close the associated Recordset object.

 ` Close the ADO connection
objADOConnection.Close 

If you wanted to, you could start the application over again, reusing the Command object. However, we've finished with the Phone sample for now.

---

Ideas for Improvements

The simple Phone sample is ripe for improving. Among its limitations are that it can only search using the surname, or last name, of the person. Changing it to search on a variety of name and descriptive attributes would make it more useful. Here are some suggestions:

• Allow searching on first and last names
• Return all phone numbers for the person, including fax and home numbers
• Instead of returning telephone numbers, retrieve the user's e-mail address. Using the mailto: protocol, it's easy to invoke the user's default e-mail program to send a message to the address.
• Use the Telephony API to allow the computer's modem to dial the number.

---