- The ADO/OLE DB Conspiracy
- What Does ADO 2.5 Have to Do with Exchange 2000?
- The Role of the Web Storage System
- The Exchange OLE DB Provider
- File URLs
- HTTP URLs
- Programmatically Getting a User's HTTP Mailbox Folder URLs
- The Open Method of the ADO 2.5 Record Object
- Using Web Storage System SQL to Get a List of Folders
- Web Storage System SQL
- The SELECT Statement
- The ORDER BY Clause
- The RANK BY Clause
- Some Unsupported SQL
- Summary
Using Web Storage System SQL to Get a List of Folders
Web Storage System SQL (WSS SQL) is a SQL dialect that you can use to query items in the Web Storage System. It can't quite do everything that, say, T-SQL can do for SQL Server (see Web Storage System SQL in the Exchange 2000 SDK for a complete language reference), but its familiar syntax will ease programmers into the transition from querying a standard relational database to querying semistructured data.
A common task is to traverse a list of folders within a folder. In Exchange 2000, you do this by writing a WSS SQL query. In the example that follows, we want to retrieve all of the folders under our Exchange server's Public Folders. In our case, we want to retrieve everything under http://gomezawin2000.gomezamobile.local/public. Note that this is the MAPI client public store installed with Exchange 2000. This is the Public Folders hierarchy that users see when opening Outlook 2000. Since this folder hierarchy is there for backward compatibility with Exchange 5.5, we still have to write extra code to retrieve the contents of folders underneath the top-level folders. This means that the scope of our SQL query is limited to "shallow traversal," as the following code shows:
SELECT "DAV:displayname", "DAV:contentclass", "DAV:href"
FROM SCOPE('shallow traversal of
"http://gomezawin2000.gomezamobile.local/public"')
WHERE "DAV:ishidden" = False
AND "DAV:isfolder" = True
If we were querying another TLH or even a private store, we could specify "deep traversal" in the scope. With deep traversal, the specified folder is searched, and any subfolders found underneath are recursively searched.
In addition, we want to limit the DAV:isfolder property to True and the DAV:ishidden property to False in the WHERE clause. Otherwise we'll get all of the "hidden" MAPI values along with the folders. The sample Visual Basic code follows. It requires that you establish a valid ADO connection to your Exchange server and pass it in along with the folder hierarchy you wish to query. The function returns an ADO Recordset object to the calling routine:
Public Function GetFolderList(ByVal sURL As String, _
ByVal objConn As ADODB.Connection) As
ADODB.Recordset
' sURL should be a valid URL like http://gomezawin2000.gomezamobile.local/public
' The objConn is a valid connection to the above folder path.
Dim objADORS
As ADODB.Recordset
Dim sSQL As String
Set objADORS =
New ADODB.Recordset
sSQL = "select ""DAV:displayname"", ""DAV:contentclass"", ""DAV:href"" from"
sSQL = sSQL & " scope('shallow traversal of """ &
sURL & """')"
sSQL = sSQL & " where ""DAV:ishidden"" = False and ""DAV:isfolder"" = True"
objADORS.Open sSQL, objConn
Set GetFolderList = objADORS
End Function
The code that calls this routine will now have a standard ADO Recordset with each folder represented as a Record. Your code can now loop through the Recordset.
We know that was a lot of new information to digest, but it will all start to make sense as you learn how to write your own queries. Let's take a closer look at the special dialect of SQL that we need to speak in order to master manipulating Exchange items.