(SQL Server) Controlling paths within a Zip

How to control the paths stored within a .zip.

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

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

    DECLARE @success int

    -- This is the directory structure on the local filesystem
    -- from which we'll create .zip archives:
    -- The root directory is /temp/filesToZip
    -- 
    -- We have these files:
    -- /temp/filesToZip/faxCover.doc
    -- /temp/filesToZip/exe/Setup.exe
    -- /temp/filesToZip/images/cheese.jpg
    -- /temp/filesToZip/images/dude.gif
    -- /temp/filesToZip/images/img3.gif
    -- /temp/filesToZip/images/img4.gif
    -- /temp/filesToZip/images/img5.gif
    -- /temp/filesToZip/images/scream.jpg
    -- /temp/filesToZip/images/imageInfo/scream.xml
    -- /temp/filesToZip/images/imageInfo/cheese.xml
    -- /temp/filesToZip/text/html/bonaireFishing.html
    -- /temp/filesToZip/text/html/upload.html
    -- /temp/filesToZip/text/txt/hello.txt
    -- /temp/filesToZip/text/txt/HelloWorld123.txt
    -- /temp/filesToZip/text/xml/hamlet.xml
    -- /temp/filesToZip/text/xml/pigs.xml

    -- There are three properties to help control the paths stored
    -- within a .zip:
    -- AppendFromDir
    -- PathPrefix
    -- DiscardPaths
    -- 

    -- First we'll demonstrate AppendFromDir
    -- When a directory tree is appended via AppendFiles or
    -- AppendFilesEx, the AppendFromDir sets the base of the 
    -- directory tree appended (if the file pattern contains a 
    -- relative path, or no path at all).

    -- Clear the zip object.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'test1.zip'

    DECLARE @recurse int
    SELECT @recurse = 1

    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.xml', @recurse

    -- The zip will contain:
    -- images/imageInfo/scream.xml
    -- images/imageInfo/cheese.xml
    -- text/xml/hamlet.xml
    -- text/xml/pigs.xml

    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Clear the zip object.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'test2.zip'

    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/images'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.xml', @recurse
    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/text'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.xml', @recurse

    -- The zip will contain:
    -- imageInfo/scream.xml
    -- imageInfo/cheese.xml
    -- xml/hamlet.xml
    -- xml/pigs.xml

    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- The PathPrefix property adds an arbitrary path prefix to each
    -- file in the .zip.
    -- For example:

    -- Clear the zip object.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'test3.zip'

    EXEC sp_OASetProperty @zip, 'PathPrefix', 'chilkat/'

    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/images'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.xml', @recurse
    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/text'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.xml', @recurse

    -- The zip will contain:
    -- chilkat/imageInfo/scream.xml
    -- chilkat/imageInfo/cheese.xml
    -- chilkat/xml/hamlet.xml
    -- chilkat/xml/pigs.xml

    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- The DiscardPaths property removes the path from each file
    -- in the zip:
    -- For example:

    -- Clear the zip object.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'test4.zip'

    EXEC sp_OASetProperty @zip, 'PathPrefix', ''
    EXEC sp_OASetProperty @zip, 'DiscardPaths', 1

    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*', @recurse

    -- The zip will contain:
    -- faxCover.doc
    -- Setup.exe
    -- cheese.jpg
    -- dude.gif
    -- img3.gif
    -- img4.gif
    -- img5.gif
    -- scream.jpg
    -- scream.xml
    -- cheese.xml
    -- bonaireFishing.html
    -- upload.html
    -- hello.txt
    -- HelloWorld123.txt
    -- hamlet.xml
    -- pigs.xml

    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- You can combine PathPrefix with DiscardPaths:

    -- Clear the zip object.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'test5.zip'

    EXEC sp_OASetProperty @zip, 'PathPrefix', 'chilkat/'
    EXEC sp_OASetProperty @zip, 'DiscardPaths', 1

    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*', @recurse

    -- The zip will contain:
    -- chilkat/faxCover.doc
    -- chilkat/Setup.exe
    -- chilkat/cheese.jpg
    -- chilkat/dude.gif
    -- chilkat/img3.gif
    -- chilkat/img4.gif
    -- chilkat/img5.gif
    -- chilkat/scream.jpg
    -- chilkat/scream.xml
    -- chilkat/cheese.xml
    -- chilkat/bonaireFishing.html
    -- chilkat/upload.html
    -- chilkat/hello.txt
    -- chilkat/HelloWorld123.txt
    -- chilkat/hamlet.xml
    -- chilkat/pigs.xml

    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- One last example -- combine DiscardPaths with PathPrefix
    -- with multiple calls to AppendFiles:

    -- Clear the zip object.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, 'test6.zip'

    EXEC sp_OASetProperty @zip, 'DiscardPaths', 1
    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp/filesToZip/'

    -- Get all .gif files:
    EXEC sp_OASetProperty @zip, 'PathPrefix', 'gif/'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.gif', @recurse

    -- Get all .jpg files:
    EXEC sp_OASetProperty @zip, 'PathPrefix', 'jpg/'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.jpg', @recurse

    -- Get all .xml files:
    EXEC sp_OASetProperty @zip, 'PathPrefix', 'xml/'
    EXEC sp_OAMethod @zip, 'AppendFiles', @success OUT, '*.xml', @recurse

    -- The zip will contain:
    -- jpg/cheese.jpg
    -- gif/dude.gif
    -- gif/img3.gif
    -- gif/img4.gif
    -- gif/img5.gif
    -- jpg/scream.jpg
    -- xml/scream.xml
    -- xml/cheese.xml
    -- xml/hamlet.xml
    -- xml/pigs.xml

    EXEC sp_OAMethod @zip, 'WriteZipAndClose', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    EXEC @hr = sp_OADestroy @zip


END
GO