(SQL Server) Streaming Compression

Compress and decompress using a stream.

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

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

    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', 'deflate'

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

    -- This example sets the source and sink of the stream to files.
    -- A stream can have use other streams as a source or sink,
    -- or the application can itself be the source/sink by directly
    -- reading or writing a stream.  (See below for an example of this..)
    EXEC sp_OASetProperty @streamC, 'SourceFile', 'qa_data/hamlet.xml'
    EXEC sp_OASetProperty @streamC, 'SinkFile', 'qa_output/hamlet_compressed.dat'

    -- Compress from source to sink.
    DECLARE @success int
    EXEC sp_OAMethod @compress, 'CompressStream', @success OUT, @streamC
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @streamC
        RETURN
      END


    PRINT 'File-to-file deflate compression successful.'
    -- Note: The FileSize method returns a signed 32-bit integer.  If the file is potentially larger than 2GB, call FileSizeStr instead to return
    -- the size of the file as a string, then convert to an integer value.

    EXEC sp_OAGetProperty @streamC, 'SourceFile', @sTmp0 OUT
    EXEC sp_OAMethod @fac, 'FileSize', @iTmp0 OUT, @sTmp0
    PRINT 'Original size = ' + @iTmp0

    EXEC sp_OAGetProperty @streamC, 'SinkFile', @sTmp0 OUT
    EXEC sp_OAMethod @fac, 'FileSize', @iTmp0 OUT, @sTmp0
    PRINT 'Compressed size = ' + @iTmp0

    -- Now do file-to-file decompression
    DECLARE @streamD int
    -- Use "Chilkat_9_5_0.Stream" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Stream', @streamD OUT

    EXEC sp_OASetProperty @streamD, 'SourceFile', 'qa_output/hamlet_compressed.dat'
    EXEC sp_OASetProperty @streamD, 'SinkFile', 'qa_output/hamlet_restored.xml'

    -- Decompress from source to sink.
    EXEC sp_OAMethod @compress, 'DecompressStream', @success OUT, @streamD
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @compress, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @compress
        EXEC @hr = sp_OADestroy @streamC
        EXEC @hr = sp_OADestroy @streamD
        RETURN
      END


    PRINT 'File-to-file deflate decompression successful.'

    -- Let's double-check to see that the files are equal in size and content:

    DECLARE @bFilesEqual int
    EXEC sp_OAGetProperty @streamC, 'SourceFile', @sTmp0 OUT
    EXEC sp_OAGetProperty @streamD, 'SinkFile', @sTmp1 OUT
    EXEC sp_OAMethod @fac, 'FileContentsEqual', @bFilesEqual OUT, @sTmp0, @sTmp1
    IF @bFilesEqual <> 1
      BEGIN

        PRINT 'The output file is not equal to the input file!'
      END
    ELSE
      BEGIN

        PRINT 'The file was successfully compressed and decompressed.'
      END

    -- ---------------------------------------------------------------------
    -- Now let's decompress again, but this time w/ the application reading
    -- the decompressed data directly from a stream.
    DECLARE @streamA int
    -- Use "Chilkat_9_5_0.Stream" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Stream', @streamA OUT

    EXEC sp_OASetProperty @streamA, 'SourceFile', 'qa_output/hamlet_compressed.dat'

    -- Start decompressing in a background thread.
    DECLARE @task int
    EXEC sp_OAMethod @compress, 'DecompressStreamAsync', @task OUT, @streamA
    EXEC sp_OAMethod @task, 'Run', @success OUT

    -- Read decompressed data from streamA:
    DECLARE @decompressedText nvarchar(4000)

    EXEC sp_OAGetProperty @streamA, 'EndOfStream', @iTmp0 OUT
    WHILE (@iTmp0 <> 1)
      BEGIN
        EXEC sp_OAGetProperty @streamA, 'DataAvailable', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @streamA, 'ReadString', @decompressedText OUT

            PRINT @decompressedText
          END
      END

    -- Let's make sure the background task finished and that the decompress was successful.
    -- It should already be the case that the task is finished.
    EXEC sp_OAGetProperty @task, 'Finished', @iTmp0 OUT
    WHILE (@iTmp0 <> 1)
      BEGIN
        EXEC sp_OAMethod @task, 'SleepMs', NULL, 20
      END

    -- The decompressor may have finished, but it is possible that data
    -- remains to be flushed.  
    EXEC sp_OAGetProperty @streamA, 'DataAvailable', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @streamA, 'ReadString', @decompressedText OUT

        PRINT @decompressedText
      END

    -- Did streamA succeed in reading the entire file?
    EXEC sp_OAGetProperty @task, 'TaskSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

        PRINT 'async decompress failed:'
        EXEC sp_OAGetProperty @task, 'ResultErrorText', @sTmp0 OUT
        PRINT @sTmp0
        SELECT @success = 0
      END


    PRINT 'The async decompress was successful.'

    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @compress
    EXEC @hr = sp_OADestroy @streamC
    EXEC @hr = sp_OADestroy @streamD
    EXEC @hr = sp_OADestroy @streamA


END
GO