R converts SQL varbinary to bytes (?) and cannot convert these to the original jpg image

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:

After making an OBDC connection, run the following in R:

channel <- odbcConnect("SQL_Connection_R_Demo")
XY <- sqlQuery(channel, "SELECT * FROM [dbo].[ImportedImages]" ,error=TRUE)
[1] "rlogo.jpg"
[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 ]

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,

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.