(SQL Server) Create a ZIP Archive with a Top-Level Directory Using PathPrefix

This example demonstrates how to use the PathPrefix property to ensure that all files in a ZIP archive are stored beneath a common top-level directory.

The example recursively adds files from:

c:/zipTest/directory_name

And stores them in the ZIP archive beneath:

directory_name/

For example, the local filesystem file:

c:/zipTest/directory_name/hello.txt

Will be stored in the ZIP archive as:

directory_name/hello.txt

This is useful when creating ZIP archives that should extract into a single parent directory rather than directly into the current extraction location.

The example also demonstrates that methods such as AppendFiles initially add references to filesystem files, and that the actual file reading and compression occur later when WriteZipAndClose is called.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    DECLARE @zip int
    EXEC @hr = sp_OACreate 'Chilkat.Zip', @zip OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Initialize the Zip object and set the output filename.
    -- The .zip file is not created until WriteZip or WriteZipAndClose is called.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'qa_output/test.zip'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Prepend "directory_name/" to each ZIP entry added after this point.
    -- 
    -- For example, a file found at:
    -- 
    --     c:/zipTest/directory_name/hello.txt
    -- 
    -- Will be stored in the ZIP as:
    -- 
    --     directory_name/hello.txt
    -- 
    -- This is useful when you want the files to unzip beneath a specific
    -- top-level directory.
    EXEC sp_OASetProperty @zip, 'PathPrefix', 'directory_name/'

    -- Recursively add references to all files beneath:
    -- 
    --     c:/zipTest/directory_name
    -- 
    -- AppendFiles does not immediately read or compress the files.
    -- It adds references to files in the local filesystem.
    -- The referenced files are read and compressed when WriteZipAndClose is called.
    DECLARE @recurse int
    SELECT @recurse = 1

    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, 'c:/zipTest/directory_name/*', @recurse
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Write the ZIP archive and close it.
    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @zip


END
GO