How to replicate BLOB (PDF & XML) files and how to extract them

Question

How to replicate BLOB (PDF & XML) files and how to extract them

kenyangyiwei

SSC Veteran

Points: 220
More actions
October 5, 2011 at 6:15 am

#148727

A web service to generate PDF file and its description XML file, store PDF and XML as BLOB objects in a SQL 2005 Server (Sender), I need to replicate them to another SQL 2005 Server (Receiver).
once they arrive at the receiver Database server, how to extract them out as a PDF file, XML file respectively and store them to another different file server? Also how to make sure the files will not be extracted multiple times at the receiving end?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

File operations directly from TSQL are a pain.

SQL server is designed to fiddle with objects INSIDE a database, and as soon as you want to step outside of that context(to disk) , it's better to use a programming language, as that's it's forte.

Elliot Whitlow, who is a heavy contributer here on SSC as well, has a really nice CLR project that i've adopted:

that CLR project has functions for both the isnert and the extraction of BLOB fields(varbinary and image)

--Elliot Whitlow's project! awesome

http://nclsqlclrfile.codeplex.com/

it took me like two minutes to install total.

here's a code snippet demoing both upload to disk and extraction from disk:

--MFGetFileImage

-- Parameters: @FilePath, @FileName

-- purpose: given a path and filename, return the varbinary of the file to store in the database.

-- usage:

CREATE TABLE myImages(id int,filename varchar(200),rawimage varbinary(max) )

INSERT INTO myImages(id,filename,rawimage)

SELECT 1,'fedora_spinner.gif',dbo.MFGetFileImage('C:\Data\','fedora_spinner.gif' )

--MSPSaveFileImage

-- Parameters: @FilePath,@FileName,@FileBytes

-- purpose: given an varbinary image column in a table, write that image to disk

-- usage:

--assumes table and the file from the example above for dbo.MFGetFileImage exists already.

declare @myfile varbinary(max)

SELECT @myfile = rawimage FROM myImages WHERE id = 1

EXEC dbo.MSPSaveFileImage 'C:\Data','spinning.gif',@myfile

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kenyangyiwei SSC Veteran Points: 220 More actions · Answer 2

Thanks, another qestuion is how to check when fresh blob objects arrive at the receivers SQL 2005 server?

Lowell SSC Guru Points: 323546 More actions · Answer 3

you'll have to explain...it depends on what you mean by "received"

if they are "received" in a folder on the disk, vs "received" a s in a new row is created in the database, vs a blob is available via a web service but not yet requested.

how would you manually know something is "received" now

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kenyangyiwei SSC Veteran Points: 220 More actions · Answer 4

Receive means a new row of BLOB objects arrive at receiver SQL Server Database, how to check that? is it best to use trigger event or build a stored procedure set as scheduled job to check that. I imagine to have a flag in table to indicate whether a row is extracted, so when a new row arrives, then the BLOB objects embeded in the row need to be extracted and transfer to another file server, and a flag in the table will be set to Y, so the same row doesn't get extracted multiple times

Lowell SSC Guru Points: 323546 More actions · Answer 5

kenyangyiwei (10/5/2011)
Receive means a new row of BLOB objects arrive at receiver SQL Server Database, how to check that? is it best to use trigger event or build a stored procedure set as scheduled job to check that. I imagine to have a flag in table to indicate whether a row is extracted, so when a new row arrives, then the BLOB objects embeded in the row need to be extracted and transfer to another file server, and a flag in the table will be set to Y, so the same row doesn't get extracted multiple times

yeah that's how i would do it; i skip the trigger idea, and would have a job that runs every x minutes, that compares rows that have been processed or not.

either a flag in the existing row of data to indicate that it was processed, or a seperate table, which has the PK of the row from the original table for items it has processed...that way you can join the "processed" table to the original for rows that are new.

does your blob data ever get UPDATED? in that case, i'd use a trigger, but not for just processing new inserts.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!