[GLLUG] BLOBs in Databases

Kami Vaniea kami.vaniea at gmail.com
Mon Feb 2 14:44:55 EST 2015


I occasionally use BLOBs in my work, though I tend to use MySQL over
SQLite. For example, I once built a site that would do a kind of email
based version repository. So you would email the system your doc file,
and CC everybody who you wanted to view it. The system would then store
the doc file, along with all future emailed doc files in one easily
found place. Managing all that via the file system was annoying, and
security problematic, so we threw the doc files into BLOBs. When the
user wanted their file back the system would just read the contents of
the BLOB back to them like it was a regular file.

I've also done research projects where we automatically screen captured
websites and needed to store the captures somewhere with a pile of
metadata. So again, we used BLOBs. Other projects I've worked on needed
to store binaries of various things. For example, web scraping projects
that wanted to store the binaries of free apps so they could be analyzed
in the future.

I imagine BLOBs are also an excellent place to put things you don't want
MySQL getting smart about. I've had way too many issues in the past with
UTF8/16 text getting chewed up by either MySQL or whatever language I'm
using. Putting data in a BLOB is a good way to flag "here be dragons" to
the database so it doesn't try and index it or "improve" it in any way.

I've never really tried reading the BLOB data directly from the DB.
Normally I just use the DB the same way I would use the file system and
code the application I'm writing to visualize whatever the binary is.

- Kami


On 02/02/2015 02:17 PM, Chick Tower wrote:
> I've been doing a little research into SQLite, and they have a storage
> class (they say it's slightly more general than a datatype) for binary
> large objects (BLOB).  You can store photo images, or I suppose even
> executables, in the database.  (I assume the same is true for other
> databases.)  My question is why?  You can't view an image with SQLite.
> How do you even get an image out of a database to view it?
>
>
>
> Perhaps I'm wrong in the intended use of the BLOB storage class.  It
> says on the SQLite website "The value is a blob of data, stored exactly
> as it was input."  So maybe it's intended for something other than
> images, but, if so, what?  I still wonder how you extract a BLOB from a
> database in order to do anything with it.  Does anyone know what to do
> with BLOBs?
>
>
>




More information about the linux-user mailing list