(SQL Server) Crypt2 Compression Example

The Chilkat Crypt2 class include a few methods for compression and decompression (inflate). These are legacy method that existed long before the Chilkat.Compression class was first introduced. These methods should be avoided in favor of using the Chilkat.Compression methods. The reason is that they are limited to bzip2, and the compressed output includes an 8-byte header that is composed of a magic number (0xB394A7E1) and the size of the original uncompressed data (4 bytes and thus limited to 4GB).

This example demonstrates compression and decompression, and also how to compress using Crypt2 and decompress using Chilkat.Compression. The Chilkat.Compression class does not emit any header, and supports other compression methods.

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

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

    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    DECLARE @i int
    SELECT @i = 0
    WHILE @i < 10
      BEGIN
        DECLARE @success int
        EXEC sp_OAMethod @sb, 'AppendInt', @success OUT, @i
        EXEC sp_OAMethod @sb, 'Append', @success OUT, ' the quick brown fox jumps over the crazy dog' + CHAR(13) + CHAR(10)
        SELECT @i = @i + 1
      END


    PRINT 'Before compression:'
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    -- Before compression:
    -- 0 the quick brown fox jumps over the crazy dog
    -- 1 the quick brown fox jumps over the crazy dog
    -- 2 the quick brown fox jumps over the crazy dog
    -- 3 the quick brown fox jumps over the crazy dog
    -- 4 the quick brown fox jumps over the crazy dog
    -- 5 the quick brown fox jumps over the crazy dog
    -- 6 the quick brown fox jumps over the crazy dog
    -- 7 the quick brown fox jumps over the crazy dog
    -- 8 the quick brown fox jumps over the crazy dog
    -- 9 the quick brown fox jumps over the crazy dog

    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    -- Compress the utf-8 representation of the string.
    -- Note: Choosing "Unicode" or "utf-16" is a poor choice for text that is mostly us-ascii.
    -- The utf-8 representation will result in a smaller size.
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
    DECLARE @compressedStr nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'CompressStringENC', @compressedStr OUT, @sTmp0


    PRINT 'After compression:'

    PRINT @compressedStr

    -- After compression:
    -- 4aeUs+ABAABCWmgzMUFZJlNZT/+H9wAAN1mAABJAAH/gP/v/8CAAkDGExNBgjEMjCYFKqGoAaDygNDamyTYmKe6Ypqn0mqZpgmaZd/hv8fLz9McsU7kyTgmwnAmibU4pyJom5PxNqapxTBO1M05JuT4TrT+TRMCapqm9VzTmn+LuSKcKEgn/8P7g

    -- Note: The compressed data includes an 8-byte header composed of a magic number (0xB394A7E1) and the original data byte count.
    -- Therefore, the Crypt2 compress/inflate methods are considered legacy and should only be used for data already
    -- compressed with these methods.  The Chilkat.Compression class is generalized, emits no headers, and can interoperate with
    -- with other software systems without trouble.  

    -- If we re-encode from base64 to hex, then we can see the header:
    EXEC sp_OAMethod @crypt, 'ReEncode', @sTmp0 OUT, @compressedStr, 'base64', 'hex'
    PRINT @sTmp0

    -- The hex compressed string is:
    -- E1A794B3E0010000425A68333141592653594...
    -- We can see the 8-byte header in the hex:  magic number = 0xE1A794B3E (little-endian), and byte count = 0xE0010000 (little-endian)

    DECLARE @originalStr nvarchar(4000)
    EXEC sp_OAMethod @crypt, 'InflateStringENC', @originalStr OUT, @compressedStr

    PRINT 'After inflate:'

    PRINT @originalStr

    -- ---------------------------------------------------------------------------
    -- To inflate with Chilkat.Compression:

    DECLARE @compress int
    -- Use "Chilkat_9_5_0.Compression" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Compression', @compress OUT

    EXEC sp_OASetProperty @compress, 'Algorithm', 'bzip2'
    EXEC sp_OASetProperty @compress, 'Charset', 'utf-8'
    EXEC sp_OASetProperty @compress, 'EncodingMode', 'base64'

    DECLARE @bd int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT

    EXEC sp_OAMethod @bd, 'AppendEncoded', @success OUT, @compressedStr, 'base64'
    -- Remove the 1st 8 bytes.
    EXEC sp_OAMethod @bd, 'RemoveChunk', @success OUT, 0, 8

    EXEC sp_OAMethod @compress, 'DecompressBd', @success OUT, @bd


    PRINT 'After decompression:'
    EXEC sp_OAMethod @bd, 'GetString', @sTmp0 OUT, 'utf-8'
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @compress
    EXEC @hr = sp_OADestroy @bd


END
GO