All geek questions in one place

Save .zip file to blob in SQL Server

I have to do it:

  • Take this @HTML and save the file example.txt
  • example.txtsave .zipwith password
  • .zip convert file to blob and save to table

Do you know how to do this only in T-SQL? Is it possible?

DECLARE @HTML varchar(200)

    SET @HTML = '<html>
    <META http-equiv=Content-Language content=pl>
    <META http-equiv=Content-Type content="text/html; charset=iso-8859-2">
    <body style="color:black; font-family: verdana, arial, helvetica, sans-serif; font-size:11px;">
    TEST</body></html>'

    SELECT @HTML

Using something like this?

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

OK I created this:

using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server; 

namespace SaveBlobData
{
    class SaveHTML
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SaveBlob(out SqlInt32 value, int idZak)
        {
            value = 3 + idZak;
        }
    }
}

I add .dll, but I get an error when choosing this. Where is the problem?

enter image description here

+4
source share
2 answers
  • Take this @HTML and save to example.txt file

You can use the OLE automation object for this part.

DECLARE @FSO int,
        @oFile int,
        @filename nvarchar(max) = 'c:\Folder\example.txt'

-- Create OLE Automation Object
EXEC sp_OACreate 'Scripting.FileSystemObject', @FSO OUT

-- Create file
EXEC sp_OAMethod @FSO, 'CreateTextFile', @oFile OUT, @filename, 8 , True

-- Write data
EXEC sp_OAMethod @oFile, 'Write', NULL, @HTML

-- Clear used objects
EXEC sp_OADestroy @FSO
EXEC sp_OADestroy @oFile
  1. example.txt save to .zip with password

I do not know how this can be done using t-sql, for this purpose we use powershell (or cmd):

# path to 7-zip
$zip = "C:\Program Files\7-Zip\7z.exe"
# file 
$FilesArh = "c:\Folder\example.zip"
$Dir = "c:\Folder\example.txt"
&$zip a "$FilesArh" "$Dir"

Test1.ps1 :

param
(
[String] $FilesArh,
[String] $Dir
) 
# path to 7-zip
$zip = "C:\Program Files\7-Zip\7z.exe"
&$zip a "$FilesArh" "$Dir"

SQL Server:

EXEC xp_cmdshell 'powershell C:\Folder\Test.ps1 "c:\Folder\example.zip" "c:\Folder\example.txt"'
  1. .zip blob :

OPENROWSET BLOB .

INSERT INTO your_table (BLOB_field)
SELECT * 
FROM OPENROWSET(BULK 'c:\Folder\example.zip', SINGLE_BLOB) AS BLOB
+3

, CLR, , SQL Server. .NET( , , , , , ..). DLL SQL Server. MSDN, :

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;
EXEC HelloWorld;

CLR ( ) https://msdn.microsoft.com/pl-pl/library/ms131094(v=sql.110).aspx

, CLR-, SQL Server, .

+2

Source: https://habr.com/ru/post/1649133/

More articles:


All Articles