r - Reconstitute PNG file stored as RAW in SQL Database

Question

I am working toward writing a report from a SQL database (Windows SQL Server) that will require certain people to sign the report before submitting it to the client. We are hoping to have a system where these people can authorize their signature in the database, and then we can use an image of their signature stored in the database and place it on the report generated by LaTeX.

The signature images are created as PNGs, then stored in the database in a field with type varbinary. In order to use the signature in the report, I need to reconstitute the PNG to a file that I can with includegraphics in LaTeX.

Unfortunately, I can't seem to recreate the PNGs out of the data base. Since I can't post a signature, we'll use the image below as an example.

With this image on my computer, I'm able to read the file as raw, write it to a different file, and get the same image when I open the new file.

#* It works to read the image from a file and rewrite it elsewhere
pal <- readBin("C:/[filepath]/ColorPalette.png",
          what = "raw", n = 1e8)
writeBin(pal,
         "C:/[filepath]/colors.png",
         useBytes=TRUE)

Now, I've saved that same image to the database, and using RODBC, I can extract it like so:

#*** Capture the raw from the database
con <- odbcConnect("DATABASE")
Users <- sqlQuery(con, "SELECT * FROM dbo.[User]")

db_pal <- Users$Signature[Users$LastName == "MyName"]

#*** Write db_pal to a file, but the image won't render
#*** Window Photo Viewer can't open this picture because the file appears to be damaged, corrupted, or is too large (12KB)

writeBin(db_pal[[1]],
         "C:/[filename]/db_colors.png",
         useBytes=TRUE)

The objects pal and db_pal are defined here in this Gist (they are too long to fit in the allowable space here)

Note: db_pal is a list of one raw vector. Also, it's clearly different than the raw vector pal

> length(pal)
[1] 2471
> length(db_pal[[1]])
[1] 9951

Any thoughts on what I may need to do to get this image out of the database?

Answer 1

Well, we've figured out a solution. The raw vector being returned through RODBC did not match what was in the SQL database. Somewhere in the pipeline, the varbinary object from SQL was getting distorted. I'm not sure why or how. But this answer to a different problem inspired us to recast the variables. As soon as we recast them, we could see the correct representation.

The next problem was that all of our images are more than 8000 bytes, and RODBC only allows 8000 characters at a time. So I had to fumble my way around that. The code below does the following:

1. Determine the largest number of bytes in an image file
2. Create a set of variables (ImagePart1, ..., ImagePart[n]) breaking the image into as many parts as necessary, each with max length 8000.
3. Query the database for all of the images.
4. Combine the image parts into a single object
5. Write the images to a local file.

The actual code

library(RODBC)

lims <- odbcConnect("DATABASE")

#* 1. Determine the largest number of bytes in the largest image file
ImageLength <- sqlQuery(lims, 
                            paste0("SELECT MaxLength = MAX(LEN(u.Image)) ",
                                   "FROM dbo.[User] u"))

#* Create a query string to make a set of variables breaking
#* the images into as many parts as necessary, each with 
#* max length 8000
n_img_vars <- ImageLength$MaxLength %/% 8000 + 1

start <- 1 + 8000 * (0:(n_img_vars - 1))
end <- 8000 + 8000 * (0:(n_img_vars - 1))

img_parts <- paste0("ImagePart", 1:n_img_vars, 
                    " = CAST(SUBSTRING(u.Image, ", start,
                    ", ", end, ") AS VARBINARY(8000))")

full_query <- paste0("SELECT u.OID, u.LastName, u.FirstName,
",
                     paste0(img_parts, collapse =",
"), "
",
                     "FROM dbo.[User] u 
",
                     "WHERE LEN(u.Image) > 0")

#* 3. Query the database for all the images
Images <- sqlQuery(lims, full_query)

#* 4. Combine the images parts into a single object
Images$full_image <- 
  apply(Images[, grepl("ImagePart", names(Images))], 1, 
        function(x) do.call("c", x))

#* 5. Write the images to a local file
for(i in seq_len(nrow(Images))){
  DIR <- "[FILE_DIR]"
  FILENAME <- with(Images, paste0(OID[i], "-", LastName[i], ".png"))
  writeBin(unlist(Images$full_image[i]),
           file.path(DIR, FILENAME))
}