question

Vertigo avatar image
Vertigo asked

How to write BLOB back out to Disk?

Notice: I am a DBA. I am not a developer and have very little experience in tsql besides the basic stuff so far so please keep it simple or use comments in your code PLEASE! Problem: We turned on an interface from one system to a different system after 6 months of uptime. Now we need to go back and get the first 6 months of data and move it to the other system. The database has a field called document that is of data type 'image' / blob. I have the query to get the facility,firstname, lastname, episode, account number, doctitle, doctype (pdf), and the documents binary blob. I need to copy/move the .pdf to a fileshare while renaming the pdf based on the facility, firstname, lastname, episode, account number, etc. How do I rename the file based on the other fields? How do I create a .pdf from a blob? Does this help? http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_10.shtml#Write_BLOB_To_File Thank you!
queryrenameblob
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What database system? You are mentioning TSQL, but later you are providing link to Oracle. Soo it is on MS SQL or on Oracle? In case of MS SQL, what version?
0 Likes 0 ·
Its on SQL Server 2005 (9.0.4035). I thought by posting a link that showed how it was done in Oracle someone who understood that might be able to port it over to SQL.
0 Likes 0 ·
Daniel Ross avatar image
Daniel Ross answered
hey Vertigo...not sure if this will help at all. I've created an 2005 SSIS package with a VB.net script to export images to file. Here is the script first, Option Strict Off Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.io Imports System.Drawing.Imaging Imports System.Drawing Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' ' Add your code here ' ' Writes the BLOB to a file (*.bmp). Dim bw As BinaryWriter ' Streams the BLOB to the FileStream object. Dim bufferSize As Integer = 100 ' Size of the BLOB buffer. Dim outbyte As Byte() = New Byte(bufferSize - 1) {} ' The BLOB byte[] buffer to be filled by GetBytes. Dim retval As Long ' The bytes returned from GetBytes. Dim startIndex As Long = 0 ' The starting position in the BLOB output. Dim emp_id As String = "" ' The employee id to use in the file name. ' Open the connection and read data into the DataReader. emp_id = Row.ID Dim myByte() As Byte myByte = DirectCast(Row.PICTURE.GetBlobData(0, Row.LENGTH), Object) Dim intArraySize As Integer Dim ms As New MemoryStream '("d:\test\" & emp_id & ".jpg", FileMode.OpenOrCreate, FileAccess.Write) ms.Write(myByte, 0, Row.LENGTH) Dim eps As EncoderParameters = New EncoderParameters(1) eps.Param(0) = New EncoderParameter(Encoder.Quality, 40) Dim ici As ImageCodecInfo = GetEncoderInfo("image/jpeg") Dim picImage As Image Try picImage = Image.FromStream(ms) '(fs.Write(myByte, 0, Row.LENGTH)) Catch ex As Exception msgbox(ex.Message) End Try Try picImage.Save("\\server\share\" & emp_id & ".jpg", ici, eps) 'picImage.Save("d:\test\" & emp_id & ".jpg", ici, eps) Catch ex As Exception End Try End Sub Private Function GetEncoderInfo(ByVal mimeType As String) As ImageCodecInfo Dim j As Integer Dim encoders As ImageCodecInfo() encoders = ImageCodecInfo.GetImageEncoders() For j = 0 To encoders.Length If encoders(j).MimeType = mimeType Then Return encoders(j) End If Next j Return Nothing End Function End Class Then in the package, just pass the image and the length of the image into the script. The script is a destination, not a transformation.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Daniel Ross, isnt' it too complex? As the bytes stored in blob contains everything. So it is enough to only write the bytes to the disk forexample using the FileStream. Then the Script Componen can contain only the code as the one in my answer (only the **Using** part.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
On SQL Server 2005+ you can create a CLR function for writing the BLOB data back to files. Below is a code which creates a very simple scalar CLR function which handles the writing. using System; using System.Collections.Generic; using System.Text; using System.Data.SqlTypes; using System.IO; using Microsoft.SqlServer.Server; public class BlobExport { [SqlFunction(IsDeterministic = true)] public static int ExportBlobField(SqlBytes inputData, SqlString destinationPath) { using (FileStream dataStream = new FileStream(destinationPath.Value, FileMode.Create)) { dataStream.Write(inputData.Value, 0, (int)inputData.Length); dataStream.Close(); } return 1; } } For example using a C# Express you crate a new Class Library. Put the above code as the source of any class. Then compile it and register it as Assembly in your database. The assembly needs External Access permission set and therefore your database have to be set as TRUSTWORTHY ALTER DATABASE [YourDB] SET TRUSTWORTHY ON Also CLR needs to be enabled on the server: sp_configure 'clr enabled', 1 RECONFIGURE WITH OVERRIDE Once CLR is enabled and TRUSWORTHY SET, you can register your assembly: CREATE ASSEMBLY [PPSQLCLRSafe] AUTHORIZATION [dbo] FROM 'PathToYourAssembly.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS After that you can register the Scalar function: CREATE FUNCTION [dbo].[WriteBlobToFile]( @inputdata varbinary(max), @destinationPath nvarchar(255) ) RETURNS [int] WITH EXECUTE AS CALLER AS EXTERNAL NAME [YourAssembly].[BlobExport].[ExportBlobField] Once registered, you can use a simple selects to write the blob data to the files: SELECT [ID] ,[FileName] ,[fileData] ,dbo.WriteBlobToFile([fileData], 'C:\OutputDirectory\' + [FileName]) FROM [TestDB].[dbo].[TestTable]
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.