# Insert large binary file in database (MySQL)

**URL:** <https://www.ruby-forum.com/t/insert-large-binary-file-in-database-mysql/140148>\
**Category:** Ruby\
**Created:** [June 11, 2008, 11:44am UTC](https://www.ruby-forum.com/t/insert-large-binary-file-in-database-mysql/140148 "2008-06-11T11:44:12Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![axyd80](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/82dd89/32.png) [@axyd80](https://www.ruby-forum.com/u/axyd80)\
**Post date:** [June 11, 2008, 11:44am UTC](https://www.ruby-forum.com/t/insert-large-binary-file-in-database-mysql/140148/1 "2008-06-11T11:44:12Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![axyd80](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/82dd89/32.png) [@axyd80](https://www.ruby-forum.com/u/axyd80)\
**Post date:** [June 11, 2008, 6:15pm UTC](https://www.ruby-forum.com/t/insert-large-binary-file-in-database-mysql/140148/2 "2008-06-11T18:15:11Z")

</div>

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 …

---

<div class="post-metadata">

**Author:** ![axyd80](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/82dd89/32.png) [@axyd80](https://www.ruby-forum.com/u/axyd80)\
**Post date:** [June 12, 2008, 5:35am UTC](https://www.ruby-forum.com/t/insert-large-binary-file-in-database-mysql/140148/3 "2008-06-12T05:35:56Z")

</div>

On Wednesday 11 June 2008 04:43:54 Alexey P. 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.

---

<div class="post-metadata">

**Author:** ![axyd80](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/82dd89/32.png) [@axyd80](https://www.ruby-forum.com/u/axyd80)\
**Post date:** [June 13, 2008, 1:06pm UTC](https://www.ruby-forum.com/t/insert-large-binary-file-in-database-mysql/140148/4 "2008-06-13T13:06:54Z")

</div>

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.