Gmail POP3: Recent Mode with Persistent UIDL Tracking
See more GMail SMTP/IMAP/POP Examples
Use recent:user@example.com to access Gmail's recent POP delivery window, then download each UIDL only once. This example uses an app password, requiring 2-Step Verification and an account policy that allows app passwords. Enable POP and select Keep Gmail's copy in the Inbox. An ordinary account password will not work. If app passwords are unavailable, use the OAuth IMAP examples.
Recent mode exposes approximately the last 30 days of mail and is useful when multiple POP clients need the same messages. Keep the recent prefix out of SMTP settings. This is a rolling window, not a complete-mailbox backup; polling after a long gap can miss older mail.
UIDLs stay as opaque strings. A SHA-256 filename derived from mailbox plus UIDL identifies each saved MIME file. The final file doubles as the persistent completion receipt: a second run skips it. A partial download or failed write is never marked complete, and a leftover .part file is retried. The code sends no DELE commands.
Keep this private directory dedicated to the downloader, use a stable mailbox spelling, and run only one writer at a time. Moving or deleting final files discards the tracking information and permits a new download while the message is still available. For business processing beyond saving MIME, use an application transaction and idempotent handling.
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
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
-- Use a private directory dedicated to this application's downloaded mail.
DECLARE @mailbox nvarchar(4000)
SELECT @mailbox = 'user@example.com'
DECLARE @appPassword nvarchar(4000)
SELECT @appPassword = 'YOUR_16_CHARACTER_APP_PASSWORD'
DECLARE @files int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @files OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @files, 'DirEnsureExists', @success OUT, 'gmail-pop-mail'
IF @success <> 1
BEGIN
PRINT 'Could not create the private download directory.'
EXEC @hr = sp_OADestroy @files
RETURN
END
DECLARE @mailman int
EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT
EXEC sp_OASetProperty @mailman, 'MailHost', 'pop.gmail.com'
EXEC sp_OASetProperty @mailman, 'MailPort', 995
EXEC sp_OASetProperty @mailman, 'PopSsl', 1
EXEC sp_OASetProperty @mailman, 'RequireSslCertVerify', 1
DECLARE @joined1 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined1 OUT
EXEC sp_OAMethod @joined1, 'Append', @success OUT, 'recent:'
EXEC sp_OAMethod @joined1, 'Append', @success OUT, @mailbox
EXEC sp_OAMethod @joined1, 'GetAsString', @sTmp0 OUT
EXEC sp_OASetProperty @mailman, 'PopUsername', @sTmp0
EXEC sp_OASetProperty @mailman, 'PopPassword', @appPassword
-- Use an eligible account's app password loaded from protected configuration.
-- The recent: prefix is only for POP; never add it to an SMTP username.
EXEC sp_OAMethod @mailman, 'Pop3BeginSession', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'POP login failed. Check the app password, POP setting, and account policy.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
RETURN
END
DECLARE @uidls int
EXEC @hr = sp_OACreate 'Chilkat.StringTable', @uidls OUT
EXEC sp_OAMethod @mailman, 'FetchUidls', @success OUT, @uidls
IF @success <> 1
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'Could not list POP UIDLs.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
RETURN
END
DECLARE @hash int
EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @hash OUT
EXEC sp_OASetProperty @hash, 'HashAlgorithm', 'sha256'
EXEC sp_OASetProperty @hash, 'EncodingMode', 'hex'
EXEC sp_OASetProperty @hash, 'Charset', 'utf-8'
DECLARE @mime int
EXEC @hr = sp_OACreate 'Chilkat.BinData', @mime OUT
DECLARE @saved int
SELECT @saved = 0
DECLARE @skipped int
SELECT @skipped = 0
DECLARE @i int
SELECT @i = 0
EXEC sp_OAGetProperty @uidls, 'Count', @iTmp0 OUT
WHILE (@i < @iTmp0)
BEGIN
DECLARE @uidl nvarchar(4000)
EXEC sp_OAMethod @uidls, 'StringAt', @uidl OUT, @i
-- Never use the opaque server UIDL as a filesystem path.
DECLARE @joined2 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined2 OUT
EXEC sp_OAMethod @joined2, 'Append', @success OUT, @mailbox
EXEC sp_OAMethod @joined2, 'Append', @success OUT, CHAR(10)
EXEC sp_OAMethod @joined2, 'Append', @success OUT, @uidl
DECLARE @fileKey nvarchar(4000)
EXEC sp_OAMethod @joined2, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @hash, 'HashStringENC', @fileKey OUT, @sTmp0
EXEC sp_OAGetProperty @hash, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'Could not construct the local UIDL key.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
EXEC @hr = sp_OADestroy @hash
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @joined2
RETURN
END
DECLARE @joined3 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined3 OUT
EXEC sp_OAMethod @joined3, 'Append', @success OUT, 'gmail-pop-mail/'
EXEC sp_OAMethod @joined3, 'Append', @success OUT, @fileKey
EXEC sp_OAMethod @joined3, 'Append', @success OUT, '.eml'
DECLARE @path nvarchar(4000)
EXEC sp_OAMethod @joined3, 'GetAsString', @path OUT
DECLARE @exists int
EXEC sp_OAMethod @files, 'FileExists3', @exists OUT, @path
IF @exists < 0
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'Could not check the local download receipt.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
EXEC @hr = sp_OADestroy @hash
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
RETURN
END
IF @exists = 1
BEGIN
SELECT @skipped = @skipped + 1
END
ELSE
BEGIN
EXEC sp_OAMethod @mailman, 'FetchMimeBd', @success OUT, @uidl, @mime
IF @success <> 1
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'Download failed; the UIDL was not marked complete.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
EXEC @hr = sp_OADestroy @hash
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
RETURN
END
-- A final .eml file is the persistent completion receipt for this UIDL.
-- Save to a sibling temporary file first. A leftover .part file is not a receipt.
DECLARE @joined4 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined4 OUT
EXEC sp_OAMethod @joined4, 'Append', @success OUT, @path
EXEC sp_OAMethod @joined4, 'Append', @success OUT, '.part'
EXEC sp_OAMethod @joined4, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @mime, 'WriteFile', @success OUT, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'Could not save the complete MIME file.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
EXEC @hr = sp_OADestroy @hash
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
EXEC @hr = sp_OADestroy @joined4
RETURN
END
DECLARE @joined5 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined5 OUT
EXEC sp_OAMethod @joined5, 'Append', @success OUT, @path
EXEC sp_OAMethod @joined5, 'Append', @success OUT, '.part'
EXEC sp_OAMethod @joined5, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @files, 'FileRename', @success OUT, @sTmp0, @path
IF @success <> 1
BEGIN
EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT
PRINT 'Could not commit the downloaded message. Keep the temporary file for diagnosis.'
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
EXEC @hr = sp_OADestroy @hash
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
EXEC @hr = sp_OADestroy @joined4
EXEC @hr = sp_OADestroy @joined5
RETURN
END
SELECT @saved = @saved + 1
END
SELECT @i = @i + 1
END
-- No DELE commands: leave the messages on Gmail for other clients/recent-mode polls.
EXEC sp_OAMethod @mailman, 'Pop3EndSession', @success OUT
PRINT 'Saved: ' + @saved + ' Already saved: ' + @skipped
IF @success <> 1
BEGIN
PRINT 'The local files are committed, but closing the POP session failed.'
END
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @uidls
EXEC @hr = sp_OADestroy @hash
EXEC @hr = sp_OADestroy @mime
EXEC @hr = sp_OADestroy @joined2
EXEC @hr = sp_OADestroy @joined3
EXEC @hr = sp_OADestroy @joined4
EXEC @hr = sp_OADestroy @joined5
END
GO