Zipping file from Stored Procedure

[Former Member]

Is it possible from within a stored procedure to zip up files ?

[MarkCulp]

There is nothing built into the server to zip up the files, but you can easily call an [external procedure][1] to do it, or use [xp\\_cmdshell][2] to run an external process that will zip up the files for you.

---

[edit 2013-02-15]

Sorry, most of my response above is bogus... I confused compression with encryption 😞

Since SA v10 the compress() and decompress() functions have had a second parameter named 'algorithm' which when set to 'gzip' can be used to compress/decompress data that can be exchanged outside of the database server and decompressed/compressed using gunzip/gzip.

Example:

-- write compressed contents of variable @data to file data.gz
call xp_write_file( 'data.gz', compress( @data, 'gzip' ) );
-- and then decompress it using gunzip
call xp_cmdshell( 'gunzip data.gz' );

[Breck_Carter]

It's the tie you're wearing in your photo... restricts the bloodflow 🙂

[Former Member]

I ended up downloading 7-Zip, writing a bat file to call 7 Zip, and then calling that bat file with an Event. Worked well.

[VolkerBarth]

What about reading the file with xp_read_file() and using the builtin COMPRESS() function to compress data and write it back to file with xp_write_file()?

Note, I can't tell whether the builtin compression with the "zip" algorithm will be compatible with ordinary zipped files. On Linux, using "gzip" should work that way. - Mark's response may indicate that "zip" itself won't work here as expected...

[MarkCulp]

Using compress within the engine will compress the data contents but you cannot unzip the result outside of the engine (unless you know what you're doing). The same is true the other way - you cannot use uncompress to unzip contents which have been zipped outside the engine. (Note: this comment applies to SA v12 and below).

[Edit: 2013-02-15 this statement is incorrect - I have corrected my answer to this question]

Also, the question was not clear whether a single file was being zipped or a zip archive (containing multiple files) was wanted.

[VolkerBarth]

Thanks for the clarification - I had a weak impression I had read about that limitation in some FAQ or NNTP thread (therefore my suspicion...) but couldn't find anything relevant.

Now it's well-documented here, at least:)

[VolkerBarth]

(Note: this comment applies to SA v12 and below).

Hmmm, have I overseen something in the Nagano docs...?

[VolkerBarth]

Now it's well-documented here, at least:)

Hmm, I'm not so sure anymore:)

[VolkerBarth]

Okay, when Mark has been refering to encryption instead of compression, then the v16 hint makes sense ("Raw encryption", do you hear me?)...