Sir Keith,

Is it good to store binary file like eg. pdf file, in a SQL Server ?

Makikisawsaw lang huh. Since wala pa reply si Keith, ako muna hehehe.

For me, no. My usual practice is to just store the "location" of the file in the filesystem to say, a varchar field in your database. Interesting topic to ah.

lamia:

Makikisawsaw lang huh. Since wala pa reply si Keith, ako muna hehehe.

For me, no. My usual practice is to just store the "location" of the file in the filesystem to say, a varchar field in your database. Interesting topic to ah.

Hehehe. Yup! This is a really interesting topic!

I agree with Lamia. I have developed different applications that handle this type of scenario and 100% of the time the DBA and the Network Admin agrees that it is better and safer to have the file storage on the filesystem instead of the database. The point I always here when there is a discussion about this issue is the saying that you shouldn't put all your eggs in one basket. What we usually do is just create a field on a table that points to the location of the file and another field that specifies what type of location (e.g. filesystem, url, web service). This way we can update the location in the event that we change the storage type/location.

 HTH

 sir lamia thanks for the reply. but if we store the file in the filesystem is it possible to execute fulltext search on the filesystem?

 sir Keith thanks for the reply. my next question is how to perform fulltext search on pdf file that is stored on a filesystem?

jerome1216:

 sir Keith thanks for the reply. my next question is how to perform fulltext search on pdf file that is stored on a filesystem?

Hmmm... Haven't done this before but my idea is like this...

1. Get eh location of the PDF from the database
   
2. Using some sort of API, load the PDF and get the contents(whoah, isn't this big??, but it's the same when you store in RDBMS anyway)
3. I'm not an expert about text types, but my idea is that the contents you get from the PDF should be human readable (ascii) so when you compare with the search query, they will match. Store the contents of the PDF in  String or something.

Wow, I actually learned something new from Keith.:)

jerome1216:

...how to perform fulltext search on pdf file that is stored on a filesystem?

Since we couldn't agree more na we do not store the PDFs themselves sa database, siguro if you want to store the "content" of the pdf why not "strip" the text and store them?

Take a look at this: http://devpinoy.org/blogs/marl/archive/2008/03/04/pdf-to-text-using-open-source-library-pdfbox-another-sample-for-grade-1-pupils.aspx

 thank you for the ideas sir lamia and marl. 

jerome1216:

 sir lamia thanks for the reply. but if we store the file in the filesystem is it possible to execute fulltext search on the filesystem?

if you are going to store the PDF in the filesystem, try using Windows Indexing Service to support full text search on your PDFs. Then on your on SQL backend you can create a linked server to Windows Indexing service database to run your full text search queries. I'm not the person who actually implemented this, but thats what we are using with our commercial document management software. I can say its very efficient though.