(SQL Server) Zip Append Files and Entry Type

See more Zip Examples

Demonstrates appending files to a zip object for writing a .zip. Also explains the "entry type".

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 @success int

    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 @entry int

    DECLARE @zipPath nvarchar(4000)
    SELECT @zipPath = 'c:/temp/qa_output/out.zip'

    -- Initialize the zip object with the path of the .zip to be created.
    EXEC sp_OAMethod @zip, 'NewZip', @success OUT, @zipPath

    -- Recursively append to the zip object the paths of the files and directories in a directory tree.
    -- At this point we are not creating the .zip, nor are we reading the contents of the files.
    -- We are simply appending references to the files and directories in the local filesystem
    -- that will get processed when WriteZip is called.
    DECLARE @recurse int
    SELECT @recurse = 1
    DECLARE @saveExtraPath int
    SELECT @saveExtraPath = 0
    DECLARE @archiveOnly int
    SELECT @archiveOnly = 0
    DECLARE @includeHidden int
    SELECT @includeHidden = 1
    DECLARE @includeSystem int
    SELECT @includeSystem = 0
    EXEC sp_OAMethod @zip, 'AppendFilesEx', @success OUT, 'c:/temp/files_to_zip', @recurse, @saveExtraPath, @archiveOnly, @includeHidden, @includeSystem
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Note: We can iterate over the references to the files that will be processed when zipping:
    DECLARE @count int
    EXEC sp_OAGetProperty @zip, 'NumEntries', @count OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OAMethod @zip, 'GetEntryByIndex', @entry OUT, @i
        EXEC sp_OAGetProperty @entry, 'FileName', @sTmp0 OUT

        EXEC sp_OAGetProperty @entry, 'EntryType', @iTmp0 OUT
        PRINT @sTmp0 + ' entryType = ' + @iTmp0
        EXEC @hr = sp_OADestroy @entry

        SELECT @i = @i + 1
      END

    PRINT '----'

    -- Sample output for the above loop:
    -- The entry type = 1, because these are entries that are references to files in the local filesystem
    -- that are to be processed when the zip is written.

    -- hamlet.xml entryType = 1
    -- hello.pdf entryType = 1
    -- ...
    -- ...

    -- Possible entry type values:
    -- 0 - Mapped Entry: An entry in an existing Zip file.
    -- 1 - File Entry: A file not yet in memory, but referenced. These entries are added by calling AppendFiles, AppendFilesEx, AppendOneFileOrDir, etc.
    -- 2 - Data Entry: An entry containing uncompressed data from memory. These entries are added by calling AppendData, AppendString, etc.
    -- 3 - Null Entry: An entry that no longer exists in the .zip.
    -- 4 - New Directory Entry: A directory entry added by calling AppendNewDir. 

    -- The application can continue to add additional entries.
    -- For example:
    DECLARE @eTemp int
    EXEC sp_OAMethod @zip, 'AppendString', @eTemp OUT, 'helloWorld.txt', 'Hello World'
    EXEC @hr = sp_OADestroy @eTemp

    -- Now let's have a look at what will get zipped when we write the zip:
    EXEC sp_OAGetProperty @zip, 'NumEntries', @count OUT
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OAMethod @zip, 'GetEntryByIndex', @entry OUT, @i
        EXEC sp_OAGetProperty @entry, 'FileName', @sTmp0 OUT

        EXEC sp_OAGetProperty @entry, 'EntryType', @iTmp0 OUT
        PRINT @sTmp0 + ' entryType = ' + @iTmp0
        EXEC @hr = sp_OADestroy @entry

        SELECT @i = @i + 1
      END

    PRINT '----'

    -- You can see that "helloWorld.txt" is added. 
    -- Its entryType = 2, which means it is in-memory data (it is not a reference to a file in the local filesystem).

    -- hamlet.xml entryType = 1
    -- hello.pdf entryType = 1
    -- ...
    -- ...
    -- helloWorld.txt entryType = 2

    -- ----------------------------------------------------------------------------
    -- Now we'll write the zip to a .zip file.
    EXEC sp_OASetProperty @zip, 'FileName', @zipPath
    EXEC sp_OAMethod @zip, 'WriteZip', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Let's look again at the entries in the .zip
    EXEC sp_OAGetProperty @zip, 'NumEntries', @count OUT
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OAMethod @zip, 'GetEntryByIndex', @entry OUT, @i
        EXEC sp_OAGetProperty @entry, 'FileName', @sTmp0 OUT

        EXEC sp_OAGetProperty @entry, 'EntryType', @iTmp0 OUT
        PRINT @sTmp0 + ' entryType = ' + @iTmp0
        EXEC @hr = sp_OADestroy @entry

        SELECT @i = @i + 1
      END

    PRINT '----'

    -- After writing the .zip, the entry types change to 0.
    -- Entry type 0 indicates that it's a "mapped entry", which is an entry in an existing (and currently open) .zip file.

    -- hamlet.xml entryType = 0
    -- hello.pdf entryType = 0
    -- ...
    -- ...
    -- helloWorld.txt entryType = 0

    -- ----------------------------------------------------------------------------
    -- If desired, we could continue adding additional entries of any kind (files or data) to the currently open .zip archive...

    EXEC sp_OAMethod @zip, 'AppendString', @eTemp OUT, 'helloWorld2.txt', 'Hello World 2'
    EXEC @hr = sp_OADestroy @eTemp

    -- Write the .zip, including the new entries..
    EXEC sp_OAMethod @zip, 'WriteZip', @success OUT
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @zip, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @zip
        RETURN
      END

    -- Finally, let's look again at the entries in the .zip
    EXEC sp_OAGetProperty @zip, 'NumEntries', @count OUT
    SELECT @i = 0
    WHILE @i < @count
      BEGIN
        EXEC sp_OAMethod @zip, 'GetEntryByIndex', @entry OUT, @i
        EXEC sp_OAGetProperty @entry, 'FileName', @sTmp0 OUT

        EXEC sp_OAGetProperty @entry, 'EntryType', @iTmp0 OUT
        PRINT @sTmp0 + ' entryType = ' + @iTmp0
        EXEC @hr = sp_OADestroy @entry

        SELECT @i = @i + 1
      END

    PRINT '----'

    -- hamlet.xml entryType = 0
    -- hello.pdf entryType = 0
    -- ...
    -- ...
    -- helloWorld.txt entryType = 0
    -- helloWorld2.txt entryType = 0

    EXEC sp_OAMethod @zip, 'CloseZip', NULL


    PRINT 'Success'

    EXEC @hr = sp_OADestroy @zip


END
GO