SQL Server
SQL Server
Examine Junk/NonJunk Flags on Outlook.com and GMail
See more IMAP Examples
Examines the Junk and NonJunk FLAGS for IMAP on Outlook.com and GMail.com.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 @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
DECLARE @imap int
EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample 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
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