(SQL Server) Streaming Decrypt HTTP Response

If the an encrypted file was previously uploaded, this example demonstrates how to download and decrypt in a streaming way.

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

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

    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkatdownload.com', @port, @bTls, @bAutoReconnect

    -- Send the request to download a file.
    EXEC sp_OAMethod @rest, 'SendReqNoBody', @success OUT, 'GET', '/9.5.0.83/chilkat-9.5.0-python-3.8-x64.zip'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Read the response header.
    DECLARE @responseStatusCode int
    EXEC sp_OAMethod @rest, 'ReadResponseHeader', @responseStatusCode OUT
    IF @responseStatusCode < 0
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END


    PRINT 'Response status code = ' + @responseStatusCode

    -- We expect a 200 response status if the JPG data is coming.
    -- Otherwise, we'll get a string response body with an error message(or no response body).
    IF @responseStatusCode <> 200
      BEGIN
        DECLARE @errResponse nvarchar(4000)
        EXEC sp_OAMethod @rest, 'ReadRespBodyString', @errResponse OUT
        EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN
            EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
          END
        ELSE
          BEGIN

            PRINT @errResponse
          END
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- Create an object for decrypting.
    -- This example will use "none" for the encryption algorithm, which means it's just a pass-through.
    -- Typically you would set the decrypt properties, such as secret key, IV, key length, padding scheme, cipher mode..
    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', 'none'

    -- The following settings are ignored in this test because our CryptAlgorithm is "none".
    -- However, if you were truly decrypting, you might set CryptAlgorithm = "aes".
    EXEC sp_OASetProperty @crypt, 'CipherMode', 'cbc'
    EXEC sp_OASetProperty @crypt, 'KeyLength', 128
    EXEC sp_OAMethod @crypt, 'SetEncodedKey', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'
    EXEC sp_OAMethod @crypt, 'SetEncodedIV', NULL, '000102030405060708090A0B0C0D0E0F', 'hex'

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

    -- The decryptStream's source will be the receiveStream.
    DECLARE @decryptStream int
    -- Use "Chilkat_9_5_0.Stream" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Stream', @decryptStream OUT

    -- The decrypt stream's sink will be a file.
    EXEC sp_OASetProperty @decryptStream, 'SinkFile', 'qa_output/chilkat-9.5.0-python-3.8-x64.zip'
    EXEC sp_OAMethod @decryptStream, 'SetSourceStream', @success OUT, @receiveStream

    -- Create and run a background async task to decrypt incoming data and write to a file.
    DECLARE @decryptStreamTask int
    EXEC sp_OAMethod @crypt, 'DecryptStreamAsync', @decryptStreamTask OUT, @decryptStream
    EXEC sp_OAMethod @decryptStreamTask, 'Run', @success OUT

    EXEC sp_OAMethod @rest, 'ReadRespBodyStream', @success OUT, @receiveStream, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @crypt
        EXEC @hr = sp_OADestroy @receiveStream
        EXEC @hr = sp_OADestroy @decryptStream
        RETURN
      END


    PRINT 'Successfully received the .zip file.'

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @crypt
    EXEC @hr = sp_OADestroy @receiveStream
    EXEC @hr = sp_OADestroy @decryptStream


END
GO