(SQL Server) Examine Junk/NonJunk Flags on Outlook.com and GMail

Examines the Junk and NonJunk FLAGS for IMAP on Outlook.com and GMail.com.

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)
    DECLARE @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- This example assumes Chilkat Imap to have been previously unlocked.
    -- See Unlock Imap for sample code.

    -- Connect to the outlook.com IMAP server.
    -- We'll do the same for GMail below..
    -- Use TLS
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    DECLARE @success int
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap-mail.outlook.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myAccount@outlook.com', 'OUTLOOK_PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Begin keeping a log of the session here.
    EXEC sp_OASetProperty @imap, 'KeepSessionLog', 1

    -- Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- The LastResponse property contains the full response of the last command.
    -- The response to the SELECT mailbox command shows the FLAGS that can be set
    -- for emails in the mailbox.
    DECLARE @selectResponse nvarchar(4000)
    EXEC sp_OAGetProperty @imap, 'LastResponse', @selectResponse OUT

    PRINT @selectResponse

    -- The response to "SELECT Inbox" looks like this:
    -- There are no FLAGS for Junk/NonJunk..

    -- * 4 EXISTS
    -- * 0 RECENT
    -- * FLAGS (\Seen \Answered \Flagged \Deleted \Draft $MDNSent)
    -- * OK [PERMANENTFLAGS (\Seen \Answered \Flagged \Deleted \Draft $MDNSent)] Permanent flags
    -- * OK [UIDVALIDITY 14] UIDVALIDITY value
    -- * OK [UIDNEXT 1719] The next unique identifier value
    -- aaac OK [READ-WRITE] SELECT completed.

    -- Fetch the flags for each message in the mailbox.
    -- Prior to running this example, (using Mozilla Thunderbird) I marked one of the emails
    -- in my outlook.com Inbox as Junk.  
    DECLARE @sequenceNum int
    SELECT @sequenceNum = 1
    EXEC sp_OAGetProperty @imap, 'NumMessages', @iTmp0 OUT
    WHILE @sequenceNum <= @iTmp0
      BEGIN
        DECLARE @flags nvarchar(4000)
        EXEC sp_OAMethod @imap, 'FetchFlags', @flags OUT, @sequenceNum, 0


        PRINT @sequenceNum + ': ' + @flags
        SELECT @sequenceNum = @sequenceNum + 1
      END

    -- The output of the above loop is this:

    -- 	1: \Seen
    -- 	2: \Seen
    -- 	3: \Seen
    -- 	4: \Seen

    -- As you can see, nothing is marked as Junk/Spam.  This concurs with the list of FLAGS
    -- documented in the response to the SELECT command.  Apparently, there is no flag for junk/nonjunk
    -- for outlook.com.  Mozilla Thunderbird must be storing additional information about particular
    -- emails locally.  (In other words, perhaps the Junk/NonJunk flag is information stored locally 
    -- by Thunderbird, and is not a flag set on the server.)


    PRINT '---- Session Log ----'
    EXEC sp_OAGetProperty @imap, 'SessionLog', @sTmp0 OUT
    PRINT @sTmp0

    -- The session log looks like this:

    -- 	----IMAP REQUEST----
    -- 	aaac SELECT "Inbox"
    -- 	----IMAP RESPONSE----
    -- 	* 4 EXISTS
    -- 	* 0 RECENT
    -- 	* FLAGS (\Seen \Answered \Flagged \Deleted \Draft $MDNSent)
    -- 	* OK [PERMANENTFLAGS (\Seen \Answered \Flagged \Deleted \Draft $MDNSent)] Permanent flags
    -- 	* OK [UIDVALIDITY 14] UIDVALIDITY value
    -- 	* OK [UIDNEXT 1719] The next unique identifier value
    -- 	aaac OK [READ-WRITE] SELECT completed.
    -- 	----IMAP REQUEST----
    -- 	aaad FETCH 1 (FLAGS)
    -- 	----IMAP RESPONSE----
    -- 	* 1 FETCH (FLAGS (\Seen))
    -- 	aaad OK FETCH completed.
    -- 	----IMAP REQUEST----
    -- 	aaae FETCH 2 (FLAGS)
    -- 	----IMAP RESPONSE----
    -- 	* 2 FETCH (FLAGS (\Seen))
    -- 	aaae OK FETCH completed.
    -- 	----IMAP REQUEST----
    -- 	aaaf FETCH 3 (FLAGS)
    -- 	----IMAP RESPONSE----
    -- 	* 3 FETCH (FLAGS (\Seen))
    -- 	aaaf OK FETCH completed.
    -- 	----IMAP REQUEST----
    -- 	aaag FETCH 4 (FLAGS)
    -- 	----IMAP RESPONSE----
    -- 	* 4 FETCH (FLAGS (\Seen))
    -- 	aaag OK FETCH completed.

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    -- --------------------------------------------------------------------------------
    -- Now let's check GMail..
    -- Again, I've marked one email as Junk.

    EXEC sp_OASetProperty @imap, 'KeepSessionLog', 0
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.gmail.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myAccount@gmail.com', 'GMAIL-IMAP-PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    EXEC sp_OASetProperty @imap, 'KeepSessionLog', 1

    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    EXEC sp_OAGetProperty @imap, 'LastResponse', @selectResponse OUT

    PRINT @selectResponse

    -- The response to GMail "SELECT Inbox" looks like this:

    -- * FLAGS (\Answered \Flagged \Draft \Deleted \Seen $Forwarded $NotPhishing $Phishing $label1 $label2 $label3 $label4 $label5 Junk NonJunk)
    -- * OK [PERMANENTFLAGS (\Answered \Flagged \Draft \Deleted \Seen $Forwarded $NotPhishing $Phishing $label1 $label2 $label3 $label4 $label5 Junk NonJunk \*)] Flags permitted.
    -- * OK [UIDVALIDITY 3] UIDs valid.
    -- * 46 EXISTS
    -- * 0 RECENT
    -- * OK [UIDNEXT 4147] Predicted next UID.
    -- * OK [HIGHESTMODSEQ 403404]
    -- aaai OK [READ-WRITE] Inbox selected. (Success)

    EXEC sp_OAGetProperty @imap, 'NumMessages', @sequenceNum OUT
    DECLARE @minSeqNum int
    SELECT @minSeqNum = 1
    IF @sequenceNum > 10
      BEGIN
        SELECT @minSeqNum = @sequenceNum - 10
      END
    WHILE @sequenceNum >= @minSeqNum
      BEGIN
        DECLARE @flags nvarchar(4000)
        EXEC sp_OAMethod @imap, 'FetchFlags', @flags OUT, @sequenceNum, 0


        PRINT @sequenceNum + ': ' + @flags
        SELECT @sequenceNum = @sequenceNum - 1
      END

    -- The output (for GMail) of the above loop is this:

    -- 	46: Junk \Seen
    -- 	45: NonJunk \Answered \Seen
    -- 	44: NonJunk \Seen
    -- 	43: \Answered \Seen
    -- 	42: \Answered \Seen
    -- 	41: NonJunk \Seen
    -- 	40: NonJunk \Answered \Seen
    -- 	39: NonJunk \Seen
    -- 	38: NonJunk \Seen
    -- 	37: NonJunk \Seen
    -- 	36: NonJunk \Seen

    -- As you can see, the email I marked as "Junk" using Mozilla Thunderbird has the Junk flag set.
    -- This concurs with GMail's list of FLAGS.  Apparently, since the IMAP server has a "Junk" flag,
    -- Thunderbird set it when I marked the email as Junk. 

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @imap


END
GO