Save R plot as a BLOB

I recently posed a question on stackoverflow on whether anyone knew an efficient way to save an R plot to a MySQL database as a BLOB. My plan was to use my personal desktop to perform R routines and save them to a web server, where they could then be accessed and displayed on a web page using a little PHP magic. After getting numerous responses on what a terrible idea this was, I was able to piece my own solution together. The steps are fairly simple. First, save the plot as a temp file, Second, read it back into R as a binary string. Third, insert the binary text into the database using the RODBC library. The code snippet is below.

## open connection
library(RODBC)
channel <- odbcConnect("")
 
## create a plot
x <- rnorm(100,0,1)
 
## save plot as temp file
png(filename="temp.png", width=500, height=500)
hist(x, col="light blue")
dev.off()
 
## read temp file as a binary string
plot_binary <- paste(readBin("temp.png", what="raw", n=1e6), collapse="")
 
## insert binary sting into a table
sqlQuery(channel, paste("INSERT INTO test VALUES (1, x'",plot_binary,"')", sep=""))
 
## close connection
odbcClose(channel)

I understand the ‘other way’ to do this (and most would argue better) would be to ftp the file to the server and save only the file path to the database. I guess I’m just being a bit lazy by letting the database do all the storage details, but so far so good.

Tags: mysql, R, Uncategorized