Accessing the Web Storage System Using ADO and ExOLEDB

The SELECT Statement

The starting point for any query is a well-formed SELECT statement. The SELECT statement returns the values of properties in a particular folder or folders within a store. It has the following general syntax:

SELECT <list of properties separated by commas or *>
FROM SCOPE (shallow traversal of <some folder URL>
            deep traversal of <some folder URL>)
WHERE <some search condition>
ORDER BY <list of properties separated by commas>

To select specific properties, you need to enclose each property name in quotation marks and separate each property name with commas:

SELECT "DAV:href", "DAV:displayname", "urn:schemas:contacts:fileas"

SELECT * and ADO's GetChildren Method

SELECT * is also valid syntax in a WSS SQL query. A SELECT * query returns all of the properties defined by the expected content class of the specified folder. However, just because you can do something doesn't mean you should. Although using SELECT * might be interesting when playing with WSS SQL to identify every value you can return, you should never use it in production code. It is both extremely time-consuming and unnecessarily broad, and it even makes the Exchange server perform unnecessary processing to get the Stream objects.

Another argument against using SELECT * is that unless you define your custom properties in a schema, you will not get them using the SELECT * query. As we'll see later, adding your own custom properties to an item is as easy as adding them to the item's Fields collection. However, since they are not defined specifically in the item's schema, SELECT * doesn't know anything about them. If you do specify the custom properties in the SELECT statement, they will be retrieved.

While we're on the subject, you may have noticed the GetChildren method of the ADO Record object. Its purpose is to return a Recordset whose rows represent the files and subdirectories in the directory represented by the Record. For example, let's assume that we have a valid ADO Record called objRec. If objRec.RecordType = adCollectionRecord, meaning that the record consists of a collection of records, we can use the code

Dim objRecordset As ADODB.Recordset
Set objRecordset = objRec.GetChildren

to get a Recordset consisting of all of the records in the collection. Unfortunately, this is the computational equivalent of the SELECT * query and should be avoided in production code.

Query Scope

Another component of a well-formed WSS SQL query is its scope. Instead of querying a table in a database, in WSS SQL you issue a query on a folder scope. A folder scope consists of two parts: a folder specified by a URL and a depth. We are already familiar with building URLs; the scope part is even simpler. A scope of deep traversal on a folder means that the folder and all of its subfolders will be searched. A scope of shallow traversal means that only the specified folder will be searched.

