Gmail IMAP: Read, Add, or Remove Message Labels
See more GMail SMTP/IMAP/POP Examples
Locate a message in INBOX by its decimal Gmail message ID, then read its labels or add/remove one custom label with X-GM-LABELS. The default action is read. Changing action to add or remove modifies the mailbox. Obtain the message ID from the message and thread IDs example.
The code changes only the specified custom label and preserves other labels. A message shown under several labels is still one Gmail message. System labels have different semantics; use the archive/Trash example for those operations. If the message left INBOX, select its current mailbox before searching.
This focused raw-command example accepts printable ASCII custom labels beginning with a letter or digit, escapes quotes and backslashes, and displays the returned label list in IMAP syntax. Non-ASCII names require IMAP modified UTF-7 encoding. The response is not parsed by splitting on spaces because quoted names can contain spaces.
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
DECLARE @replacements int
-- 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
EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'INBOX'
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Could not open INBOX.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- Obtain this decimal string from the Gmail message/thread ID example.
DECLARE @gmailMessageId nvarchar(4000)
SELECT @gmailMessageId = '1278455344230334865'
DECLARE @idText int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @idText OUT
EXEC sp_OAMethod @idText, 'Append', @success OUT, @gmailMessageId
DECLARE @validation int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @validation OUT
DECLARE @matches int
EXEC sp_OAMethod @idText, 'RegexMatch', @matches OUT, '\A[1-9][0-9]{0,19}\z', @validation, 1000
IF @matches <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Expected a decimal Gmail message ID string.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
RETURN
END
DECLARE @target int
EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @target OUT
DECLARE @joined1 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined1 OUT
EXEC sp_OAMethod @joined1, 'Append', @success OUT, 'X-GM-MSGID '
EXEC sp_OAMethod @joined1, 'Append', @success OUT, @gmailMessageId
EXEC sp_OAMethod @joined1, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, @sTmp0, 1, @target
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Could not find the Gmail message in the selected mailbox.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
RETURN
END
EXEC sp_OAGetProperty @target, 'Count', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC sp_OAGetProperty @target, 'Count', @iTmp0 OUT
PRINT 'Expected one message in the selected mailbox. Matches: ' + @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 @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
RETURN
END
DECLARE @uid nvarchar(4000)
EXEC sp_OAMethod @target, 'ToCompactString', @uid OUT
-- Choose read, add, or remove. Only add/remove changes the message.
DECLARE @action nvarchar(4000)
SELECT @action = 'read'
DECLARE @label nvarchar(4000)
SELECT @label = 'Example label'
IF (@action <> 'read') and (@action <> 'add') and (@action <> 'remove')
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Choose read, add, or remove.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
RETURN
END
DECLARE @labelText int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @labelText OUT
EXEC sp_OAMethod @labelText, 'Append', @success OUT, @label
-- This raw-command example accepts printable ASCII custom labels only.
-- For non-ASCII labels, encode modified UTF-7 before quoting the command argument.
EXEC sp_OAMethod @labelText, 'RegexMatch', @matches OUT, '\A[A-Za-z0-9][\x20-\x7e]*\z', @validation, 1000
IF @matches <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Use a printable ASCII custom label starting with a letter or digit.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @labelText
RETURN
END
EXEC sp_OAMethod @labelText, 'Replace', @replacements OUT, '\', '\\'
EXEC sp_OAMethod @labelText, 'Replace', @replacements OUT, '"', '\"'
DECLARE @response nvarchar(4000)
DECLARE @command nvarchar(4000)
IF @action <> 'read'
BEGIN
DECLARE @operation nvarchar(4000)
SELECT @operation = '+X-GM-LABELS.SILENT'
IF @action = 'remove'
BEGIN
SELECT @operation = '-X-GM-LABELS.SILENT'
END
DECLARE @joined2 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined2 OUT
EXEC sp_OAMethod @joined2, 'Append', @success OUT, 'UID STORE '
EXEC sp_OAMethod @joined2, 'Append', @success OUT, @uid
EXEC sp_OAMethod @joined2, 'Append', @success OUT, ' '
EXEC sp_OAMethod @joined2, 'Append', @success OUT, @operation
EXEC sp_OAMethod @joined2, 'Append', @success OUT, ' ("'
EXEC sp_OAMethod @labelText, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @joined2, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @joined2, 'Append', @success OUT, '")'
EXEC sp_OAMethod @joined2, 'GetAsString', @command OUT
EXEC sp_OAMethod @imap, 'SendRawCommand', @response OUT, @command
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 'Label update failed. Check the custom label and mailbox permissions.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @labelText
EXEC @hr = sp_OADestroy @joined2
RETURN
END
END
DECLARE @joined3 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined3 OUT
EXEC sp_OAMethod @joined3, 'Append', @success OUT, 'UID FETCH '
EXEC sp_OAMethod @joined3, 'Append', @success OUT, @uid
EXEC sp_OAMethod @joined3, 'Append', @success OUT, ' (X-GM-LABELS)'
EXEC sp_OAMethod @joined3, '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 read the message labels.'
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @labelText
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
RETURN
END
-- Display the server's label-list syntax, including quoted/encoded names.
-- This is a FETCH response, not an authentication/session log.
PRINT @response
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC @hr = sp_OADestroy @statusText
EXEC @hr = sp_OADestroy @statusMatch
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @labelText
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
END
GO