Insert large binary file in database (MySQL)

Discussion in 'Ruby' started by Alexey Petrushin, Jun 11, 2008.

  1. Hello!

    Probably a stupid question ...
    How can i insert large binary file in MySQL using IO stream?

    For example, 'Sequel' can do 'insert' as:

    dataset << {:name => 'a name', :value => 'some value'}

    i need something like:

    File.open('path_to_binary_file'){|file|
    dataset << {:name => 'a name', :value => file}
    }

    Thanks!
    --
    Posted via http://www.ruby-forum.com/.
     
    Alexey Petrushin, Jun 11, 2008
    #1
    1. Advertising

  2. In Java it can be done this way:

    File file = new File("/home/sumedha/sumedha.jpg");
    fis = new FileInputStream(file);
    ps = conn.prepareStatement("insert into pictures(id, name,
    photo) values (?, ?, ?)");
    ps.setString(1, "001");
    ps.setString(2, "sumedha");
    ps.setBinaryStream(3, fis, (int) file.length());
    ps.executeUpdate();
    conn.commit();

    hope Ruby also has something similar ...
    --
    Posted via http://www.ruby-forum.com/.
     
    Alexey Petrushin, Jun 11, 2008
    #2
    1. Advertising

  3. On Wednesday 11 June 2008 04:43:54 Alexey Petrushin wrote:
    > Probably a stupid question ...
    > How can i insert large binary file in MySQL using IO stream?


    You probably shouldn't.

    MySQL is for storing small things, like userids, (hashed) passwords, posts,
    etc.

    For larger things, it's going to be easier and more efficient to use the
    filesystem.
     
    David Masover, Jun 12, 2008
    #3
  4. Alexey Petrushin

    Guest

    You can create a text field in your table, and convert to/from Base64/
    Binary as needed. You have to do a File.open, iterating each line, and
    << Base64.encode64(line), to encode ; Base64.decode64(line), to
    decode. It's an option. I know BLOB sends, using the db adapter are a
    problem with Postgres when the db is non-local (not on the same
    machine) to the app. If you have to store the file object in your
    database, I'd recommend the Base64 transform. Note: storage compared
    to binary will increase slightly by converting to/from Base64 (even
    though ruby is decently fast at this), application processing time
    will go up due to conversions, and your DB machine may slow down
    somewhat (dependent on load). If you do go this route, you should md5
    or sha1 hash the binary object (before transform) to save on
    redundantly storing objects, and rather use a join table for a user to
    file association, against the md5/sha1.
     
    , Jun 13, 2008
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. linus
    Replies:
    0
    Views:
    556
    linus
    Oct 24, 2003
  2. Jeffrey H. Coffield
    Replies:
    1
    Views:
    1,860
  3. Roedy Green
    Replies:
    0
    Views:
    1,476
    Roedy Green
    Jul 23, 2009
  4. sahm
    Replies:
    4
    Views:
    867
  5. julian
    Replies:
    8
    Views:
    449
    Avatar
    Apr 6, 2006
Loading...

Share This Page