(SQL Server) Compressing StringBuilder Data Using CompressSb (Single Call and Chunked)

See more Compression Examples

1. Single-call compression, where the entire input is compressed in one operation.
2. Chunked compression, where the input is split into multiple parts and processed sequentially using the FirstChunk and LastChunk properties.

The example shows how compressed output is appended to a BinData object, and how the result can be verified by decompressing back to the original text. This is useful for both simple use cases and scenarios where data is processed incrementally (such as streaming or large inputs).

Key Points

• CompressSb converts text to bytes using the Charset property (UTF-8 recommended).
• Compressed data is appended to a BinData object.
• When both FirstChunk and LastChunk are true, compression is done in a single call.
• For chunked processing:
  • First chunk → FirstChunk = true, LastChunk = false
  • Middle chunks → both false
  • Final chunk → LastChunk = true
• Chunked compression is useful when processing data incrementally.

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 @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API has already been unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @compress int
    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', 'zlib'

    -- ================================================================
    -- 1) Single-call compression (entire data in one call)
    -- ================================================================

    DECLARE @sb int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT

    EXEC sp_OAMethod @sb, 'Append', @success OUT, 'The quick brown fox jumps over the lazy dog. '
    EXEC sp_OAMethod @sb, 'Append', @success OUT, 'This is a simple example using CompressSb.'

    DECLARE @bdCompressed int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdCompressed OUT

    -- When both FirstChunk and LastChunk are true (the defaults),
    -- the entire compression happens in a single call.
    EXEC sp_OASetProperty @compress, 'FirstChunk', 1
    EXEC sp_OASetProperty @compress, 'LastChunk', 1

    EXEC sp_OAMethod @compress, 'CompressSb', @success OUT, @sb, @bdCompressed
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @bdCompressed
        RETURN
      END

    DECLARE @compressedBase64 nvarchar(4000)
    EXEC sp_OAMethod @bdCompressed, 'GetEncoded', @compressedBase64 OUT, 'base64'


    PRINT 'Single-call compressed (base64):'

    PRINT @compressedBase64

    -- ================================================================
    -- 2) Chunked compression using FirstChunk / LastChunk
    -- ================================================================

    DECLARE @bdChunkedOut int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bdChunkedOut OUT

    -- First chunk
    EXEC sp_OASetProperty @compress, 'FirstChunk', 1
    EXEC sp_OASetProperty @compress, 'LastChunk', 0

    DECLARE @sbPart int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPart OUT

    EXEC sp_OAMethod @sbPart, 'Append', @success OUT, 'The quick brown fox '

    EXEC sp_OAMethod @compress, 'CompressSb', @success OUT, @sbPart, @bdChunkedOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @bdCompressed
        EXEC @hr = sp_OADestroy @bdChunkedOut
        EXEC @hr = sp_OADestroy @sbPart
        RETURN
      END

    -- Middle chunk
    EXEC sp_OASetProperty @compress, 'FirstChunk', 0
    EXEC sp_OASetProperty @compress, 'LastChunk', 0

    EXEC sp_OAMethod @sbPart, 'Clear', NULL
    EXEC sp_OAMethod @sbPart, 'Append', @success OUT, 'jumps over the lazy dog. '

    EXEC sp_OAMethod @compress, 'CompressSb', @success OUT, @sbPart, @bdChunkedOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @bdCompressed
        EXEC @hr = sp_OADestroy @bdChunkedOut
        EXEC @hr = sp_OADestroy @sbPart
        RETURN
      END

    -- Final chunk
    EXEC sp_OASetProperty @compress, 'FirstChunk', 0
    EXEC sp_OASetProperty @compress, 'LastChunk', 1

    EXEC sp_OAMethod @sbPart, 'Clear', NULL
    EXEC sp_OAMethod @sbPart, 'Append', @success OUT, 'This is a chunked CompressSb example.'

    EXEC sp_OAMethod @compress, 'CompressSb', @success OUT, @sbPart, @bdChunkedOut
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @bdCompressed
        EXEC @hr = sp_OADestroy @bdChunkedOut
        EXEC @hr = sp_OADestroy @sbPart
        RETURN
      END

    DECLARE @chunkedBase64 nvarchar(4000)
    EXEC sp_OAMethod @bdChunkedOut, 'GetEncoded', @chunkedBase64 OUT, 'base64'


    PRINT 'Chunked compressed (base64):'

    PRINT @chunkedBase64

    -- ================================================================
    -- 3) Decompress to verify correctness
    -- ================================================================

    DECLARE @sbDecompressed int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbDecompressed OUT

    -- Decompress in a single call (entire data already assembled)
    EXEC sp_OASetProperty @compress, 'FirstChunk', 1
    EXEC sp_OASetProperty @compress, 'LastChunk', 1

    EXEC sp_OAMethod @compress, 'DecompressSb', @success OUT, @bdChunkedOut, @sbDecompressed
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @sb
        EXEC @hr = sp_OADestroy @bdCompressed
        EXEC @hr = sp_OADestroy @bdChunkedOut
        EXEC @hr = sp_OADestroy @sbPart
        EXEC @hr = sp_OADestroy @sbDecompressed
        RETURN
      END


    PRINT 'Decompressed text:'
    EXEC sp_OAMethod @sbDecompressed, 'GetAsString', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @compress
    EXEC @hr = sp_OADestroy @sb
    EXEC @hr = sp_OADestroy @bdCompressed
    EXEC @hr = sp_OADestroy @bdChunkedOut
    EXEC @hr = sp_OADestroy @sbPart
    EXEC @hr = sp_OADestroy @sbDecompressed


END
GO