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

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.

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

    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