SQL Server

Generate a Unique Key for an Email

Demonstrates how to generate a unique key for an email. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

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

    EXEC sp_OAMethod @email, 'LoadEml', @success OUT, 'qa_data/eml/someEmail.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    --  The ComputeGlobalKey method computes a global unique key for an email that may be used as a 
    --  key for a relational database table (or anything else). 
    --  The key is created by a digest-MD5 hash of the concatenation of the following header fields: 
    --  Message-ID, Subject, From, Date, To. (The header fields are Q/B decoded if necessary, 
    --  converted to the utf-8 encoding, concatenated, and hashed using MD5.) 
    --  The 16-byte MD5 hash is returned as an encoded string (such as base64, url, hex, etc.)
    --  If the bFold argument is true, then the 16-byte MD5 hash is folded to 8 bytes with an XOR to produce a shorter key.

    DECLARE @bFold int
    SELECT @bFold = 0
    DECLARE @key nvarchar(4000)
    EXEC sp_OAMethod @email, 'ComputeGlobalKey2', @key OUT, 'hex', @bFold

    PRINT 'key: ' + @key

    EXEC @hr = sp_OADestroy @email


END
GO