ActiveRecord support for BLOB

Rails
1

I have a model called Product with an attribute called file of
type :binary (DB BLOB). If I do product.save (where product is an
instance of my Product model), here is SQL statement that the mysql
adapter for Ruby will do:

INSERT INTO PRODUCT COLUMNS(NAME, PRICE, FILE) VALUES(‘PROD1’,
4.56, X’00DEF033423023220’)

All is hunky dory for as long as my BLOB is not too big. If the blob
is say 8MB (not that big actually),there is a problem:

The buffer used by the mysql server(max_allowed_packet) to hold the
commands must be at least 16MB (two bytes for hexadecimal
representation). This is an unecessarily huge number for holding SQL
commands.

Is there a way to tell ruby to use a host variable in the SQL
statement (i.e. VALUES(‘PROD1’, 4.56, :blobvar) instead of the literal
format in the example above. I cannot believe this problem has never
been seen before? Imagine if the BLOB data I need to store is 100MB?

Your help is much appreciated.

2

Any comments from anyone on this question?

3

Is there another place I should/could be asking this question?

4

I would guess that most developers, including me, would want to avoid
storing large blob data inside the database. Especially in the flat
table structure you are showing in your example.

If it were me, I would store a URI/URL to “GET” the resource
representing your binary file. In other words store the files in the
file system, which can be much more efficient at storing and
retrieving binary data than most databases. This way you can take
advantage of the browser’s built-in download manager, take tremendous
pressure off your database, etc.

INSERT INTO PRODUCT COLUMNS(NAME, PRICE, URI) VALUES(‘PROD1’,
4.56, ‘/product_files/123’)

routes.rb

map.resources :product_files

5

Oh btw. Sorry I don’t have a direct answer to your question. I don’t
know the answer to that.

6

Robert,

I appreciate the feedback but if I understand it you are just pushing
back the same dilemma to another web server (the server’s whose web
address you are suggesting I store). My problem is I want users to be
able to store and retrieve those large BLOBs. Not sure what the
reluctance to using BLOB is. With proper caching I think you can get
good results.

On May 23, 2:29 pm, Robert W. [email protected] wrote:
(snip)

If it were me, I would store a URI/URL to “GET” the resource
representing your binary file. In other words store the files in the
file system, which can be much more efficient at storing and
retrieving binary data than most databases.
(snip)

7

I appreciate the feedback but if I understand it you are just
pushing back the same dilemma to another web server (the
server’s whose web address you are suggesting I store). My
problem is I want users to be able to store and retrieve
those large BLOBs. Not sure what the reluctance to using
BLOB is. With proper caching I think you can get good results.

I fully agree with you, for what it’s worth. Kirk H. has started a
DBI2 project which hopes to clean up the cruft from the current DBI
library and I’m planning on making proper BLOB support happen therein.
Hopefully an ActiveRecord adapter won’t be too difficult to write at
that point. Of course, this is entirely vaporware at the moment, so
don’t hold your breath.

  • donald