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.
Chilkat SQL Server Downloads
-- 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