Unzip Encrypted Text into a String Variable

Demonstrates how to open an encrypted .zip archive and unzip a text file directly into a string variable.

Download Chilkat 32-bit Zip ActiveX (.msi) (includes objects for .zip, .gz, .bz2, and .Z)

Download All 32-bit Chilkat ActiveX Components (.zip)

Download All 64-bit Chilkat ActiveX Components (.zip)

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @zip int
    EXEC @hr = sp_OACreate 'Chilkat.Zip2', @zip OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  Any string unlocks the component for the 1st 30-days.
    EXEC sp_OAMethod @zip, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Set the password required for decrypting.
    EXEC sp_OASetProperty @zip, 'DecryptPassword', 'myPassword'

    EXEC sp_OAMethod @zip, 'OpenZip', @success OUT, 'encrypted.zip'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Locate the file within the Zip to be unzipped into a string variable:
    DECLARE @entry int

    EXEC sp_OAMethod @zip, 'FirstMatchingEntry', @entry OUT, '*.csv'
    IF Not (@entry Is NULL )
      BEGIN

        --  There are a few different InflateToString methods.
        --  InflateToString allows you to control line-endings LF-only vs. CRLF
        --  InflateToString2 returns the line-endings unmodified.
        --  Both InflateToString and InflateToString2 assume the
        --  data consists of ANSI characters.
        --  The InflateToString3 method allows for text stored in different
        --  character encodings to be returned as a string

        --  This example uses InflateToString2 -- line endings are
        --  unmodified and the text is simple ANSI...
        DECLARE @strCsv nvarchar(4000)

        EXEC sp_OAMethod @entry, 'InflateToString2', @strCsv OUT

        PRINT @strCsv

      END
    ELSE
      BEGIN

        PRINT 'No matching entry found!'
      END


END
GO