(SQL Server) Get IMAP UID from Email Header

Demonstrates how to get the IMAP UID from an email header. After fetching an email or header using IMAP, the UID is contained in the ckx-imap-uid header field.

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
    -- 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.someMailServer.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, 'login', 'password'
    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

    DECLARE @fetchUids int
    SELECT @fetchUids = 1
    -- Get the message UIDs of all the emails in the mailbox

    DECLARE @messageSet int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT

    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'ALL', @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

    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

    -- The UID of each fetched email header is available 
    -- in the ckx-imap-uid header field.
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    DECLARE @msgSet1 int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @msgSet1 OUT

    DECLARE @i int
    SELECT @i = 0
    DECLARE @szBundle int
    EXEC sp_OAGetProperty @bundle, 'MessageCount', @szBundle OUT
    WHILE @i < @szBundle
      BEGIN
        EXEC sp_OAMethod @bundle, 'EmailAt', @success OUT, @i, @email

        -- Build a message set containing one UID
        DECLARE @uidStr nvarchar(4000)
        EXEC sp_OAMethod @email, 'GetHeaderField', @uidStr OUT, 'ckx-imap-uid'
        EXEC sp_OAMethod @msgSet1, 'FromCompactString', @success OUT, @uidStr

        -- Move this message to some other folder.
        EXEC sp_OAMethod @imap, 'MoveMessages', @success OUT, @msgSet1, 'someOtherFolder'
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            -- Exit the loop...
            SELECT @i = @szBundle
          END
        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
    EXEC @hr = sp_OADestroy @msgSet1


END
GO