How to Read and Write MySQL Blobs

5 Jul 2009

1 From the MySQL Command-Line Tool
2 From a Java Application
3 References

While we're normally tempted to only store textual data into databases, it can come in handy to also store binary data. This is what MySQL refers to as blobs.

1 From the MySQL Command-Line Tool

The MySQL command-line tool makes it easy to have a first taste of working with blobs using databases. It all happens with the LOAD_FILE() function:

UPDATE tbl_name SET blob_column=LOAD_FILE('/tmp/picture') WHERE id=1;

2 From a Java Application

The general idea is to create the whole tuple without the blob and add the blob manually next. Basically, to add the blob you first get a dummy one from the DB, edit it, and send it back (i.e. update it). Don't forget that you count from 1 to n in the MySQL Connector world.

Create the tuple you want to add the blob to. Make sure the blob's attribute is not null (e.g. you may put a dummy string on the fly).
Get a binary form of the data you want to store. This is normally easy, since this data comes from a file:
```
BufferedInputStream bis = new BufferedInputStream(new FileInputStream(dataFile));
```
... where dataFile is a File. Since we'll copy the binary data into an array supplied as argument, let's make some room in this array:
```
byte[] data = new byte[(int) dataFile.length()];
```
You may then read the file and copy its contents into your array:
```
bis.read(data);
```

Get the dummy blob from the tuple you added, using the usual procedure:

ResultSet resultSet = statement.executeQuery("SELECT blob FROM table WHERE condition = true");
resultSet.next();
Blob blob = resultSet.getBlob(1);

... where statement is a valid statement.

Edit the blob:
```
blob.setBytes(1, data);
```
Create a prepared-statement, that is, an incomplete update query you which you'll finish off by using Java methods connected to it:
```
PreparedStatement ps = connection.prepareStatement("UPDATE table SET blob = ? WHERE condition = true");
```
... where connection is valid. Set the update value:
```
ps.setBlob(1, blob);
```
... and flush everything up:
```
ps.executeUpdate();
```

Bear in mind that most of these instructions need their SQLException to be caught; you'd better wrap it all up in one try block.

3 References

LOAD_FILE()