[RESOLVED] Using T-SQL to compress file using Windows compressing utility

Can this be done? I've seen samples that uses winzip but I don't have winzip so I'm wondering if this is doable using the built-in windows compressing utility. This is the VB6 code that does it in Vista, not sure if this is convertible at all to T-SQL. The other option I am looking into is to make a small program/dll that will do it and be called from SQL Server.

Code:

Public Sub ZipFile(ByVal strFileToZip As String, ByVal strTargetZip As String, Optional ByVal bolFileType As Boolean = True) CreateEmptyZip strTargetZip Dim folder As Folder3 Dim zipObject As IShellDispatch5 Set zipObject = CreateObject("Shell.Application") Set folder = zipObject.nameSpace(strTargetZip) If bolFileType Then folder.CopyHere strFileToZip Else folder.CopyHere zipObject.nameSpace(strFileToZip).Items End If End Sub Private Sub CreateEmptyZip(ByVal sPath As String) Dim strZIPHeader As String strZIPHeader = Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String$(18, 0) ' header required to convince Windows shell that this is really a zip file CreateObject("Scripting.FileSystemObject").CreateTextFile(sPath).Write strZIPHeader End Sub

Re: Using T-SQL to compress file using Windows compressing utility

It is possible to have VBScript inside jobs etc, so it might work... creating a program will almost certainly work, you can use the SP xp_shell (or something like that!) to run it.

edit: I was fairly close, but no cigar... it is actually xp_cmdshell

Re: Using T-SQL to compress file using Windows compressing utility

Thanks si. So it does look that my only option is to call another application.

Re: Using T-SQL to compress file using Windows compressing utility

FYI: SQL Server supports OLE Automation using some system stored procedures.

The link is to BOL for SQL Server 2008 but these sprocs exist in all versions (well maybe not 6.5).

http://msdn.microsoft.com/en-us/library/ms190501.aspx

Re: Using T-SQL to compress file using Windows compressing utility

Hmmmnnn... Cool, will dig deeper into it. Thanks for the information!

Re: Using T-SQL to compress file using Windows compressing utility

Some problems when I tried it. For some reason I cannot duplicate this VB6 code:

Code:

strZIPHeader = Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String$(18, 0)

Below is my attempt. How could I pass a string with trailing empty characters?

Code:

DECLARE @object int DECLARE @hr int DECLARE @src varchar(255), @desc varchar(255) DECLARE @return int DECLARE @property int EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc PRINT @src RETURN END EXEC @hr = sp_OAMethod @object, 'CreateTextFile', @property OUT, 'C:\PIS\Test.zip' IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc print @src print @desc RETURN END DECLARE @xx nchar(22) SET @xx = CHAR(80) + CHAR(75) + CHAR(5) + CHAR(6) + REPLICATE(CHAR(0),18) PRINT len(@xx) EXEC @hr = sp_OAMethod @property, 'Write', NULL, @xx IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @property, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc print @src print @desc RETURN END -- Destroy the object. EXEC @hr = sp_OADestroy @object IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END

And this has been my attempt at instantiating the folder object but I am getting a type mismatch error on the sp_OAMethod. Since NameSpace seem to like variant (as seen http://www.vbforums.com/showthread.php?t=581859) I even tried declaring @xx as sql_variant to no avail.

Code:

DECLARE @object int DECLARE @hr int DECLARE @src varchar(255), @desc varchar(255) DECLARE @return int DECLARE @property int EXEC @hr = sp_OACreate 'Shell.Application', @object OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc PRINT @src RETURN END -- Get a property by calling the method. EXEC @hr = sp_OAMethod @object, 'NameSpace', @property OUT, 'C:\Test.zip' IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc print @src print @desc RETURN END

Any help will be highly appreciated.

Re: Using T-SQL to compress file using Windows compressing utility

Okay, here is the finished code which will allow us to compress a file like in Windows. I have made this so that I can compress my database back-up files to conserve hard disk space.

Code:

CREATE PROCEDURE CompressFile @ZipFile VARCHAR(255), @FileToZip VARCHAR(255) AS --author: dee-u of www.vbforums.com, 08-28-2009 DECLARE @hr INT, @folderObject INT, @shellObject INT, @src VARCHAR(255), @desc VARCHAR(255), @command VARCHAR(255), @password VARCHAR(255), @username VARCHAR(255) SET @username = 'username' SET @password = 'password' --Create table to save dummy text to create zip file CREATE TABLE ##DummyTable ( [DummyColumn] [VARCHAR](255)) --header of a zip file DECLARE @zipHeader VARCHAR(22) SET @zipHeader = CHAR(80) + CHAR(75) + CHAR(5) + CHAR(6) + REPLICATE(CHAR(0),18) --insert zip header INSERT INTO ##DummyTable (DummyColumn) VALUES (@zipHeader) --save/create target zip SET @command = 'bcp "..##DummyTable" out "' + @ZipFile + '" -c -S "(local)" -U "' + @username + '" -P "' + @password + '"' EXEC MASTER..xp_cmdshell @command --Drop used temporary table DROP TABLE ##DummyTable --get shell object EXEC @hr = sp_OACreate 'Shell.Application' , @shellObject OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @shellObject , @src OUT , @desc OUT SELECT hr = convert(VARBINARY(4),@hr), Source = @src, DESCRIPTION = @desc RETURN END --get folder SET @command = 'NameSpace("' + @ZipFile + '")' EXEC @hr = sp_OAMethod @shellObject , @command , @folderObject OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @shellObject , @src OUT , @desc OUT SELECT hr = convert(VARBINARY(4),@hr), Source = @src, DESCRIPTION = @desc RETURN END --copy file to zip file SET @command = 'CopyHere("' + @FileToZip + '")' EXEC @hr = sp_OAMethod @folderObject , @command IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @folderObject , @src OUT , @desc OUT SELECT hr = convert(VARBINARY(4),@hr), Source = @src, DESCRIPTION = @desc RETURN END --Destroy the objects used. EXEC sp_OADestroy @shellObject EXEC sp_OADestroy @folderObject GO

Sample usage:

Code:

EXEC CompressFile 'C:\test.zip', 'C:\test.jpg'