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!

more ▼

asked Aug 01, 2012 at 06:31 PM in Default

avatar image

10 1 1 1

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?

Aug 02, 2012 at 06:29 AM Pavel Pawlowski

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.

Aug 02, 2012 at 01:01 PM Vertigo
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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);
             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


Also CLR needs to be enabled on the server:

 sp_configure 'clr enabled', 1

Once CLR is enabled and TRUSWORTHY SET, you can register your assembly:

 FROM 'PathToYourAssembly.dll'

After that you can register the Scalar function:

 CREATE FUNCTION [dbo].[WriteBlobToFile](
  @inputdata varbinary(max),
  @destinationPath nvarchar(255)
 RETURNS [int]
 EXTERNAL NAME [YourAssembly].[BlobExport].[ExportBlobField]

Once registered, you can use a simple selects to write the blob data to the files:

     ,dbo.WriteBlobToFile([fileData], 'C:\OutputDirectory\' + [FileName])
 FROM [TestDB].[dbo].[TestTable]

more ▼

answered Aug 03, 2012 at 06:16 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left

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

         picImage = Image.FromStream(ms) '(fs.Write(myByte, 0, Row.LENGTH))
     Catch ex As Exception
     End 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.

more ▼

answered Aug 03, 2012 at 03:19 AM

avatar image

Daniel Ross
2.9k 11 15 18

@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.

Aug 03, 2012 at 06:20 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 01, 2012 at 06:31 PM

Seen: 6271 times

Last Updated: Aug 03, 2012 at 06:20 AM

Copyright 2016 Redgate Software. Privacy Policy