(SQL Server) Move Messages from one Mailbox to Another

If your IMAP server supports the MOVE capability, then it is possible to move messages from one mailbox (folder) to another. This example demonstrates the MOVE command using the SendRawCommand method. The IMAP MOVE Extension is documented in RFC 6851.

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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @imap int
    -- Use "Chilkat_9_5_0.Imap" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use an implicit TLS connection.
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    DECLARE @success int
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'MY-IMAP-DOMAIN'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Authenticate
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'MY-IMAP-LOGIN', 'MY-IMAP-PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Get the list of capabilities:
    DECLARE @caps nvarchar(4000)
    EXEC sp_OAMethod @imap, 'Capability', @caps OUT

    PRINT 'Capabilities: ' + @caps

    -- Here is an example of the string returned:
    -- * CAPABILITY IMAP4rev1 UNSELECT IDLE NAMESPACE QUOTA ID XLIST CHILDREN X-GM-EXT-1 
    -- UIDPLUS COMPRESS=DEFLATE ENABLE MOVE CONDSTORE ESEARCH UTF8=ACCEPT APPENDLIMIT=35882577
    -- LIST-EXTENDED LIST-STATUS

    EXEC sp_OAMethod @imap, 'HasCapability', @iTmp0 OUT, 'MOVE', @caps
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'The IMAP server does not support the MOVE extension.'
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END


    PRINT 'Good, the MOVE extension is supported...'

    -- Select a mailbox, search for some messages to get a sequence-set (i.e. a 
    -- range of message sequence numbers or UIDs.  Then move these messages to another
    -- mailbox.

    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'old'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Get a set of message sequence numbers for all emails with "Gencer" in the FROM name/address.
    DECLARE @bReturnUids int
    SELECT @bReturnUids = 0
    DECLARE @msgSet int
    EXEC sp_OAMethod @imap, 'Search', @msgSet OUT, 'FROM Gencer', @bReturnUids
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- The message set, as a compact string, will look something like this:
    -- 1572,1876:1881,1883,1886,1895,1905:1906,1910:1911,1923,1959:1963
    DECLARE @sequenceSet nvarchar(4000)
    EXEC sp_OAMethod @msgSet, 'ToCompactString', @sequenceSet OUT

    PRINT @sequenceSet

    -- Let's form our MOVE command.
    DECLARE @sbMoveCmd int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbMoveCmd OUT

    EXEC sp_OAMethod @sbMoveCmd, 'Append', @success OUT, 'MOVE '
    EXEC sp_OAMethod @sbMoveCmd, 'Append', @success OUT, @sequenceSet
    EXEC sp_OAMethod @sbMoveCmd, 'Append', @success OUT, ' old/gencer'

    EXEC sp_OAMethod @sbMoveCmd, 'GetAsString', @sTmp0 OUT
    PRINT 'Sending: ' + @sTmp0

    DECLARE @cmdResponse nvarchar(4000)
    EXEC sp_OAMethod @sbMoveCmd, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @imap, 'SendRawCommand', @cmdResponse OUT, @sTmp0
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @sbMoveCmd
        RETURN
      END


    PRINT @cmdResponse

    -- The response looks like this:
    -- 	* 1572 EXPUNGE
    -- 	* 1875 EXPUNGE
    -- 	* 1875 EXPUNGE
    -- 	* 1875 EXPUNGE
    -- 	* 1875 EXPUNGE
    -- 	* 1875 EXPUNGE
    -- 	* 1875 EXPUNGE
    -- 	* 1876 EXPUNGE
    -- 	* 1878 EXPUNGE
    -- 	* 1886 EXPUNGE
    -- 	* 1895 EXPUNGE
    -- 	* 1895 EXPUNGE
    -- 	* 1898 EXPUNGE
    -- 	* 1898 EXPUNGE
    -- 	* 1909 EXPUNGE
    -- 	* 1944 EXPUNGE
    -- 	* 1944 EXPUNGE
    -- 	* 1944 EXPUNGE
    -- 	* 1944 EXPUNGE
    -- 	* 1944 EXPUNGE
    -- 	* 2274 EXISTS
    -- 	aaaf OK [COPYUID 62 1582,1886:1891,1893,1896,1905,1915:1916,1920:1921,1933,1974:1978 20,17,16,15,14,13,12,10:11,9,19,18,8,7,6,5,4,3,2,1] (Success)

    -- The last line should indicate OK.
    -- Note: Starting in Chilkat v9.5.0.62, the Chilkat will add a MoveMessages method to make this easier.
    -- Until then, or if using an older version of Chilkat, the response will need to be parsed for "OK" or "BAD".

    EXEC @hr = sp_OADestroy @msgSet

    -- Disconnect from the IMAP server.
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT


    PRINT 'All Done.'

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @sbMoveCmd


END
GO