(SQL Server) Examine Yahoo Mail IMAP Session Log

See more Yahoo Mail Examples

To examine the IMAP session log, set the KeepSessionLog property = 1, and then examine the contents of the SessionLog property.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- See Get Yahoo Mail OAuth2 Access Token for Desktop App 
    -- for sample code showing how to obtain a Yahoo Mail access token.

    -- In this example, our access token was previously saved to the following file which contains JSON like this:
    -- {
    --   "access_token": "kCVQdnOdul...LHucA--",
    --   "refresh_token": "AIenVXETSo0jklFBVkPS8vVm8E.Ej9ToRG.xDbDYmZ65WIs5t6CZhDrD",
    --   "expires_in": 3600,
    --   "token_type": "bearer",
    --   "xoauth_yahoo_guid": "HGDQCVF5JB4YDOWHITQJFBHCWA"
    -- }

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/tokens/yahooMail.json'
    IF @success <> 1
      BEGIN

        PRINT 'Failed to load Yahoo Mail access token file.'
        EXEC @hr = sp_OADestroy @json
        RETURN
      END
    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @accessToken OUT, 'access_token'

    -- Do OAuth2 authentication by using the access token as the password in the Login method below...
    DECLARE @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT

    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993

    -- Tell Chilkat IMAP to keep a session log.
    EXEC sp_OASetProperty @imap, 'KeepSessionLog', 1

    -- Connect to the Yahoo IMAP server.
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.mail.yahoo.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Indicate that XOAUTH2 authentication is to be used:
    EXEC sp_OASetProperty @imap, 'AuthMethod', 'XOAUTH2'

    -- Login
    -- Use the email address of the Yahoo Mail account that granted authorization.
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'user@yahoo.com', @accessToken
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
      END
    ELSE
      BEGIN

        PRINT 'Successfully authenticated with Yahoo IMAP using XOAUTH2!'
      END

    -- The ListMailboxes method returns a Mailboxes object
    -- that contains the collection of mailboxes.
    -- It accepts two arguments: a refName and a wildcardedMailbox.

    DECLARE @refName nvarchar(4000)
    SELECT @refName = ''
    -- refName is usually set to an empty string.
    -- A non-empty reference name argument is the name of a mailbox or a level of
    -- mailbox hierarchy, and indicates the context in which the mailbox
    -- name is interpreted.

    -- Select all mailboxes matching this pattern:
    DECLARE @wildcardedMailbox nvarchar(4000)
    SELECT @wildcardedMailbox = '*'

    DECLARE @mboxes int
    EXEC sp_OAMethod @imap, 'ListMailboxes', @mboxes OUT, @refName, @wildcardedMailbox
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    DECLARE @i int
    SELECT @i = 0
    EXEC sp_OAGetProperty @mboxes, 'Count', @iTmp0 OUT
    WHILE @i < @iTmp0
      BEGIN
        EXEC sp_OAMethod @mboxes, 'GetName', @sTmp0 OUT, @i
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- Sample output looks like this:

    -- Archive
    -- Bulk Mail
    -- Draft
    -- Inbox
    -- Inbox/emailsWithAttach
    -- Inbox/test
    -- Sent
    -- Trash

    -- Examine the session log
    EXEC sp_OAGetProperty @imap, 'SessionLog', @sTmp0 OUT
    PRINT @sTmp0

    -- Sample session log

    -- ----INFO----
    -- Connecting to IMAP server at imap.mail.yahoo.com:993
    -- ----IMAP RESPONSE----
    -- * OK [CAPABILITY IMAP4rev1 SASL-IR AUTH=PLAIN AUTH=XOAUTH2 AUTH=OAUTHBEARER ID MOVE NAMESPACE XYMHIGHESTMODSEQ UIDPLUS LITERAL+ CHILDREN X-MSG-EXT OBJECTID] IMAP4rev1 Hello
    -- ----IMAP REQUEST----
    -- aaab AUTHENTICATE XOAUTH2 
    -- ----IMAP RESPONSE----
    -- aaab OK AUTHENTICATE completed
    -- ----IMAP REQUEST----
    -- aaac LIST "" "*"
    -- ----IMAP RESPONSE----
    -- * LIST (\Archive \HasNoChildren) "/" "Archive"
    -- * LIST (\Junk \HasNoChildren) "/" "Bulk Mail"
    -- * LIST (\Drafts \HasNoChildren) "/" "Draft"
    -- * LIST (\HasChildren) "/" "Inbox"
    -- * LIST (\HasNoChildren) "/" "Inbox/emailsWithAttach"
    -- * LIST (\HasNoChildren) "/" "Inbox/test"
    -- * LIST (\Sent \HasNoChildren) "/" "Sent"
    -- * LIST (\Trash \HasNoChildren) "/" "Trash"
    -- aaac OK LIST completed

    -- Disconnect from the IMAP server.
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @mboxes


    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @imap


END
GO