SQL Server

Fetch a Range of IMAP Messages by Sequence Number

Demonstrates the Chilkat Imap.FetchRange method, which downloads a contiguous run of messages by sequence number. The arguments are headersOnly, the starting sequence number, the count of messages, and the EmailBundle that receives them. This example uses the message count returned by SelectMailbox to fetch the headers of the 10 most recent messages.

Background: IMAP sequence numbers are 1-based positions within the selected mailbox, from 1 (oldest) to the message count (newest), and they shift whenever messages are expunged. That makes FetchRange ideal for "give me the newest N" style paging within a single session, but note that sequence numbers are not stable identifiers across sessions the way UIDs are — use a UID-based approach if you need to remember which messages you have already seen.

Chilkat SQL Server Downloads

Download Chilkat for SQL Server

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  Demonstrates the Imap.FetchRange method, which downloads a contiguous run of messages by
    --  sequence number.  The 1st argument is headersOnly, the 2nd is the starting sequence number,
    --  the 3rd is the count of messages, and the 4th is the EmailBundle that receives them.

    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    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
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'user@example.com', 'myPassword'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  SelectMailbox returns the number of messages in the mailbox, or -1 on failure.
    DECLARE @numMessages int
    EXEC sp_OAMethod @imap, 'SelectMailbox', @numMessages OUT, 'Inbox'
    IF @numMessages < 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  Download the 10 most recent messages (headers only).
    DECLARE @startSeqnum int
    SELECT @startSeqnum = @numMessages - 9
    IF @startSeqnum < 1
      BEGIN
        SELECT @startSeqnum = 1
      END

    DECLARE @bundle int
    EXEC @hr = sp_OACreate 'Chilkat.EmailBundle', @bundle OUT

    EXEC sp_OAMethod @imap, 'FetchRange', @success OUT, 1, @startSeqnum, 10, @bundle
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @bundle
        RETURN
      END

    DECLARE @n int
    EXEC sp_OAGetProperty @bundle, 'MessageCount', @n OUT


    PRINT 'Fetched ' + @n + ' message headers.'

    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @bundle
        RETURN
      END

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @bundle


END
GO