(SQL Server) Reading Unread POP3 Email

The POP3 protocol cannot determine which emails are "unread," and pure POP3 servers do not store this information. Servers like Exchange Server, offering both POP3 and IMAP interfaces, do contain read/unread data, but it's only accessible through IMAP. Email clients like Outlook and Thunderbird store read/unread statuses on the client side. The example demonstrates using UIDLs to track and manage "unread" emails.

Note: This example requires Chilkat v11.0.0 or greater.

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 @success int
    SELECT @success = 0

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- The mailman object is used for receiving (POP3) 
    -- and sending (SMTP) email.
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Set the POP3 server's hostname
    EXEC sp_OASetProperty @mailman, 'MailHost', 'pop.example.com'

    -- Set the POP3 login/password.
    EXEC sp_OASetProperty @mailman, 'PopUsername', '***'
    EXEC sp_OASetProperty @mailman, 'PopPassword', '***'

    -- Keep a records of already-seen UIDLs in hash table serialized to an XML file.
    DECLARE @seenUidlsPath nvarchar(4000)
    SELECT @seenUidlsPath = 'c:/temp/seenUidls.xml'

    DECLARE @sbXml int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbXml OUT

    DECLARE @htSeenUidls int
    EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @htSeenUidls OUT

    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT

    EXEC sp_OAMethod @fac, 'FileExists', @iTmp0 OUT, @seenUidlsPath
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @sbXml, 'LoadFile', @success OUT, @seenUidlsPath, 'utf-8'
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @sbXml, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @mailman
            EXEC @hr = sp_OADestroy @sbXml
            EXEC @hr = sp_OADestroy @htSeenUidls
            EXEC @hr = sp_OADestroy @fac
            RETURN
          END
        EXEC sp_OAMethod @htSeenUidls, 'AddFromXmlSb', @success OUT, @sbXml
      END

    -- Get the complete list of UIDLs on the mail server.
    DECLARE @stUidls int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @stUidls OUT

    EXEC sp_OAMethod @mailman, 'FetchUidls', @success OUT, @stUidls
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @htSeenUidls
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @stUidls
        RETURN
      END

    -- Build a list of unseen UIDLs
    DECLARE @stUnseenUidls int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @stUnseenUidls OUT

    DECLARE @uidl nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count int
    EXEC sp_OAGetProperty @stUidls, 'Count', @count OUT
    WHILE @i < @count
      BEGIN
        EXEC sp_OAMethod @stUidls, 'StringAt', @uidl OUT, @i
        EXEC sp_OAMethod @htSeenUidls, 'Contains', @iTmp0 OUT, @uidl
        IF @iTmp0 <> 1
          BEGIN
            EXEC sp_OAMethod @stUnseenUidls, 'Append', @success OUT, @uidl
          END
        SELECT @i = @i + 1
      END

    EXEC sp_OAGetProperty @stUnseenUidls, 'Count', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No unseen emails!'
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @htSeenUidls
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @stUidls
        EXEC @hr = sp_OADestroy @stUnseenUidls
        RETURN
      END

    -- Download the unseen emails, adding each UIDL to the "seen" hash table.
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OAGetProperty @stUnseenUidls, 'Count', @count OUT
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        -- Download the full email.
        EXEC sp_OAMethod @stUnseenUidls, 'StringAt', @uidl OUT, @i
        EXEC sp_OAMethod @mailman, 'FetchByUidl', @success OUT, @uidl, 0, 0, @email
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @mailman
            EXEC @hr = sp_OADestroy @sbXml
            EXEC @hr = sp_OADestroy @htSeenUidls
            EXEC @hr = sp_OADestroy @fac
            EXEC @hr = sp_OADestroy @stUidls
            EXEC @hr = sp_OADestroy @stUnseenUidls
            EXEC @hr = sp_OADestroy @email
            RETURN
          END


        PRINT @i

        EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT
        PRINT 'From: ' + @sTmp0

        EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
        PRINT 'Subject: ' + @sTmp0

        -- Add this UIDL to the "seen" hash table.
        EXEC sp_OAMethod @htSeenUidls, 'AddStr', @success OUT, @uidl, ''

        SELECT @i = @i + 1
      END

    EXEC sp_OAMethod @mailman, 'Pop3EndSession', @success OUT

    -- Update the "seen" UIDLs file.
    EXEC sp_OAMethod @sbXml, 'Clear', NULL
    EXEC sp_OAMethod @htSeenUidls, 'ToXmlSb', @success OUT, @sbXml
    EXEC sp_OAMethod @sbXml, 'WriteFile', @success OUT, @seenUidlsPath, 'utf-8', 0
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @sbXml, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @sbXml
        EXEC @hr = sp_OADestroy @htSeenUidls
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @stUidls
        EXEC @hr = sp_OADestroy @stUnseenUidls
        EXEC @hr = sp_OADestroy @email
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @sbXml
    EXEC @hr = sp_OADestroy @htSeenUidls
    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @stUidls
    EXEC @hr = sp_OADestroy @stUnseenUidls
    EXEC @hr = sp_OADestroy @email


END
GO