SQL Server

Get IMAP Message Threads as JSON

Demonstrates the Chilkat Imap.QueryThread method, which sends the IMAP THREAD command for the selected mailbox and returns the thread hierarchy as JSON. The first argument is the threading algorithm (commonly REFERENCES or ORDEREDSUBJECT), the second is IMAP search criteria, and the third (bUid) selects UID vs sequence-number identifiers. The returned JSON has a top-level threads array whose nested arrays encode parent/child relationships. This example threads all messages in the Inbox.

Tip: JSON parsing code for this result can be generated at Chilkat Tools.

Background: "Conversation view" in a mail client — grouping a message with its replies and follow-ups — is exactly what the IMAP THREAD extension provides, computed on the server. REFERENCES threads by the References/In-Reply-To headers (the accurate way), while ORDEREDSUBJECT groups by subject. Not all servers support THREAD, so check HasCapability for it first.

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.QueryThread method, which sends the IMAP THREAD command for the
    --  selected mailbox and returns the thread hierarchy as JSON.  The 1st argument is the
    --  threading algorithm (such as REFERENCES or ORDEREDSUBJECT), the 2nd is IMAP search
    --  criteria, and the 3rd (bUid) selects UID vs sequence-number identifiers.

    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

    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

    --  Group all messages into threads using the REFERENCES algorithm, returning UIDs.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @imap, 'QueryThread', @success OUT, 'REFERENCES', 'ALL', 1, @json
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    --  JSON parsing code for this result can be generated at Chilkat's online tool:
    --  https://tools.chilkat.io/jsonParse

    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 @json
        RETURN
      END

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @json


END
GO