x

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

Vertigo gravatar image

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

                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]
more ▼

answered Aug 03, 2012 at 06:16 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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

    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.
more ▼

answered Aug 03, 2012 at 03:19 AM

Daniel Ross gravatar image

Daniel Ross
2.9k 11 13 14

@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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x376
x14
x3

asked: Aug 01, 2012 at 06:31 PM

Seen: 3217 times

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