(SQL Server) Replacment for Deprecated Crypt2 Compress Functions

The Chilkat Crypt2 compression functions are deprecated and will be removed. This example shows how to duplicate the functionality using the Chilkat Compression class.

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 @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

    -- Create data to be compressed.
    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 < 25
      BEGIN
        DECLARE @success int
        EXEC sp_OAMethod @sb, 'Append', @success OUT, 'Hello World, this is a test.' + CHAR(13) + CHAR(10)
        SELECT @i = @i + 1
      END

    -- Bzip2 compress the utf-8 byte representation of the string and return the compressed data as base64.
    EXEC sp_OASetProperty @crypt, 'Charset', 'utf-8'
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'base64'
    DECLARE @compressedStr nvarchar(4000)
    EXEC sp_OAMethod @sb, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @crypt, 'CompressStringENC', @compressedStr OUT, @sTmp0


    PRINT @compressedStr

    -- Result: 4aeUs+4CAABCWmgzMUFZJlNZZ6znBgAAfNeAABJABQBAAIAmZJwAIABwUNNMAAUqoekYjMp4ixF1FlFxF8i5i1FvFwizF9iwi9RdxaizF7RZi6i0ixF3Fui/i7kinChIM9ZzgwA=

    -- The result contains an 8-byte header composed of a 4-byte magic number (0xB394A7E1) and a 4-byte length.
    -- Do the following to BZip2 decompress using Chilkat Compression
    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 8-byte header.
    EXEC sp_OAMethod @bd, 'RemoveChunk', @success OUT, 0, 8

    -- Decompress
    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_OAMethod @compress, 'DecompressBd', @success OUT, @bd

    -- Get the decompressed string.
    DECLARE @decompressedStr nvarchar(4000)
    EXEC sp_OAMethod @bd, 'GetString', @decompressedStr OUT, 'utf-8'

    PRINT @decompressedStr

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


END
GO