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

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.

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 @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