Home > Articles > Data

  • Print
  • + Share This

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.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.