(SQL Server) Streaming Compression --> Streaming Encryption

Demonstrate how to feed a compression stream into an encryption stream. This example does the following:

Data File --> Compress --> Encrypt --> Application Reads

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
    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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Stream', @streamC OUT

    -- streamE is for encryption
    DECLARE @streamE int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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

    -- Our application will be reading the output of streamE.
    -- 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

    -- Read the compressed and encrypted bytes.
    DECLARE @outData int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @outData OUT

    EXEC sp_OAGetProperty @streamE, 'EndOfStream', @iTmp0 OUT
    WHILE (@iTmp0 <> 1)
      BEGIN
        EXEC sp_OAGetProperty @streamE, 'DataAvailable', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN
            EXEC sp_OAMethod @streamE, 'ReadBd', @success OUT, @outData
          END
      END

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

    -- Get any remaining data that needs to be flushed.
    EXEC sp_OAGetProperty @streamE, 'DataAvailable', @iTmp0 OUT
    IF @iTmp0 = 1
      BEGIN
        EXEC sp_OAMethod @streamE, 'ReadBd', @success OUT, @outData
      END

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

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

    -- Save the compressed/encrypted data to a file.
    EXEC sp_OAMethod @outData, 'WriteFile', @success OUT, 'qa_output/compressedEncrypted.dat'


    PRINT 'The async compress/encrypt was successful.'

    -- Let's decrypt and decompress to further verify...
    EXEC sp_OAMethod @crypt, 'DecryptBd', @success OUT, @outData
    EXEC sp_OAMethod @compress, 'DecompressBd', @success OUT, @outData

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

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


END
GO