Save R plot to web server

Question

I'm trying to create a procedure which extracts data from a MySQL server (using the RODBC package), performs some statistical routines on that data in R, then saves generated plots back to the server such that they can be retrieved in a Web Browser via a little bit of php and web magic.

My plan is to save the plot in a MySQL BLOB field by using the RODBC package to execute a SQL insert into statement. I think I can insert the data directly as a string. Problem is, how do I get the data string and will this even work? My best thought is to use the savePlot function to save a temp file and then read it back in somehow.

Anybody tried this before or have suggestions on how to approach this?

Answer 1

Storing images in databases is often frowned upon. To create an in memory file in R you can use a textConnection as a connection. This will give you the string. It will work if you don't forget to set the proper mime type and open the connection as binary.

Answer 2

BLOB sounds like a terrible idea. Why not upload the plot to a server and write the filename into the database? You can then use php to display those plots.

Answer 3

Regardless of if you think this is a terrible idea, here is a working answer I was able to piece together from this post

## open connection
library(RODBC)
channel <- odbcConnect("")

## generate a plot and save it to a temp file
x <- rnorm(100,0,1)
hist(x, col="light blue")
savePlot("temp.jpg", type="jpeg")

## read back in the temp file as binary
plot_binary <- paste(readBin("temp.jpg", what="raw", n=1e6), collapse="")

## insert it into a table
sqlQuery(channel, paste("insert into test values (1, x'",plot_binary,"')", sep=""))

## close connection
odbcClose(channel)

Before implementation, I'll make sure to do some soul searching to decide if this should be used rather than using the servers file system.

Answer 4

Save the plot to a server and write the filename into the database will work. But there's this thing called Rapache may help. Plus, Jeroen Ooms has some online demo, including a web interface for Hadley Wickham's famous R Graph package ggplot2.