[R] RODBC Update image file into a MS-SQL database table

Baan Bapat b.bapat at decisioncraft.com
Wed Feb 10 10:21:37 CET 2010


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:
   pngfile <- file(<filename>, "rb")
   N <- 1e6
     pngfilecontents <- readBin(pngfile, what="raw", n=N)
     if(length(pngfilecontents) == N) N <- 5 * N else break
#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 pngfile handle directly

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

This also fails.

Please advise.


More information about the R-help mailing list