Storing large BLOBs in the database with ActiveRecord

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

Mario,

For some reasons (design AFAIK), AR gets/update all the columns all the
time. This is needed for validation.
So, what you can do, is to separate your model, and put the blob in a
separate one (and having a has_one relation).

Jean-Etienne

3

Jean-Etienne D. wrote:

Mario,

For some reasons (design AFAIK), AR gets/update all the columns all the
time. This is needed for validation.
So, what you can do, is to separate your model, and put the blob in a
separate one (and having a has_one relation).

Jean-Etienne
http://www.woa.hu

Jean,

Your comment is related to an issue where people would like to have
BLOBs lazy loaded (only loaded when needed). My post is related to
UPDATING (not loading) a BLOB.

4

OK, read too fast sorry.

So, AR does not support parametized query. Just for info, this has been
discussed a lot, since it has some performance issues on Oracle for
example.

5

Jean-Etienne D. wrote:

OK, read too fast sorry.

So, AR does not support parametized query. Just for info, this has been
discussed a lot, since it has some performance issues on Oracle for
example.

Jean, I am aware that AR does not support parameterized queries. My
post is COULD it.

6

Mario J. wrote:

Jean-Etienne D. wrote:

OK, read too fast sorry.

So, AR does not support parametized query. Just for info, this has been
discussed a lot, since it has some performance issues on Oracle for
example.

Jean, I am aware that AR does not support parameterized queries. My
post is COULD it.

Actually Jean, my question is not really could AR support parameterized
queries (i.e. provide capability to AR user) but rather could it use
parameterized queries internally to support updating BLOBs. This has
very different implications that supporting parameterized queries in a
general and user facing manner.

7

Yes, internally of course :slight_smile:

Check this thread: http://www.ruby-forum.com/topic/108056

8

Mario J. wrote:

Actually Jean, my question is not really could AR support parameterized
queries (i.e. provide capability to AR user) but rather could it use
parameterized queries internally to support updating BLOBs. This has
very different implications that supporting parameterized queries in a
general and user facing manner.

Change “that supporting” to “than supporting” in the text above