SQL Server

Control Stored ZIP Paths Using AppendFromDir and saveExtraPath

See more Zip Examples

This example demonstrates how the AppendFromDir property and the saveExtraPath argument work together to determine the stored paths of files within a ZIP archive.

The example adds the same local filesystem file three times, but stores it in the ZIP archive using three different paths.

The local filesystem file is:

/temp/a/hamlet.xml

The resulting ZIP archive contains:

hamlet.xml
a/hamlet.xml
temp/a/hamlet.xml

The example demonstrates:

• How saveExtraPath controls whether path information is stored in the ZIP archive
• How AppendFromDir establishes the base directory used for relative file paths
• How the same filesystem file can be stored in a ZIP archive using different relative paths
• That methods such as AddFile initially add only references to filesystem files, and that the actual file reading and compression occur later when WriteZipAndClose is called

The example also illustrates an important rule: when AppendFromDir is set, the filename passed to AddFile should normally be a relative path rather than a fully qualified absolute path.

SQL Server

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

    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, 'myZip.zip'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- This example adds the same local filesystem file three times,
    -- each time using a different stored path in the ZIP archive.
    -- 
    -- The local file is:
    -- 
    --     /temp/a/hamlet.xml
    -- 
    -- The ZIP will contain:
    -- 
    --     hamlet.xml
    --     a/hamlet.xml
    --     temp/a/hamlet.xml
    -- 

    -- ------------------------------------------------------------
    -- Case 1:
    -- Add the file using only the filename.
    -- 
    -- AddFile adds a reference to the file in the local filesystem.
    -- The file is not read or compressed at this point.  The referenced
    -- file is consumed later when WriteZipAndClose is called.
    DECLARE @saveExtraPath int
    SELECT @saveExtraPath = 0

    EXEC sp_OAMethod @zip, 'AddFile', @success OUT, '/temp/a/hamlet.xml', @saveExtraPath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Because saveExtraPath = 0, only the filename is stored
    -- in the ZIP archive:
    -- 
    --     hamlet.xml
    -- 

    -- ------------------------------------------------------------
    -- Case 2:
    -- Store the path "a/hamlet.xml" in the ZIP archive.
    -- 
    -- AppendFromDir specifies the base directory for relative file paths.
    -- The AppendFromDir path itself is not stored in the ZIP.
    -- 
    -- Because AppendFromDir is set to "/temp", the relative path
    -- "a/hamlet.xml" refers to the local filesystem file:
    -- 
    --     /temp/a/hamlet.xml
    -- 
    -- Because saveExtraPath = 1, the relative path is stored
    -- in the ZIP as:
    -- 
    --     a/hamlet.xml
    -- 
    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/temp'

    SELECT @saveExtraPath = 1

    EXEC sp_OAMethod @zip, 'AddFile', @success OUT, 'a/hamlet.xml', @saveExtraPath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- ------------------------------------------------------------
    -- Case 3:
    -- Store the path "temp/a/hamlet.xml" in the ZIP archive.
    -- 
    -- AppendFromDir is changed to "/" so that the relative path
    -- "temp/a/hamlet.xml" again refers to the same local filesystem file:
    -- 
    --     /temp/a/hamlet.xml
    -- 
    -- Because saveExtraPath = 1, the stored ZIP path is:
    -- 
    --     temp/a/hamlet.xml
    -- 
    EXEC sp_OASetProperty @zip, 'AppendFromDir', '/'

    SELECT @saveExtraPath = 1

    EXEC sp_OAMethod @zip, 'AddFile', @success OUT, 'temp/a/hamlet.xml', @saveExtraPath
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- ------------------------------------------------------------
    -- Write the ZIP archive.
    -- 
    -- This is where the referenced file is actually read from disk
    -- and compressed into the .zip archive.
    -- 
    -- The resulting ZIP contains the same file three times,
    -- each with a different stored path:
    -- 
    --     hamlet.xml
    --     a/hamlet.xml
    --     temp/a/hamlet.xml
    -- 
    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 'ZIP archive created successfully.'

    EXEC @hr = sp_OADestroy @zip


END
GO