r - Reconstitute PNG file stored as RAW in SQL Database -

i working toward writing report sql database (windows sql server) require people sign report before submitting client. hoping have system these people can authorize signature in database, , can use image of signature stored in database , place on report generated latex.

the signature images created pngs, stored in database in field type varbinary. in order use signature in report, need reconstitute png file can \includegraphics in latex.

unfortunately, can't seem recreate pngs out of data base. since can't post signature, we'll use image below example.

with image on computer, i'm able read file raw, write different file, , same image when open new file.

#* works read image file , rewrite elsewhere pal <- readbin("c:/[filepath]/colorpalette.png",           = "raw", n = 1e8) writebin(pal,          "c:/[filepath]/colors.png",          usebytes=true) 

now, i've saved same image database, , using rodbc, can extract so:

#*** capture raw database con <- odbcconnect("database") users <- sqlquery(con, "select * dbo.[user]")  db_pal <- users$signature[users$lastname == "myname"]  #*** write db_pal file, image won't render #*** window photo viewer can't open picture because file appears damaged, corrupted, or large (12kb)  writebin(db_pal[[1]],          "c:/[filename]/db_colors.png",          usebytes=true) 

the objects pal , db_pal defined here in this gist (they long fit in allowable space here)

note: db_pal list of 1 raw vector. also, it's different raw vector pal

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

any thoughts on may need image out of database?

well, we've figured out solution. raw vector being returned through rodbc did not match in sql database. somewhere in pipeline, varbinary object sql getting distorted. i'm not sure why or how. this answer different problem inspired recast variables. recast them, see correct representation.

the next problem of our images more 8000 bytes, , rodbc allows 8000 characters @ time. had fumble way around that. code below following:

1. determine largest number of bytes in image file
2. create set of variables (imagepart1, ..., imagepart[n]) breaking image many parts necessary, each max length 8000.
3. query database of images.
4. combine image parts single object
5. write images local file.

the actual code

library(rodbc)  lims <- odbcconnect("database")  #* 1. determine largest number of bytes in largest image file imagelength <- sqlquery(lims,                              paste0("select maxlength = max(len(u.image)) ",                                    "from dbo.[user] u"))  #* create query string make set of variables breaking #* images many parts necessary, each  #* max length 8000 n_img_vars <- imagelength$maxlength %/% 8000 + 1  start <- 1 + 8000 * (0:(n_sig_vars - 1)) end <- 8000 + 8000 * (0:(n_sig_vars - 1))  img_parts <- paste0("imagepart", 1:n_img_vars,                      " = cast(substring(u.image, ", start,                     ", ", end, ") varbinary(8000))")  full_query <- paste0("select u.oid, u.lastname, u.firstname,\n",                      paste0(sig_parts, collapse =",\n"), "\n",                      "from dbo.[user] u \n",                      "where len(u.image) > 0")  #* 3. query database images images <- sqlquery(lims, full_query)  #* 4. combine images parts single object images$full_image <-    apply(images[, grepl("imagepart", names(images))], 1,          function(x) do.call("c", x))  #* 5. write images 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)) }