Dear R Studio community,
I am using R to pull image strings stored in a SQL database but fail to convert those strings back to the original jpg image in R, I hope that someone can help me.
I created the database in Microsoft SQL Server Management Studio 18. One of the tables inside this database is called ImportedImages it is a simple table with two columns (1) ImageFileName, and (2) ImageData; this second column corresponds to the varbinary(MAX) string that SQL generated for each jpg image. When I connected R to the database and queried the ImageData, it generates a data.frame with 2 variables, the second variable is a "class")= chr "ODBC_binary" containing all image strings. When R reads these image strings, it somehow converts these in a different format which appears to be difficult to manage. I have not found a way to convert it to jpg in R.
Here are reproducible steps:
Download the R logo from: https://www.r-project.org/Rlogo.png, save the file in C:\img\ folder, open the file with Paintbrush and save it as jpg, here is a short version of the string:
0xFFD8FFE000104A46494600010101009000900000FFE1005A4578696600004D4D00…8A3FFFD9
After making an OBDC connection, run the following in R:
library(RODBC)
channel <- odbcConnect("SQL_Connection_R_Demo")
XY <- sqlQuery(channel, "SELECT * FROM [dbo].[ImportedImages]" ,error=TRUE)
XY[5,1][1][[1]]
[1] "rlogo.jpg"
XY[5,2][1][[1]]
[1] 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
[36] 0….
…
[981] af 01 00 00 98 dc 58 39 af 01 00 00 f0 1e 54 39 af 01 00 00
[ reached getOption("max.print") -- omitted 7237 entries ]
str(XY[5,2][1][[1]])
raw [1:8237] 00 00 00 00 ...
It appears that R converts the varbinary to bytes but I am not sure:
Rbytes <- XY[5,2][1][[1]]
write.table(Rbytes, "Rbytes.txt", row.names = FALSE, col.names = FALSE)
From here I could not find a way of converting Rbytes to the image; I tried writeBin from Base and readJPEG from library(jpeg). I found a way but it works only for very small images. Basically, in SQL covert the image strings from varbinary(MAX) to nvarchar(MAX) and in R import the character strings as text during the query:
query_string <- "SELECT CAST(ImageData AS text) AS ImageData FROM [dbo].[ ImportedImages]"
xy <- sqlQuery(channel, query_string ,error=TRUE)
rlogoTxt <- xy
With this,
rlogo <- as.raw(strtoi(substring(rlogoTxt, seq(1,nchar(df), by=2), seq(2,nchar(df), by=2)), base=16))
writeBin(rlogo, "rlogo.jpg")
This works but is not optimal and appears to work only for small memory size images.
I tried library(AmigaFFH) but again does not work.
dat <- Rbytes
spr <- rawToHWSprite(dat, c("#EE4444", "#000000", "#EEEECC"))
plot(spr, interpolate = FALSE)
spr <- rawToHWSprite(dat)
plot(spr, interpolate = FALSE)
I appreciate your help.
Thank you,
Rodrigo