June 20, 2003 at 1:06 pm
I have an application where people upload a document as part of a "text" answer to a question. The "text" answer is in a table and the document is stored on the server.
Is there a way to actually store the document in SQL Server as, for example, a Binary Large OBject (BLOB).?
Any assistance would be greatly appreciated.
June 21, 2003 at 12:48 am
you can try using Image data typ.
check BOL for image data type.
June 22, 2003 at 6:04 pm
I guess the question would be, why store it in the database at all. Unless you are going to query the field, why not just store it in a file and store the filename in the database?
June 23, 2003 at 4:23 am
Take a look on here and on MSDN for information about the ADO stream object. Getting stuff in/out is not bad.
As for why, the biggest reason I do it is security. I can control access to the data and back it up. Also has some advantages if you're running multiple servers, you can use replication to handle the mirroring.
Andy
June 23, 2003 at 4:38 am
There seems to be frequently questions on storing binary data.
One common answer to this is, store the file on the filesystem and only the filename in the db.
While I prefer storing them in the db (for the same reasons Andy mentioned) what is the advantage of this approach? Is it an IO issue, is it faster...?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 23, 2003 at 7:57 am
I transfer spreadsheet data into my SQL db to save the space. Those spreadsheets eat it up no matter whats in them.
Archie Smith
MCSE, ASE, DBA
Intranet IT Administrator
Web Applications Developer
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
June 23, 2003 at 8:13 am
I recall a CBT I took a while back that demonstrated just this scenario. The topic of the CBT was Full Text Indexing in SQL Server 2000. It showed how to store spreadsheets in the DB, and how to full-text index them for keywords, and how to query the DB to find the files you are looking for using words or phrases that may be contained within the file.
I guess my point is this: People do store binary information, such as files, in a SQL Server DB, and it could have many benefits. Sorry I can't help with the exact logistics of how to do it.
June 23, 2003 at 1:52 pm
Ok, some thoughts about why you shouldn't put them in the DB. I will grant you this is a bit old school in thinking but it is something to consider.
Why put something in a database
-- You need to search on it
-- You need to really control access and NTFS security is too big of a pain
-- You need to control multiple people accessing a piece of data and modifing it
So why not put something in a database.
You don't have to worry about the data changing, how it is accessed or searching on it. (And depending on the searching requirement there may be better tools than SQL Server).
So if you BLOBS are spreadsheets that change every week put them in the database. If the BLOBS are scaned documents don't bother.
But you may say why not just put everything in the DB.
Also in the grander scheme of things database storage is more expensive: Management cots, backup costs, licencing costs, hardware costs, etc. Also why have the SQL Server engine spend cycles dealing with documents when the engine adds no value to the process.
In Summary:
If you have static BLOB documents & Objects (for example scaned documents) don't bother putting them in the database.
June 24, 2003 at 1:29 am
Hi Henry,
quote:
So if you BLOBS are spreadsheets that change every week put them in the database. If the BLOBS are scaned documents don't bother....
If you have static BLOB documents & Objects (for example scaned documents) don't bother putting them in the database.
my BLOB's are mainly PDF files (all stored in one single db, that stores nothing else) that once they are in the db never change. I receive a lot of research from investment banks. To make my life easier I store this in a db, grant access to whoever of senior management is interested in, have some asp pages to access and that's it.
I thought someone came up with arguments about losing performance, a db isn't supposed to do things the filesystem can handle more efficiently....and arguments like that.
Glad to see noone mentioned that!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply