Store a PDF file in MySQL

Question

How would you store a PDF document in a field in MySQL?

Currently I have a list of customers and each customer has a certificate with information about their account that they can give to other companies to prove that they're our customer. Currently their certificate is exported as a PDF and e-mailed to someone here at work (the customer gets a physical copy as well), and that person's mailbox is filled with these e-mails. I'd much prefer to just have it in the customer's record - allowing it to be accessed via the customer's file in our in-house CRM.

I considered putting the PDFs in a folder and storing their location as a varchar in the customer's record, but if the PDFs get moved/deleted/etc. then we're up a creek.

My understanding is that a BLOB or MEDIUMBLOB is the type of field that I'd use to store it, but I'm a little ignorant in this regard. I'm not sure how to store something like that in the field (what C# datatype to give it), and then how to get it and open it via a PDF reader.

Answer 1

Honestly, I think that going with links instead of actually inserting the file into the database is the best way to go. Doing that will make the database very slow, and will be more trouble than its worth.

I would upload the files to a designated folder like "certificates" and made the certificate names go with a client number so they are easy to find and edit, remove etc. I have seen people store images in databases but even that is advised against.

If the method you wish is definitely a must, check out this article:

http://www.wellho.net/mouth/1001_-pdf-files-upload-via-PHP-store-in-MySQL-retrieve.html

It explains how to store, and retrieve .pdf files in a mySQL Database.

Best of luck!

Answer 2

I considered putting the PDFs in a folder and storing their location as a varchar in the customer's record, but if the PDFs get moved/deleted/etc. then we're up a creek.

That's the approach I would take. Then using some logic perhaps some BPEL type stuff - detect if any of the files move/delete and fire off a trigger to your DB to properly update the location/remove the location

Answer 3

Put it in the database, but the BLOB datatype probably won't cut it. The MEDIUMBLOB is normally sufficient.

MySQL Datatypes

BLOB, TEXT                L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT    L + 3 bytes, where L < 224
LONGBLOB, LONGTEXT        L + 4 bytes, where L < 232

I've used this several times with very good results. Be sure to save the filesize too, as it makes it easier to retrieve it. Not sure if it applies to C# as it does to PHP.

If using prepared statements with parameters the data will automatically be escaped AFAIK.

Also I can see no real reason as to why the database itself would get slow when storing this type of data in it. The main bottleneck will of course be the transfer of the data. Also MySQL is sometimes restrictive about the maximum length of queries and the responses in particular.

Once you have it running, it's pretty neat, especially when dealing with lots of small files. For a small number of large files, this approach does not make sense, better use some backup system to deal with moved/deleted files.

Answer 4

http://www.phpriot.com/articles/images-in-mysql is a good tutorial with some background information, and an implementation of storing images in MySQL