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.