SQL Server
SQL Server
Search IMAP Mailbox for Email Matching Criteria
Searching an IMAP mailbox for messages that match search criteria.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
-- 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.
DECLARE @imap int
EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Connect to an IMAP server.
-- Use TLS
EXEC sp_OASetProperty @imap, 'Ssl', 1
EXEC sp_OASetProperty @imap, 'Port', 993
EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
IF @success = 0
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, 'myLogin', 'myPassword'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- Select an IMAP mailbox
EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
RETURN
END
-- We can choose to fetch UIDs or sequence numbers.
DECLARE @fetchUids int
SELECT @fetchUids = 1
-- Here are examples of different search criteria:
-- Return all messages.
DECLARE @allMsgs nvarchar(4000)
SELECT @allMsgs = 'ALL'
-- Search for already-answered emails.
DECLARE @answered nvarchar(4000)
SELECT @answered = 'ANSWERED'
-- Search for messages on a specific date.
-- The date string is DD-Month-YYYY where Month is
-- Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, or Dec.
DECLARE @onDate nvarchar(4000)
SELECT @onDate = 'SENTON 05-Mar-2007'
-- Search for messages between two dates. SENTBEFORE
-- finds emails sent before a date, and SENTSINCE finds
-- email sent on or after a date. The "AND" operation
-- is implied by joining criteria, separated by spaces.
DECLARE @betweenDates nvarchar(4000)
SELECT @betweenDates = 'SENTSINCE 01-Mar-2007 SENTBEFORE 05-Mar-2007'
-- Another example of AND: find all unanswered emails
-- sent after 04-Mar-2007 with "Problem" in the subject:
DECLARE @complexSearch1 nvarchar(4000)
SELECT @complexSearch1 = 'UNANSWERED SENTSINCE 04-Mar-2007 Subject "Problem"'
-- Find messages with a specific string in the body:
DECLARE @bodySearch nvarchar(4000)
SELECT @bodySearch = 'BODY "problem solved"'
-- Using OR. The syntax is OR <criteria1> <criteria2>.
-- The "OR" comes first, followed by each criteria.
-- For example, to match all emails with "Help" or "Question" in the subject.
-- You'll notice that literal strings may be quoted or unquoted.
-- If a literal contains SPACE characters, quote it:
DECLARE @orSearch nvarchar(4000)
SELECT @orSearch = 'OR SUBJECT Help SUBJECT Question'
-- ----------------------------------------------
-- Strings are case-insensitive when searching....
-- ----------------------------------------------
-- Find all emails sent from yahoo.com addresses:
DECLARE @fromSearch nvarchar(4000)
SELECT @fromSearch = 'FROM yahoo.com'
-- Find all emails sent from anyone with "John" in their name:
DECLARE @johnSearch nvarchar(4000)
SELECT @johnSearch = 'FROM John'
-- Find emails with the RECENT flag set:
DECLARE @recentSearch nvarchar(4000)
SELECT @recentSearch = 'RECENT'
-- Find emails that don't have the recent flag set:
DECLARE @notRecentSearch nvarchar(4000)
SELECT @notRecentSearch = 'NOT RECENT'
-- This is synonymous with "OLD":
DECLARE @oldSearch nvarchar(4000)
SELECT @oldSearch = 'OLD'
-- Find all emails marked for deletion:
DECLARE @markedForDeleteSearch nvarchar(4000)
SELECT @markedForDeleteSearch = 'DELETED'
-- Find all emails having a specified header field with a value
-- containing a substring:
DECLARE @headerSearch nvarchar(4000)
SELECT @headerSearch = 'HEADER DomainKey-Signature paypal.com'
-- Find any emails having a specific header field. If the
-- 2nd argument to the "HEADER" criteria is an empty string,
-- any email having the header field is returned regardless
-- of the header field's content.
-- Find any emails with a DomainKey-Signature field:
DECLARE @headerExistsSearch nvarchar(4000)
SELECT @headerExistsSearch = 'HEADER DomainKey-Signature ""'
-- Find NEW emails: these are emails that have the RECENT flag
-- set, but not the SEEN flag:
DECLARE @newSearch nvarchar(4000)
SELECT @newSearch = 'NEW'
-- Find emails larger than a certain number of bytes:
DECLARE @sizeLargerSearch nvarchar(4000)
SELECT @sizeLargerSearch = 'LARGER 500000'
-- Find emails marked as seen or not already seen:
DECLARE @seenSearch nvarchar(4000)
SELECT @seenSearch = 'SEEN'
DECLARE @notSeenSearch nvarchar(4000)
SELECT @notSeenSearch = 'NOT SEEN'
-- Find emails having a given substring in the TO header field:
DECLARE @toSearch nvarchar(4000)
SELECT @toSearch = 'TO support@chilkatsoft.com'
-- A more long-winded way to do the same thing:
DECLARE @toSearch2 nvarchar(4000)
SELECT @toSearch2 = 'HEADER TO support@chilkatsoft.com'
-- Find emails smaller than a size in bytes:
DECLARE @smallerSearch nvarchar(4000)
SELECT @smallerSearch = 'SMALLER 30000'
-- Find emails that have a substring anywhere in the header
-- or body:
DECLARE @fullSubstringSearch nvarchar(4000)
SELECT @fullSubstringSearch = 'TEXT "Zip Component"'
-- Pass any of the above strings here to test a search:
DECLARE @messageSet int
EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT
EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, @orSearch, @fetchUids, @messageSet
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @messageSet
RETURN
END
-- Fetch the email headers into a bundle object:
DECLARE @bundle int
EXEC @hr = sp_OACreate 'Chilkat.EmailBundle', @bundle OUT
DECLARE @headersOnly int
SELECT @headersOnly = 1
EXEC sp_OAMethod @imap, 'FetchMsgSet', @success OUT, @headersOnly, @messageSet, @bundle
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @messageSet
EXEC @hr = sp_OADestroy @bundle
RETURN
END
-- Display the Subject and From of each email.
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT
DECLARE @i int
SELECT @i = 0
EXEC sp_OAGetProperty @bundle, 'MessageCount', @iTmp0 OUT
WHILE @i < @iTmp0
BEGIN
EXEC sp_OAMethod @bundle, 'EmailAt', @success OUT, @i, @email
EXEC sp_OAMethod @email, 'GetHeaderField', @sTmp0 OUT, 'Date'
PRINT @sTmp0
EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT
PRINT @sTmp0
PRINT '--'
SELECT @i = @i + 1
END
-- Disconnect from the IMAP server.
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @messageSet
EXEC @hr = sp_OADestroy @bundle
EXEC @hr = sp_OADestroy @email
END
GO