How to upload an image to SQL Server in R

Question

I am creating some graphs which I want to update into a database table. The procedure I am following is:

1. create the graphs as a png/jpeg file.
2. Read that file as a binary vector
3. sqlUpdate

My code for steps 2 & 3:

pngfile <- file(<filename>, "rb")
N <- 1e6
repeat{
  pngfilecontents <- readBin(pngfile, what="raw", n=N)
  if(length(pngfilecontents) == N) N <- 5 * N else break
}
close(pngfile)

There is a table df_DemandPatternMaster in the database with primary key DemandPatternID, with appropriate record in place with NULL value in pngFile field.

update.query <- "update df_DemandPatternMaster set "
update.query <- paste( update.query, " pngFile = '", serialize(pngfilecontents, NULL) , "' where DemandPatternID = ", , sep="")
d <- sqlQuery(connection, update.query)

I end up inserting only a byte of data. The reason it seems is that paste sees the serialized vector and creates a vector with the prefix & suffix text. I have also tried passing the png file handle directly

pngfile <- file(<filename>, "rb")
update.query <- paste( update.query, " pngFile = '", pngfile, "' where DemandPatternID = ", , sep="")

This also fails.

Please advise.

Tempietempla

Closely related question: #2046337 – Sotted

Answer 1

Perhaps if you collapse the pngfilecontents vector into a single string. Something like:

update.query <- "update df_DemandPatternMaster set "
update.query <- paste( update.query, " pngFile = '", paste(pngfilecontents, collapse="") , "' where DemandPatternID = ", sep="")

Time

Thanks Mark. paste(pngfilecontents, collapse="") works. I did not try JD Long's suggestion. – Tempietempla

@Baan, you're welcome. If you could, please mark this answer accepted by clicking the check mark to the left. This cleans up the unanswered questions and I'd appreciate the reputation points :) – Time

Answer 2

I have not tried this with a database, but I had some challenges recently when serializing to/from a text file. Here's a question I asked that might be related. Have you tried using the ascii=T switch with serialize? Then try it both with and without rawToChar.

I don't have an easy environment to test your code, but I am interested in what you come up with. I'm working on some code where I will eventually be serializing objects and putting them in a DB. I'm just not to that point yet.

Maugre