Gmail IMAP: Discover Special Folders
See more GMail SMTP/IMAP/POP Examples
Find Gmail special folders from the attributes returned by LIST, using MbxList and Mailboxes.HasFlag. The result maps roles such as Sent and Trash to the server-provided mailbox names. Chilkat decodes mailbox names for use with its mailbox methods.
Folder names can be localized and visibility can differ by account. The role attributes are stable; do not hard-code [Gmail]/Sent Mail or use the deprecated XLIST command. INBOX is a protocol-defined name. Missing map keys indicate folders not advertised by the server.
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 @sTmp1 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 @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
-- LIST special-use attributes work with localized folder names. XLIST is obsolete.
DECLARE @special int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @special OUT
DECLARE @i int
SELECT @i = 0
EXEC sp_OAGetProperty @folders, 'Count', @iTmp0 OUT
WHILE (@i < @iTmp0)
BEGIN
EXEC sp_OAMethod @folders, 'IsSelectable', @iTmp0 OUT, @i
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @folders, 'GetFlags', @sTmp1 OUT, @i
PRINT @sTmp0 + ' ' + @sTmp1
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\All'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'allMail', @sTmp0
END
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\Sent'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'sent', @sTmp0
END
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\Drafts'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'drafts', @sTmp0
END
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\Junk'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'spam', @sTmp0
END
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\Trash'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'trash', @sTmp0
END
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\Flagged'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'starred', @sTmp0
END
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, '\Important'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @sTmp0 OUT, @i
EXEC sp_OAMethod @special, 'UpdateString', @success OUT, 'important', @sTmp0
END
END
SELECT @i = @i + 1
END
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC sp_OASetProperty @special, 'EmitCompact', 0
EXEC sp_OAMethod @special, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Missing keys mean the folder was not advertised. Do not substitute an English name.
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
EXEC @hr = sp_OADestroy @special
END
GO