Import and display a jpeg from a SQL Server database using r

Work has asked us to import a series of jpeg files from a SQL Server database for display on a dashboard product we have. We are using R to query our db and build our dashboards so we need to be able to import the images from SQL Server and display them (or at least export to a local system folder).

We can pull down and display standard table information like File Name, File Size, File Type, and File Data <raw>, but we are having a hard time understanding how to extract and display the jpeg files.

I know they are stored in the database in a varbinary column types, but the standard read/writeJPEG() doesn’t seem to like anything without a file path, for example pulling the data directly from the SQL Server database into r.

Tried pulling a single file for export to local folder, but same problem with loading the image.

Any suggestions on how to proceed?

Answer

The jpeg::readJPEG function says it can read from raw vectors and you seem to have raw vectors in your data.frame. So try something like

jpeg::readJPEG(dd$FileData[[1]])

For something reproducible, here’s an example that reads a sample image from the jpeg package in as a raw vector and then turns that into an image object

#sample data
fname <- system.file("img", "Rlogo.jpg", package="jpeg")
filedataraw <- readBin(fname, "raw", file.info(fname)$size)

Now that we’ve created a sample raw vector (like the one that’s presumably stored in your dataframe), we can parse the image data with the jpeg library

img <- jpeg::readJPEG(filedataraw)
plot(1:2, type='n')
rasterImage(img, 1.2, 1.27, 1.8, 1.73)

If the goal is just to write to a file, you don’t need to parse the file as a JPEG at all. You can just write the bytes to disk.

writeBin(filedataraw, "sample.jpeg")