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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Any comments from anyone on this question? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Is there another place I should/could be asking this question? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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
On May 4, 7:54 pm, Mario Jauvin
<mari...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Oh btw. Sorry I don''t have a direct answer to your question. I don''t know the answer to that. On May 23, 2:29 pm, Robert Walker <rwalker...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 > > On May 4, 7:54 pm, Mario Jauvin <mari...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > 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.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 Walker <rwalker...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---