Trick: Compress file using SQL Server
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Trick: Compress file using SQL Server

  1. #1
    Join Date
    Jan 2006
    Location
    Pearl of the orient
    Posts
    303

    Thumbs up Trick: Compress file using SQL Server

    I just want to share this little snippet which could be used to compress a file using SQL Server. I have made this so that I can compress my database back-up files to conserve hard disk space. I thought this could come in handy to somebody else.

    Code:
    CREATE PROCEDURE CompressFile 
                    @ZipFile   VARCHAR(255), 
                    @FileToZip VARCHAR(255) 
    AS 
      --author: dee-u of CodeGuru, vbforums
      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 -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'

  2. #2
    Join Date
    Oct 2011
    Posts
    1

    Re: Trick: Compress file using SQL Server

    What file types can it compress?

    Mabalin ti image files? Ngamin adda .jpg dita example.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center