(SQL Server) Compress and Decompress Base64

Imagine we have data represented as a base64 string. This example demonstrates how to decode, compress, and re-encode to smaller base64 representing the compressed data.

Note: This example requires Chilkat v9.5.0.66 or greater.

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

    DECLARE @success int

    DECLARE @strBase64 nvarchar(4000)
    SELECT @strBase64 = 'VGhlIHF1aWNrIGJyb3duIGZveCBqdW1wZWQgb3ZlciB0aGUgbGF6eSBkb2cuDQpUaGUgcXVpY2sgYnJvd24gZm94IGp1bXBlZCBvdmVyIHRoZSBsYXp5IGRvZy4NClRoZSBxdWljayBicm93biBmb3gganVtcGVkIG92ZXIgdGhlIGxhenkgZG9nLg0KVGhlIHF1aWNrIGJyb3duIGZveCBqdW1wZWQgb3ZlciB0aGUgbGF6eSBkb2cuDQpUaGUgcXVpY2sgYnJvd24gZm94IGp1bXBlZCBvdmVyIHRoZSBsYXp5IGRvZy4NCg0K'

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

    EXEC sp_OASetProperty @compress, 'Algorithm', 'deflate'

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

    -- Load the base64 data into a BinData object.
    -- This decodes the base64. The decoded bytes will be contained in the BinData.
    EXEC sp_OAMethod @binDat, 'AppendEncoded', @success OUT, @strBase64, 'base64'

    -- Compress the BinData.
    EXEC sp_OAMethod @compress, 'CompressBd', @success OUT, @binDat

    -- Get the compressed data in base64 format:
    DECLARE @compressedBase64 nvarchar(4000)
    EXEC sp_OAMethod @binDat, 'GetEncoded', @compressedBase64 OUT, 'base64'

    PRINT 'compressed base64:'

    PRINT @compressedBase64

    -- The compressed base64 is: C8lIVSgszUzOVkgqyi/PU0jLr1DIKs0tSE1RyC9LLVIoAcrnJFZVKqTkp+vxcoUMYeW8XAA=

    -- Now decompress:
    EXEC sp_OAMethod @binDat, 'Clear', @success OUT
    EXEC sp_OAMethod @binDat, 'AppendEncoded', @success OUT, @compressedBase64, 'base64'
    EXEC sp_OAMethod @compress, 'DecompressBd', @success OUT, @binDat

    DECLARE @decompressedBase64 nvarchar(4000)
    EXEC sp_OAMethod @binDat, 'GetEncoded', @decompressedBase64 OUT, 'base64'

    PRINT 'decompressed base64:'

    PRINT @decompressedBase64

    -- The output is the original data:
    -- VGhlIHF1aWNrIGJyb3duIGZveCBqdW1wZWQgb3ZlciB0aGUgbGF6eSBkb2cuDQpUaGUgcXVpY2sgYnJvd24gZm94IGp1bXBlZCBvdmVyIHRoZSBsYXp5IGRvZy4NClRoZSBxdWljayBicm93biBmb3gganVtcGVkIG92ZXIgdGhlIGxhenkgZG9nLg0KVGhlIHF1aWNrIGJyb3duIGZveCBqdW1wZWQgb3ZlciB0aGUgbGF6eSBkb2cuDQpUaGUgcXVpY2sgYnJvd24gZm94IGp1bXBlZCBvdmVyIHRoZSBsYXp5IGRvZy4NCg0K

    EXEC @hr = sp_OADestroy @compress
    EXEC @hr = sp_OADestroy @binDat


END
GO