(SQL Server) Outlook -- Search Multiple Folders

I don't yet see how it's possible to do a recursive search of Outlook folders using the Microsoft Graph API. My best guess is to somehow use OData v4.0's $expand query option, but the hierarchical structure of the "mailFolder" and "messages" Microsoft Graph resources don't quite fit. Suggestions are welcome and can be sent to support@chilkatsoft.com.

This example will iterate over a list of folder previously obtained by a recursive traversal of the Outlook mail folders. (See the link in the example code below.)

A separate search is performed on each desired folder, and the results are combined into a single result set.

Note: This example requires Chilkat v9.5.0.68 or greater.

This example applies to: Exchange Online | Office 365 | Hotmail.com | Live.com | MSN.com | Outlook.com | Passport.com

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use your previously obtained access token here:
    -- See the following examples for getting an access token:
    --    Get Microsoft Graph OAuth2 Access Token (Azure AD v2.0 Endpoint).
    --    Get Microsoft Graph OAuth2 Access Token (Azure AD Endpoint).
    --    Refresh Access Token (Azure AD v2.0 Endpoint).
    --    Refresh Access Token (Azure AD Endpoint).

    EXEC sp_OASetProperty @http, 'AuthToken', 'MICROSOFT_GRAPH_ACCESS_TOKEN'

    -- This example will iterate over the folders previously discovered by recursively traversing the Outlook folders
    -- as shown in this example:  Outlook Recursive Folder Traversal)

    -- The XML map produced by the recursive traversal looks like this:

    -- 	<?xml version="1.0" encoding="utf-8"?>
    -- 	<hashtable>
    -- 	<e><k>/Sent Items</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEJAAAA</v></e>
    -- 	<e><k>/Inbox</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA</v></e>
    -- 	<e><k>/Junk Email</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEiAAAA</v></e>
    -- 	<e><k>/Inbox/xyz</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwEAAAA=</v></e>
    -- 	<e><k>/Inbox/abc/subFolderA/a</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAAA=</v></e>
    -- 	<e><k>/Inbox/abc</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huv8AAAA=</v></e>
    -- 	<e><k>/Outbox</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgELAAAA</v></e>
    -- 	<e><k>/Inbox/abc/subFolderA</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwAAAQ==</v></e>
    -- 	<e><k>/Inbox/abc/subFolderB</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwMAAAA=</v></e>
    -- 	<e><k>/Archive</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAG8XunwAAAA=</v></e>
    -- 	<e><k>/Deleted Items</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEKAAAA</v></e>
    -- 	<e><k>/Drafts</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEPAAAA</v></e>
    -- 	</hashtable>

    -- We'll iterate over the folders, and search all folders beginning with "/Inbox" (effectively, we're recursively searching
    -- everything under Inbox)
    DECLARE @xmlMap int
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlMap OUT

    DECLARE @success int
    EXEC sp_OAMethod @xmlMap, 'LoadXmlFile', @success OUT, 'qa_data/outlook/folderMap.xml'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load XML folder map.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @xmlMap
        RETURN
      END

    -- We're going to return just the message id, subject, and FROM name/address.
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'select', 'id,subject,from'

    -- Our search will be for emails with the word "sample" in the subject.
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'filter', 'contains(subject,''sample'')'

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    DECLARE @jsonCombined int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonCombined OUT

    EXEC sp_OASetProperty @jsonCombined, 'EmitCompact', 0

    DECLARE @sbResponse int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT

    DECLARE @sbPath int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT

    DECLARE @numFolders int
    EXEC sp_OAGetProperty @xmlMap, 'NumChildren', @numFolders OUT
    DECLARE @i int
    SELECT @i = 0
    DECLARE @j int
    SELECT @j = 0
    DECLARE @k int
    SELECT @k = 0
    WHILE @i < @numFolders
      BEGIN
        EXEC sp_OASetProperty @xmlMap, 'I', @i
        EXEC sp_OAMethod @xmlMap, 'GetChildContent', @sTmp0 OUT, 'e[i]|k'
        EXEC sp_OAMethod @sbPath, 'SetString', @success OUT, @sTmp0

        EXEC sp_OAMethod @sbPath, 'StartsWith', @iTmp0 OUT, '/Inbox', 1
        IF @iTmp0 = 1
          BEGIN


            PRINT '------------------------------------------------------------------'

            EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
            PRINT 'Searching ' + @sTmp0

            -- Search this mail folder..
            EXEC sp_OAMethod @xmlMap, 'GetChildContent', @sTmp0 OUT, 'e[i]|v'
            EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'folder_id', @sTmp0

            EXEC sp_OAMethod @sbResponse, 'Clear', NULL
            EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/me/mailFolders/{$folder_id}/messages?$filter={$filter}&$select={$select}', @sbResponse
            EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
            IF (@success <> 1) and (@iTmp0 = 0)
              BEGIN
                EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
                PRINT @sTmp0
                EXEC @hr = sp_OADestroy @http
                EXEC @hr = sp_OADestroy @xmlMap
                EXEC @hr = sp_OADestroy @json
                EXEC @hr = sp_OADestroy @jsonCombined
                EXEC @hr = sp_OADestroy @sbResponse
                EXEC @hr = sp_OADestroy @sbPath
                RETURN
              END
            EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponse

            EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
            IF @iTmp0 <> 200
              BEGIN

                EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
                PRINT 'HTTP response status = ' + @iTmp0
                EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
                PRINT @sTmp0

                PRINT 'Failed.'
                EXEC @hr = sp_OADestroy @http
                EXEC @hr = sp_OADestroy @xmlMap
                EXEC @hr = sp_OADestroy @json
                EXEC @hr = sp_OADestroy @jsonCombined
                EXEC @hr = sp_OADestroy @sbResponse
                EXEC @hr = sp_OADestroy @sbPath
                RETURN
              END

            EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
            PRINT @sTmp0

            -- Each mail folder search will return JSON with a value array, which is non-empty if any matching messages were found:

            -- 			{
            -- 			  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA')/messages(id,subject,from)",
            -- 			  "value": [
            -- 				Individual messages, if any, are listed here.
            -- 			  ]
            -- 			}

            DECLARE @numMessages int
            EXEC sp_OAMethod @json, 'SizeOfArray', @numMessages OUT, 'value'
            SELECT @j = 0
            WHILE @j < @numMessages
              BEGIN
                EXEC sp_OASetProperty @json, 'J', @j

                -- Add this message to the final result set.
                EXEC sp_OASetProperty @jsonCombined, 'K', @k
                EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
                EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].folderPath', @sTmp0
                EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].id'
                EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].id', @sTmp0
                EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].subject'
                EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].subject', @sTmp0
                EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].from.emailAddress.name'
                EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].from.emailAddress.name', @sTmp0
                EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].from.emailAddress.address'
                EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].from.emailAddress.address', @sTmp0
                SELECT @k = @k + 1

                SELECT @j = @j + 1
              END

          END

        SELECT @i = @i + 1
      END

    -- Show the final combined JSON search result.

    PRINT '------------------------------------------------------------------'

    PRINT 'Combined Search Results:'
    EXEC sp_OAMethod @jsonCombined, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample output for the above program:

    -- ------------------------------------------------------------------
    -- Searching /Inbox
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA')/messages(id,subject,from)",
    --   "value": [
    --     {
    --       "@odata.etag": "W/\"CQAAABYAAADn68XtMop0TpsYJGpfKXY9AADOpwfr\"",
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA5_vF7TKKdE6bGCRqXyl2PQAAAM6Jj10AAAA=",
    --       "subject": "A sample email with Amazon in the body",
    --       "from": {
    --         "emailAddress": {
    --           "name": "Chilkat Software",
    --           "address": "support@chilkatsoft.com"
    --         }
    --       }
    --     }
    --   ]
    -- }
    -- 
    -- ------------------------------------------------------------------
    -- Searching /Inbox/xyz
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwEAAAA%3D')/messages(id,subject,from)",
    --   "value": [
    --   ]
    -- }
    -- 
    -- ------------------------------------------------------------------
    -- Searching /Inbox/abc/subFolderA/a
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAAA%3D')/messages(id,subject,from)",
    --   "value": [
    --     {
    --       "@odata.etag": "W/\"CQAAABYAAADn68XtMop0TpsYJGpfKXY9AADOpzfb\"",
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAADn68XtMop0TpsYJGpfKXY9AAAAzombFQAAAA==",
    --       "subject": "Sample email from admin@chilkat.io",
    --       "from": {
    --         "emailAddress": {
    --           "name": "Chilkat Software",
    --           "address": "admin@chilkat.io"
    --         }
    --       }
    --     }
    --   ]
    -- }
    -- 
    -- ------------------------------------------------------------------
    -- Searching /Inbox/abc
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huv8AAAA%3D')/messages(id,subject,from)",
    --   "value": [
    --   ]
    -- }
    -- 
    -- ------------------------------------------------------------------
    -- Searching /Inbox/abc/subFolderA
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwAAAQ%3D%3D')/messages(id,subject,from)",
    --   "value": [
    --   ]
    -- }
    -- 
    -- ------------------------------------------------------------------
    -- Searching /Inbox/abc/subFolderB
    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwMAAAA%3D')/messages(id,subject,from)",
    --   "value": [
    --   ]
    -- }
    -- 
    -- ------------------------------------------------------------------
    -- Combined Search Results:
    -- {
    --   "value": [
    --     {
    --       "folderPath": "/Inbox",
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA5_vF7TKKdE6bGCRqXyl2PQAAAM6Jj10AAAA=",
    --       "subject": "A sample email with Amazon in the body",
    --       "from": {
    --         "emailAddress": {
    --           "name": "Chilkat Software",
    --           "address": "support@chilkatsoft.com"
    --         }
    --       }
    --     },
    --     {
    --       "folderPath": "/Inbox/abc/subFolderA/a",
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAADn68XtMop0TpsYJGpfKXY9AAAAzombFQAAAA==",
    --       "subject": "Sample email from admin@chilkat.io",
    --       "from": {
    --         "emailAddress": {
    --           "name": "Chilkat Software",
    --           "address": "admin@chilkat.io"
    --         }
    --       }
    --     }
    --   ]
    -- }
    -- 

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @xmlMap
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @jsonCombined
    EXEC @hr = sp_OADestroy @sbResponse
    EXEC @hr = sp_OADestroy @sbPath


END
GO