Gmail IMAP: Archive a Message or Move It to Trash
See more GMail SMTP/IMAP/POP Examples
This example changes one message in INBOX. Choose archive to move it to the discovered All Mail folder, or trash to move it to the discovered Trash folder. It resolves a decimal Gmail message ID to a mailbox UID and uses MoveMessages with the server's MOVE capability.
Archiving removes a message from INBOX while retaining it in All Mail. Moving it to Trash is a different action; Gmail normally permanently deletes messages after 30 days there. This example does not empty Trash or permanently delete mail. A message can retain other custom labels when archived.
Folder names are discovered by special-use attributes. Avoid a generic delete/EXPUNGE sequence whose Gmail outcome can depend on account settings and which can affect unrelated messages. If a connection fails during a move, inspect the source and destination before retrying. A concurrent move or deletion can make the UID disappear.
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 @token int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @token OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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 @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 @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 @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 @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 @token
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- Choose archive or trash. This example changes one message's location.
DECLARE @action nvarchar(4000)
SELECT @action = 'archive'
IF (@action <> 'archive') and (@action <> 'trash')
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Choose archive or trash.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
RETURN
END
EXEC sp_OAMethod @imap, 'HasCapability', @iTmp0 OUT, 'MOVE', @capabilities
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'The server must support IMAP MOVE.'
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 @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
RETURN
END
DECLARE @role nvarchar(4000)
SELECT @role = '\All'
IF @action = 'trash'
BEGIN
SELECT @role = '\Trash'
END
DECLARE @destination nvarchar(4000)
SELECT @destination = ''
DECLARE @count int
SELECT @count = 0
DECLARE @i int
SELECT @i = 0
EXEC sp_OAGetProperty @folders, 'Count', @iTmp0 OUT
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, @role
EXEC sp_OAMethod @folders, 'IsSelectable', @iTmp1 OUT, @i
IF (@iTmp0 = 1) and (@iTmp1 = 1)
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @destination OUT, @i
SELECT @count = @count + 1
END
SELECT @i = @i + 1
END
IF @count <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Could not identify the destination folder uniquely.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
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 for the move.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
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 @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
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 @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
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 @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
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
-- MoveMessages uses UID MOVE and maintains the Imap object's mailbox state.
-- No broad EXPUNGE is issued, and unrelated deleted flags are not committed.
EXEC sp_OAMethod @imap, 'MoveMessages', @success OUT, @target, @destination
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Move failed; verify the mailbox state before retrying after a connection loss.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
RETURN
END
PRINT 'Completed ' + @action + ' for Gmail message ' + @gmailMessageId
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
EXEC @hr = sp_OADestroy @idText
EXEC @hr = sp_OADestroy @validation
EXEC @hr = sp_OADestroy @target
EXEC @hr = sp_OADestroy @joined1
END
GO