Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.1.0+

Gmail IMAP: Identify Messages and Threads Across Folders

See more GMail SMTP/IMAP/POP Examples

Sample INBOX and the discovered All Mail folder, fetch Gmail message and thread IDs, and deduplicate messages appearing in both. Each X-GM-MSGID maps to an X-GM-THRID; equal thread IDs group messages in a Gmail conversation.

All identifiers are strings. IMAP UIDs identify messages only within one mailbox and UIDVALIDITY. Gmail IDs identify messages across labels, while the RFC Message-ID header is a separate identifier. Gmail IMAP IDs are unsigned decimal values; Gmail REST uses the corresponding hexadecimal strings. Do not pass these decimal strings directly as REST IDs.

The example reads at most ten messages per folder. Its regular expression extracts only the requested numeric metadata from a single-line FETCH result, tolerating attribute order and unrelated notifications. It is not a general IMAP/MIME parser. A vanished message is skipped. No message body is downloaded or marked read.

Use the mailbox that authorized the full-mail scope https://mail.google.com/. Obtain the protocol OAuth token and refresh it before running this example. The token is loaded from gmail-mail-token.json in a private working directory; a narrow Gmail REST token is insufficient.

Google documentation

Chilkat SQL Server Downloads

SQL Server
-- 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
    DECLARE @iTmp1 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Assumes Chilkat is unlocked. See Global Unlock Sample.

    DECLARE @statusText int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @statusText OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @statusMatch int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @statusMatch OUT

    DECLARE @completed int

    DECLARE @token int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @token OUT

    EXEC sp_OAMethod @token, 'LoadFile', @success OUT, 'gmail-mail-token.json'
    IF @success <> 1
      BEGIN

        PRINT 'Could not load the protocol token from the private working directory.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'access_token', '', 1
    IF (@iTmp0 <> 1) or (@iTmp1 = 1)
      BEGIN

        PRINT 'Authorize or refresh the mailbox first.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    DECLARE @mailbox nvarchar(4000)
    SELECT @mailbox = 'user@example.com'
    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT

    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OASetProperty @imap, 'RequireSslCertVerify', 1
    EXEC sp_OASetProperty @imap, 'AuthMethod', 'XOAUTH2'
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.gmail.com'
    IF @success <> 1
      BEGIN

        PRINT 'Could not establish the Gmail IMAP TLS connection.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @imap, 'Login', @success OUT, @mailbox, @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'IMAP authentication failed. Check the mailbox, scope, expiry, and account policy.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    DECLARE @capabilities nvarchar(4000)
    EXEC sp_OAMethod @imap, 'Capability', @capabilities OUT
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    EXEC sp_OAMethod @imap, 'HasCapability', @iTmp1 OUT, 'X-GM-EXT-1', @capabilities
    IF (@iTmp0 <> 1) or (@iTmp1 <> 1)
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Gmail IMAP extensions are unavailable.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    DECLARE @folders int
    EXEC @hr = sp_OACreate 'Chilkat.Mailboxes', @folders OUT

    EXEC sp_OAMethod @imap, 'MbxList', @success OUT, 0, '', '*', @folders
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Could not list Gmail folders.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        RETURN
      END
    DECLARE @allMail nvarchar(4000)
    SELECT @allMail = ''
    DECLARE @folderMatches int
    SELECT @folderMatches = 0
    DECLARE @f int
    SELECT @f = 0
    EXEC sp_OAGetProperty @folders, 'Count', @iTmp0 OUT
    WHILE (@f < @iTmp0)
      BEGIN
        EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @f, '\All'
        EXEC sp_OAMethod @folders, 'IsSelectable', @iTmp1 OUT, @f
        IF (@iTmp0 = 1) and (@iTmp1 = 1)
          BEGIN
            EXEC sp_OAMethod @folders, 'GetName', @allMail OUT, @f
            SELECT @folderMatches = @folderMatches + 1
          END
        SELECT @f = @f + 1
      END
    IF @folderMatches <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Expected one selectable folder with the \All special-use attribute. Check label visibility.'
        EXEC @hr = sp_OADestroy @statusText
        EXEC @hr = sp_OADestroy @statusMatch
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        RETURN
      END
    DECLARE @mailboxNames int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @mailboxNames OUT

    EXEC sp_OAMethod @mailboxNames, 'Append', @success OUT, 'INBOX'
    EXEC sp_OAMethod @mailboxNames, 'Append', @success OUT, @allMail
    DECLARE @unique int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @unique OUT

    DECLARE @found int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @found OUT

    DECLARE @uidStrings int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @uidStrings OUT

    DECLARE @raw int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @raw OUT

    DECLARE @captures int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @captures OUT

    DECLARE @folderIndex int
    SELECT @folderIndex = 0
    EXEC sp_OAGetProperty @mailboxNames, 'Count', @iTmp0 OUT
    WHILE (@folderIndex < @iTmp0)
      BEGIN
        DECLARE @selected nvarchar(4000)
        EXEC sp_OAMethod @mailboxNames, 'StringAt', @selected OUT, @folderIndex
        EXEC sp_OAMethod @imap, 'ExamineMailbox', @success OUT, @selected
        IF @success <> 1
          BEGIN
            EXEC sp_OAMethod @imap, 'Logout', @success OUT
            EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

            PRINT 'Could not open the mailbox read-only.'
            EXEC @hr = sp_OADestroy @statusText
            EXEC @hr = sp_OADestroy @statusMatch
            EXEC @hr = sp_OADestroy @token
            EXEC @hr = sp_OADestroy @imap
            EXEC @hr = sp_OADestroy @folders
            EXEC @hr = sp_OADestroy @mailboxNames
            EXEC @hr = sp_OADestroy @unique
            EXEC @hr = sp_OADestroy @found
            EXEC @hr = sp_OADestroy @uidStrings
            EXEC @hr = sp_OADestroy @raw
            EXEC @hr = sp_OADestroy @captures
            RETURN
          END
        EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'ALL', 1, @found
        IF @success <> 1
          BEGIN
            EXEC sp_OAMethod @imap, 'Logout', @success OUT
            EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

            PRINT 'Could not obtain message UIDs.'
            EXEC @hr = sp_OADestroy @statusText
            EXEC @hr = sp_OADestroy @statusMatch
            EXEC @hr = sp_OADestroy @token
            EXEC @hr = sp_OADestroy @imap
            EXEC @hr = sp_OADestroy @folders
            EXEC @hr = sp_OADestroy @mailboxNames
            EXEC @hr = sp_OADestroy @unique
            EXEC @hr = sp_OADestroy @found
            EXEC @hr = sp_OADestroy @uidStrings
            EXEC @hr = sp_OADestroy @raw
            EXEC @hr = sp_OADestroy @captures
            RETURN
          END
        EXEC sp_OAMethod @uidStrings, 'Clear', NULL
        EXEC sp_OAGetProperty @found, 'Count', @iTmp0 OUT
        IF @iTmp0 > 0
          BEGIN
            EXEC sp_OAMethod @found, 'ToCommaSeparatedStr', @sTmp0 OUT
            EXEC sp_OAMethod @uidStrings, 'SplitAndAppend', @success OUT, @sTmp0, ',', 0, 0
          END
        --  Sample up to 10 messages from each mailbox; this is not a complete synchronization.
        DECLARE @i int
        SELECT @i = 0
        EXEC sp_OAGetProperty @uidStrings, 'Count', @iTmp0 OUT
        WHILE ((@i < @iTmp0) and (@i < 10))
          BEGIN
            DECLARE @uid nvarchar(4000)
            EXEC sp_OAMethod @uidStrings, 'StringAt', @uid OUT, @i
            DECLARE @joined1 int
            EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined1 OUT

            EXEC sp_OAMethod @joined1, 'Append', @success OUT, 'UID FETCH '
            EXEC sp_OAMethod @joined1, 'Append', @success OUT, @uid
            EXEC sp_OAMethod @joined1, 'Append', @success OUT, ' (UID X-GM-MSGID X-GM-THRID)'
            DECLARE @response nvarchar(4000)
            EXEC sp_OAMethod @joined1, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @imap, 'SendRawCommand', @response OUT, @sTmp0
            EXEC sp_OAMethod @statusText, 'SetString', @success OUT, @response
            --  A completed raw exchange can still return NO or BAD. Require tagged OK.
            EXEC sp_OAMethod @statusText, 'RegexMatch', @completed OUT, '(?i)(?:\A|\r\n)[^*+ \r\n]+ OK[^\r\n]*\r?\n?\z', @statusMatch, 1000

            EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
            IF (@iTmp0 <> 1) or (@completed <> 1)
              BEGIN
                EXEC sp_OAMethod @imap, 'Logout', @success OUT
                EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

                PRINT 'Could not fetch Gmail IDs.'
                EXEC @hr = sp_OADestroy @statusText
                EXEC @hr = sp_OADestroy @statusMatch
                EXEC @hr = sp_OADestroy @token
                EXEC @hr = sp_OADestroy @imap
                EXEC @hr = sp_OADestroy @folders
                EXEC @hr = sp_OADestroy @mailboxNames
                EXEC @hr = sp_OADestroy @unique
                EXEC @hr = sp_OADestroy @found
                EXEC @hr = sp_OADestroy @uidStrings
                EXEC @hr = sp_OADestroy @raw
                EXEC @hr = sp_OADestroy @captures
                EXEC @hr = sp_OADestroy @joined1
                RETURN
              END
            EXEC sp_OAMethod @raw, 'SetString', @success OUT, @response
            --  Match only this UID's metadata line. Attribute order need not be fixed.
            --  Unsolicited EXISTS/EXPUNGE/FLAGS lines do not match these three attributes.
            DECLARE @joined2 int
            EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined2 OUT

            EXEC sp_OAMethod @joined2, 'Append', @success OUT, '(?mi)^\* [0-9]+ FETCH \((?=[^\r\n]*\bUID '
            EXEC sp_OAMethod @joined2, 'Append', @success OUT, @uid
            EXEC sp_OAMethod @joined2, 'Append', @success OUT, '(?: |\)))(?=[^\r\n]*\bX-GM-MSGID ([0-9]+)(?: |\)))(?=[^\r\n]*\bX-GM-THRID ([0-9]+)(?: |\)))[^\r\n]*\)\r?$'
            DECLARE @pattern nvarchar(4000)
            EXEC sp_OAMethod @joined2, 'GetAsString', @pattern OUT
            DECLARE @count int
            EXEC sp_OAMethod @raw, 'RegexMatch', @count OUT, @pattern, @captures, 1000
            IF @count < 0
              BEGIN
                EXEC sp_OAMethod @imap, 'Logout', @success OUT
                EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

                PRINT 'Could not parse the Gmail metadata response.'
                EXEC @hr = sp_OADestroy @statusText
                EXEC @hr = sp_OADestroy @statusMatch
                EXEC @hr = sp_OADestroy @token
                EXEC @hr = sp_OADestroy @imap
                EXEC @hr = sp_OADestroy @folders
                EXEC @hr = sp_OADestroy @mailboxNames
                EXEC @hr = sp_OADestroy @unique
                EXEC @hr = sp_OADestroy @found
                EXEC @hr = sp_OADestroy @uidStrings
                EXEC @hr = sp_OADestroy @raw
                EXEC @hr = sp_OADestroy @captures
                EXEC @hr = sp_OADestroy @joined1
                EXEC @hr = sp_OADestroy @joined2
                RETURN
              END
            IF @count = 1
              BEGIN
                DECLARE @messageId nvarchar(4000)
                EXEC sp_OAMethod @captures, 'StringOf', @messageId OUT, 'match[0].group[1].cap'
                DECLARE @threadId nvarchar(4000)
                EXEC sp_OAMethod @captures, 'StringOf', @threadId OUT, 'match[0].group[2].cap'
                EXEC sp_OAMethod @unique, 'IndexOf', @iTmp0 OUT, @messageId
                IF @iTmp0 < 0
                  BEGIN
                    --  Direct JSON member names; both IDs remain strings without numeric conversion.
                    EXEC sp_OAMethod @unique, 'AppendString', @success OUT, @messageId, @threadId


                    PRINT 'Message ID: ' + @messageId + '  Thread ID: ' + @threadId
                  END
                ELSE
                  BEGIN

                    PRINT 'Already encountered under another label: ' + @messageId
                  END
              END
            ELSE
              BEGIN


                PRINT 'No single metadata row for UID ' + @uid + '; it may have disappeared. Skipping.'
              END
            SELECT @i = @i + 1
          END
        SELECT @folderIndex = @folderIndex + 1
      END
    EXEC sp_OAMethod @imap, 'Logout', @success OUT
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC sp_OAGetProperty @unique, 'Size', @iTmp0 OUT
    PRINT 'Unique sampled messages: ' + @iTmp0

    EXEC @hr = sp_OADestroy @statusText
    EXEC @hr = sp_OADestroy @statusMatch
    EXEC @hr = sp_OADestroy @token
    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @folders
    EXEC @hr = sp_OADestroy @mailboxNames
    EXEC @hr = sp_OADestroy @unique
    EXEC @hr = sp_OADestroy @found
    EXEC @hr = sp_OADestroy @uidStrings
    EXEC @hr = sp_OADestroy @raw
    EXEC @hr = sp_OADestroy @captures
    EXEC @hr = sp_OADestroy @joined1
    EXEC @hr = sp_OADestroy @joined2


END
GO