473,728 Members | 1,583 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Storing BLOBs outside database

I remember readng that BLOBs can be stored externally (with reference to
the BLOB file stored in tables instead).

Does anyone have any experience doing this ? I have a few questions:

1).what are the things to watch out for (apart from obvious ones like
'file not found' type errors).
2). How may a stored proc be written to fetch the BLOB data ? (An
example would be very helpful)
3). How are errors handled in the stored proc that fetches the BLOB
data? (again an example would be very helpful)
Jun 27 '08 #1
3 3317
Here is a good reading on the topic:
http://research.microsoft.com/resear...MSR-TR-2006-45

The biggest problem with storing BLOB in the file system (in the current SQL
Server versions) is to guarantee the consistency of the data. For example,
if you have to restore your database to a point in time, how to restore all
files to that same time mark. And with that approach you store in the
database the path to the BLOB file. Then your client applicaiton code can
read the location of the BLOB and open it via the client functions to handle
BLOB data.

SQL Server 2008 will have the new FILESTREAM data type which provides
storing BLOB data to the file system while maintaining transactional
consistency.
http://msdn.microsoft.com/en-us/libr...(SQL.100).aspx
http://msdn.microsoft.com/en-us/libr...(SQL.100).aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Jun 27 '08 #2
Annonymous Coward wrote:
I remember readng that BLOBs can be stored externally (with reference to
the BLOB file stored in tables instead).
The way that people (ie document management systems) have done this in the
past is a)used image data types or b) simply held a URL which the client
picks up and processes either from a shared directory or a "lightweigh t web
server" (google for details)

Current Sql databases are not very good at processing large blobs
efficiently (despite the marketing b******t) as any processing - even just
a passthru mode costs performance on big files.
Jun 27 '08 #3
Annonymous Coward (me@home.com) writes:
I remember readng that BLOBs can be stored externally (with reference to
the BLOB file stored in tables instead).
Well, that you mean that you store the file name.
Does anyone have any experience doing this ? I have a few questions:

1).what are the things to watch out for (apart from obvious ones like
'file not found' type errors).
As Plamen said, SQL 2008 has FILESTREAM, but on SQL 2005, the big
problem is to get transactional consistency. And backups right.
2). How may a stored proc be written to fetch the BLOB data ? (An
example would be very helpful)
If the images are file, you don't fetch them from stored procedures,
but you read them directly from the file system.

On SQL 2008 with FILESTREAM, you can read the blobs from stored procedure,
but the more performant way is return a filehandle to the client
that can read the blob directly outside SQL Server.
3). How are errors handled in the stored proc that fetches the BLOB
data? (again an example would be very helpful)
What would be different here from reading a normal column?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 27 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
3487
by: Benoit St-Jean | last post by:
I am looking at options/ways to store 12 million gif/jpg images in a database. Either we store a link to the file or we store the image itself in the database. Images will range from 4k to 35k in size and there will be 12 millions of them (at the beginning)... I expect a 8% growth every year. We will also have to perform some cleanup jobs to delete images that are not longer referenced by the master table. We'll also have to...
3
2947
by: Dave Smithz | last post by:
Hi There, Being quite new to MS-SQL I would like to ask if there is a general opinion of what approach should be taken to storing things like external documents and images in databases. Should the actual files be stored within the database, or instead should links to the files on a file server or something similar be stored instead. For the end user I imagine it is easier to have everything stored within the
3
4724
by: hamvil79 | last post by:
I'm implementig a java web application using MySQL as database. The main function of the application is basically to redistribuite documents. Those documents (PDF, DOC with an average size around 2Mb) are stored in BLOB column. The amount of documents for the first year should not exceed 5/6 Giga, but I cannot make prevision for the next years. Those documents are mainly just accessed (update and delete are not so
7
6950
by: Howard Lowndes | last post by:
My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2 I have no difficulty inserting and managing BLOBs into the Large Object system table, and I have a user table called images which maintains the relationship between the BLOB loid and the identity that relates to it in my user tables. So far so good. When I RTFM obout psql it refers to the \lo_import, \lo_list, \lo_export and \lo_unlink functions.
6
7715
by: stenospamron | last post by:
Does anyone have any advice as to how to get JPG images into an OLE Object field? I have created a table that includes this data type, and allowed Access wizzard to generate a form. I wish to copy various images from the web (in my initial trials, I copied the files to my PC), and which generally seem to be JPG files. However, Access (Access 2000 ver 9.0.2720) does not seem to be able to cope with this. When I attempt to insert the...
2
2820
by: Jerry LeVan | last post by:
Hi, I am just getting into large objects and bytea "stuff". I created a small db called pictures and loaded some large objects and then tried to do a restore. Here is how I got the dump. pg_dump -Fc -b pictures > /Users/jerry/desktop/db.comp
2
2075
by: Joolz | last post by:
Hello everyone, Sorry if this is a FAQ, but I've groups.googled the subject and can't find a definite answer (if such a thing exists). I'm working on a db in postgresql on a debian stable server, ext3 filesystem. The db will contain files, not too many (I expect somewehere between 10 and 100 files max to be inserted daily), and not too big (mostly pdf files, some images. The size will rarely be larger than 1Mb). My plan was to store...
4
3925
by: lorirobn | last post by:
Hi, I need to add photos to my database. Back End is on MS SQL Server (I believe 2000), and Front End is on MS Access. I have read about storing the photos as BLOBS, but I am not sure how to do this with SQL Server. Does this mean store the photo as OLE image, but do something else to it to make it a "Blob"? I have also read about linking to the photo rather than storing it on
10
3050
by: nayden | last post by:
I started playing with python a few weeks ago after a number of years of perl programming and I can say that my first impression is, unsurprisingly, quite positive. ;) The reason I am writing here is that I can't seem to figure out how to save/restore python objects into a relational database. The way I used to do it in perl was to 'freeze' the object before storing it into the database and 'thaw' it before restoring it. (For those not...
0
8895
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9267
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9191
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
6012
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4523
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4787
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3231
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2645
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2159
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.