(SQL Server) Stream a file to compression to encryption to an output file.

Runs a chain of streams to read a source file, compress, encrypt, and write an output file.

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

    DECLARE @success int

    -- Initialize the compression object.
    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'

    -- Initialize the encryption object.
    DECLARE @crypt int
    -- Use "Chilkat_9_5_0.Crypt2" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @crypt OUT

    EXEC sp_OASetProperty @crypt, 'CryptAlgorithm', 'chacha20'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 256
    EXEC sp_OASetProperty @crypt, 'EncodingMode', 'hex'
    DECLARE @ivHex nvarchar(4000)
    SELECT @ivHex = '000000000000000000000002'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, @ivHex, 'hex'
    EXEC sp_OASetProperty @crypt, 'InitialCount', 42
    DECLARE @keyHex nvarchar(4000)
    SELECT @keyHex = '1c9240a5eb55d38af333888604f6b5f0473917c1402b80099dca5cbc207075c0'
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, @keyHex, 'hex'

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

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

    -- The source for the compressor is a file.
    EXEC sp_OASetProperty @streamC, 'SourceFile', 'qa_data/hamlet.xml'
    -- The source for the encryptor is the output of the compressor stream.
    EXEC sp_OAMethod @streamE, 'SetSourceStream', @success OUT, @streamC

    -- The sink for the encryptor is the output file.
    EXEC sp_OASetProperty @streamE, 'SinkFile', 'qa_output/compressed_encrypted.dat'

    -- Start the compression and encryption streams in background threads..
    DECLARE @taskC int
    EXEC sp_OAMethod @compress, 'CompressStreamAsync', @taskC OUT, @streamC
    DECLARE @taskE int
    EXEC sp_OAMethod @crypt, 'EncryptStreamAsync', @taskE OUT, @streamE
    EXEC sp_OAMethod @taskC, 'Run', @success OUT
    EXEC sp_OAMethod @taskE, 'Run', @success OUT

    -- Let the streams run until finished.
    -- Let's make sure the background task finished.
    -- It should already be the case that the task is finished.
    EXEC sp_OAGetProperty @taskE, 'Finished', @iTmp0 OUT
    EXEC sp_OAGetProperty @taskC, 'Finished', @iTmp1 OUT
    WHILE ((@iTmp0 <> 1) or (@iTmp1 <> 1))
      BEGIN
        EXEC sp_OAMethod @taskE, 'SleepMs', NULL, 20
      END

    -- check for success..
    EXEC sp_OAGetProperty @taskE, 'TaskSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

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

    -- check for success..
    EXEC sp_OAGetProperty @taskC, 'TaskSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN

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

    EXEC @hr = sp_OADestroy @taskE

    EXEC @hr = sp_OADestroy @taskC

    -- Load the file that was produced and decrypt/decompress to verify.
    DECLARE @binData int
    -- Use "Chilkat_9_5_0.BinData" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @binData OUT

    EXEC sp_OAMethod @binData, 'LoadFile', @success OUT, 'qa_output/compressed_encrypted.dat'
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @binData
    EXEC sp_OAMethod @compress, 'DecompressBd', @success OUT, @binData

    -- binData should contain the original data..
    EXEC sp_OAMethod @binData, 'WriteFile', @success OUT, 'qa_output/original_hamlet.xml'


    PRINT 'Finished.'

    EXEC @hr = sp_OADestroy @compress
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @streamC
    EXEC @hr = sp_OADestroy @streamE
    EXEC @hr = sp_OADestroy @binData


END
GO