ActiveRecord support for BLOB

rubyonrails-talk
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

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.

(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 Haines 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