As we mentioned earlier, the Public Folders TLH, which is the MAPI client public folder store, is limited to shallow traversal (as are also, you may remember, such Exchange clients as Outlook 2000's All Public Folders). It is simply not implemented like the other public stores in the Web Storage System. Veteran collaborative application programmers will remember that they had to write some recursive code to get at all of the folders in the MAPI store. In Exchange 2000, this is still true for the Public Folders TLH only. Any other public folder store or private folder store allows deep traversals. For example, the following code finds all child folders contained in the particular folder. The query is limited to all visible folders contained in the specified folder; subfolders are not searched:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

On the other hand, this next query will include any and all subfolders:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('deep traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

In our previous examples we used the full URL path. The SCOPE operator can also accept relative URL paths because WSS SQL queries are executed on the basis of the context of an ADO connection. For example, let's pretend that you already have a connection open to the Administrator's root mailbox folder:

file://./backofficestorage/GomezaMobile.local/MBX/Administrator

The following query gets everything (including subfolders) under the Administrator's Contacts folder:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('deep traversal of "/Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

SCOPE Shorthand

A couple of syntax shortcuts are possible when you're working with SCOPE. Use of the SCOPE keyword in a SELECT statement is optional. If you do not use the SCOPE keyword (as in the query that follows), the search depth defaults to shallow:

SELECT "DAV:href" from
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

If you do use the SCOPE keyword but fail to specify shallow or deep traversal, the SELECT statement defaults to deep traversal, as in this query:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE _
('"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
          Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Then there is the sin of omission when dealing with the public folder tree designed for MAPI clients. As we have mentioned several times, a deep traversal on that tree results in an error. As you probably would expect, a query that defaults to a deep traversal (as in the preceding syntax) also generates an error.

You can specify more than one folder in your SCOPE clause as long as you separate each one with commas, as in the following example:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"', _
'shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Inbox"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

SCOPE Errors

When you search more than one folder, you must specify the same depth for each folder. You cannot mix and match deep and shallow searches in the same SELECT statement. If you do, you get the error shown in Figure 7.6.

Figure 7.6
Error resulting from mixing shallow and deep traversals in a single SELECT statement

While we're on the subject of things that can go wrong when you're working with SCOPE, remember how security works. If you try to execute a query against a folder to which you do not have access, you will get the error message "URL is outside of scope" (see Figure 7.7). The query uses the connection that you specify to do its work. So, for example, if you open a connection to a user's private mailbox and then run a query against a public folder store, you will get the same "URL is outside of scope" error.

Figure 7.7
Error resulting from querying against a folder that the connection can't access

If you decide to do something wrong, such as specifying deep traversal of the MAPI public folder store, you will get the error message "Specified resource was out-of-scope" (see Figure 7.8).

Figure 7.8
Error resulting from specifying deep traversal on a MAPI public folder store

A generic catchall error message for any syntax error in WSS SQL is "Submitted SQL statement was incomplete" (see Figure 7.9). As you may suspect, this is the most common error.

Figure 7.9
General WSS SQL syntax error

The WHERE Clause

We now come to the most diverse part of the SELECT statement: the WHERE clause. It is diverse because WHERE clauses can contain any number of logical conditions chained together by AND and OR keywords. What's more, the WHERE clause can have predicates (GROUP BY, LIKE, ORDER BY, and so on).

The Web Storage System uses tokens as values for search conditions in a WHERE clause. Tokens are surrounded by single quotation marks. They are the literal values on which you want to search. For example, the following query returns all contacts in the Administrator's Contacts folder that work in Massachusetts:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
   "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/Contacts"
WHERE ("urn:schemas:contacts:st" = 'MA')

Note that field names used in the WHERE clause are surrounded by double quotation marks, just as field names used in the SELECT clause are. So far, you would expect to specify a literal in SQL by using single quotation marks anyway. Now here's the catch. A token can contain only alphabetical characters unless it is surrounded by double quotation marks. For example, how would one get all contacts who were also vice presidents? Would the following code work?

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
   "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/Contacts"
WHERE ("urn:schemas:contacts:title" = 'Vice President')

As it turns out, this code would not work. Since the token contains a space, we need to surround it in double quotation marks first and then tack on the single quotation marks. The following query is syntactically correct and returns the desired results:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
   "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/Contacts"
WHERE ("urn:schemas:contacts:title" = '"Vice President"')

As we have seen in prior examples, a couple of values can go in the WHERE clause that do not need single or double quotation marks. These are the Boolean True and False values, as in the following query:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"', _
'shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Inbox"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Specifying a Date Range in a WHERE Clause

When you're specifying date values in the WHERE clause, the single quotation marks around the date value are all you need. For example, the following query is syntactically correct if you're asking for contacts in the Administrator's Contacts folder that were created on that person's birthday:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
    "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
             Contacts"
WHERE "DAV:creationdate" = '2000-07-28'

However (there always seems to be a however), dates are not stored in items in this format. They also contain the hour, minute, second, and millisecond of creation. So a more plausible WHERE clause would be the following:

WHERE "DAV:creationdate" = '2000-07-28 11:39:39.275'

However (again), you most likely will not know the exact fraction of a second when a particular item was created. A more practical way to guarantee that you get all contacts in the Administrator's Contacts folder that were created on that person's birthday would use the following code:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
    "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
             Contacts"
WHERE "DAV:creationdate" > '2000-07-27' AND "DAV:creationdate" 
    < '2000-07-29'

In this case we are looking for everything greater than the day before and less than the day after.

Embedded WSS SQL

As you may have gathered, WSS SQL suffers from the same problems that plague any embedded SQL code and have discouraged its use. The parsing and overcompensating for the quotation marks that are part of the syntax can become really cumbersome. To make sure that everything looks good to the query parser (in virtually every circumstance), we come up with some string beauties in our VB code, as in the following example, which gets everything from the currently logged-on user's Contacts folder:

  Dim objMailbox As CDO.IMailbox
  Dim objPerson As New CDO.Person
  Dim sSQL As String
 
  ' Get the currently logged-on user:
  ' UserName should be something like:
  ' CN=Administrator,CN=Users,DC=GomezaMobile,DC=local
  objPerson.DataSource.Open "LDAP://" & objInfo.UserName
  
  ' Get his or her mailbox:
  Set objMailbox = objPerson
  
  sSQL = "select ""DAV:href"", " & _
                 """urn:schemas:contacts:fileas"", " & _
                 """urn:schemas:contacts:email1""" & _
                 " from "
  sSQL = sSQL & """" & objMailbox.Contacts & """"

Here's what seems to be another interesting WSS SQL anomaly. Let's assume in the following VB code snippet that EX_DISPLAYNAME and EX_ISFOLDER are previously defined constants containing the schema field names and that RootURL is a valid URL that was passed into the hypothetical function as an argument. This is valid embedded WSS SQL syntax:

  sRootSQL = "SELECT " & """" & EX_DISPLAYNAME & """ " & _
             "FROM scope('shallow traversal of """ & RootURL & """') " & _
             "WHERE (" & """" & EX_ISFOLDER & """ = True) " & _
             "ORDER BY " & """" & EX_DISPLAYNAME & """"

No doubt some of the more experienced SQL/ADO programmers in the reading audience are cringing. Isn't this one of the reasons we have stored procedures and parameterized queries in SQL databases--to get rid of ugly embedded code in programs and help maintainability? Yes it is, but consider the fact that WSS SQL is in its infancy and embedded is all we have right now. So when developing production code, just be careful, use your best judgment, and test, test, test.

Another feature that makes the WHERE clause versatile is the fact that you can add what are called predicates to it. The next few sections discuss these in more detail.

The CAST Function

The CAST function is similar to cast functions in other languages in that it explicitly converts a field to another data type. In WSS SQL, you use CAST to attribute a data type to a property in the returned Recordset object. The Web Storage System recognizes all data types in the Exchange 2000 schema installed with the product. CAST is used mainly for working with custom properties because the Web Storage System might not know what their types are. Using CAST in a WSS SQL query is entirely optional. If you don't use it, your custom properties are evaluated as string values. Boolean properties don't need to be cast.

The basic syntax of the CAST function is as follows:

CAST ("<some value>" AS "<some datatype>")

"<Some value>" can be the name of a property or a literal string (both need to be surrounded by double quotation marks). Here's the catch: The CAST function supports only XML data types. Therefore "<some datatype>" needs to be replaced with one of the values from Table 7.8 (surrounded by double quotation marks, of course).

Some examples will help illustrate all of this. This first one casts a custom date property to a data time before comparing it:

WHERE CAST("custom:mydateprop" AS "datetime") > "2001-12-01T10:00"

The next example casts the string "0" as an integer to compare it with another integer:

WHERE "custom:count" > CAST("0" AS "int")

UUID properties are recognized with or without the braces, as in the next two examples:

   WHERE CAST ("myguid" AS "uuid") 
      = "F04DA480-65B9-11d1-A29F-00AA00C14882"
   WHERE CAST ("myguid" AS "uuid") 
      = "{F04DA480-65B9-11d1-A29F-00AA00C14882}"

All Microsoft Exchange 2000 binary properties are base 64 encoded by default because this is a format that can be sent in XML:

WHERE CAST ("custom:mybinaryprop" AS "bin.base64") > "a98k231KJg823kj"

Table 7.8  XML Property Data Types Supported by CAST

If you want to encode using hex, you must cast the property and the value against which you are comparing it:

WHERE CAST ("custom:mybinaryprop" AS "bin.hex") = _
CAST("08AF183B82" AS "bin.hex")

Enabling Full-Text Indexing

The next four predicates deal with character and pattern matching in WSS SQL. First we'll introduce each one, and then we'll explain how to use them in WSS SQL queries. Most of these predicates work only if full-text indexing is applied to the store being searched. To implement full-text indexing (Figure 7.10), open the Exchange System Manager, expand Servers, and then expand your Exchange server and right-click on the store to be indexed.

Figure 7.10
Implementing full-text indexing using the Exchange System Manager

You will be prompted for where to put the catalog. After you create the index, right-click on the store and choose Properties. You will see a new Full-Text Indexing tab similar to the one in Figure 7.11, where you can regulate how often the index is updated and when it should be rebuilt.

Figure 7.11
Full-text indexing maintenance using the Exchange System Manager

Even after doing this, you will get the error depicted in Figure 7.12 because the index has been enabled but not actually created.

Figure 7.12
A full-text index must be built before it can be used

To build the index, right-click on the store in the Exchange System Manager and select Start Full Population. You will be warned that it might take a long time depending on the size of your store. However, this option will build the complete index right away. You can check on the progress of the index by expanding the store in Exchange System Manager, clicking Full-Text Indexing, and inspecting the "last build time" property, as shown in Figure 7.13.

Figure 7.13
Checking on the Exchange System Manager's progress

For more information, search on "Full-Text Indexing" in the Exchange 2000 SDK.

The CONTAINS Predicate

The CONTAINS predicate allows you to search for a specific word or phrase in a predetermined order. Only whole words are matched, so you cannot use wild-card expressions here. The basic syntax is

CONTAINS ("<property name>", '<search specification>')

The property name is optional. If none is specified, then only the message or document body is searched. You can also use an asterisk (*) to include all properties marked for full-text indexing. The property name must be a string surrounded by double quotation marks, such as urn:schemas:mailheader:to. The asterisk does not have to be surrounded by the quotation marks.

The search specification is the string to search for, surrounded by single quotation marks and following the same rules as discussed earlier for tokens in a WHERE clause. You can use the AND and OR keywords and the FORMSOF predicate (discussed in the next subsection). The NOT operator is allowed only if it follows the AND keyword.

The FORMSOF Predicate

The FORMSOF predicate allows you a bit of leeway. It is used as part of a WHERE clause to find word variations, typically within a CONTAINS or FREETEXT predicate. The basic syntax is

FORMSOF (<type>, "<string>" [, <some other string>])

In the Web Storage System the only type supported is INFLECTIONAL. The string can be one or more words for which the search engine is to find variations. Each word must be delimited by double quotation marks and separated by commas. For example,

FORMSOF (INFLECTIONAL, "fight")

will find items containing "fighter," "fighters," "fighting," and so on.

When FORMSOF is used within a CONTAINS or FREETEXT predicate, the whole FORMSOF predicate is considered a token, so it must be surrounded by single quotation marks:

WHERE CONTAINS ("urn:schemas:contacts:hobbies", _
'FORMSOF (INFLECTIONAL, "photograph", "announce")')

In this case all contacts that listed "photography," "photographs," "photographer," "announcer," "announcing," and so on would be returned.

The FREETEXT Predicate

The FREETEXT predicate is used as part of a WHERE clause to loosely match any item having a variation of a word or a group of words. The basic syntax is

 FREETEXT ("<property name>" [, <some other property name>],
 '<search criteria>')

The property name is optional. If none is specified, then only the message or document body is searched. You can also use an asterisk (*) to include all properties marked for full-text indexing. The property name must be a string surrounded by double quotation marks, such as "urn:schemas:mailheader:to". The asterisk does not have to be surrounded by the quotation marks.

The search specification is the string to search for, surrounded by single quotation marks and following the same rules as discussed earlier for tokens in a WHERE clause. You can use the AND and OR keywords and the FORMSOF predicate (see the previous subsection). The NOT operator is allowed only if it follows the AND keyword.

The main difference is that FREETEXT allows you to find one or more words specified in the search criteria. For example,

WHERE FREETEXT ("urn:schemas:contacts:personaltitle", 'Mr Ms Dr')

pulls all contacts with "Mr," "Ms," or "Dr" in their name. When you're using the FREETEXT predicate, a group of words can match several items. For example, in the clause that follows, any item containing "garden," "gardening," "gardener," "gardens," "rose," "roses," and so on will be returned:

WHERE FREETEXT ("urn:schemas:contacts:hobbies", 'garden rose')

The LIKE Predicate

The LIKE predicate is used as part of the WHERE clause to perform character matches. LIKE does a character-by-character comparison of the specified property value and the supplied characters. It essentially finds characters that match a specified string. The basic syntax is

WHERE "<property name>" LIKE "<value>"

The property name must be surrounded by double quotation marks. The value needs to be surrounded by single quotation marks. The percent character (%) matches any or no characters adjacent to the specified string. For example, "%ar%" would match "Mary":

WHERE ("urn:schemas:contacts:spousecn" LIKE '%ar%')

The GROUP BY Predicate

The GROUP BY predicate organizes the returned Recordset object according to one or more of the properties specified in the SELECT statement. The basic syntax is

GROUP BY "<property name>" [, <some more property names>]

Basically, you can have any number of property names surrounded by double quotation marks and separated by commas. The only catch is that the property names have to be specified in the SELECT clause as well:

SELECT "DAV:href", "urn:schemas:contacts:fileas", _
       "urn:schemas:contacts:email1", _
       "urn:schemas:contacts:st"
  FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/ 
               Contacts"
  GROUP BY "urn:schemas:contacts:st"

Getting Item Counts with GROUP BY

As we'll see shortly, WSS SQL does not support COUNT. However, by including the DAV:visiblecount property in the SELECT statement, you can get a count of the items that you specify in the GROUP BY clause. The DAV:visiblecount property is a read-only property that returns the number of visible items in a Recordset object that are not folders. That is, items that have DAV:isfolder and DAV:ishidden set to True will not be counted.

For example, let's say that you want to count the number of low-importance, normal-importance, and high-importance messages in a user's inbox. Your query could go something like this:

SELECT "DAV:visiblecount", "urn:schemas:httpmail:importance", _
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
            Inbox"
GROUP BY "urn:schemas:httpmail:importance"

Let's assume that objRecordset is a new ADO Recordset, that sSQL contains the query string given in the preceding code, and that objConn is a valid connection to the Administrator's root mailbox folder:

objRecordset.Open sSQL, objConn

Now take the resulting Recordset and loop through it, checking for the urn:schemas:httpmail:importance field. Whenever you find it, consult the DAV:visiblecount property to get the total for that importance value. The code to do this would look something like this:

 Dim sImportance As String
 objRecordset.MoveFirst
 Do Until objRecordset.EOF
     Select Case objRecordset("urn:schemas:httpmail:importance")
         Case 2
             sImportance = "High"
         Case 1
             sImportance = "Normal"
         Case 0
             sImportance = "Low"
   End Select
   MsgBox objRecordset("DAV:visiblecount") & " " & sImportance _
      & " importance messages."
 Loop