Read binary blob field in R from an sqlite database

Question

I would like to read a binary field from an sqlite database from R.

I succeeded with an indirect method, consisting in first exporting the binary field in a temporary file, via a shell command invoking sqlite3 and then reading this binary file with the readbin() command. Here is the detailed code:

library(RSQLite)
fileConn<-file("script.sql")
writeLines(c('.load fileio.dll','SELECT writefile("tmp.bin",Curve) FROM Table1 WHERE Id=41;','.exit'), fileConn)
close(fileConn)

shell('sqlite3.exe database.sqlite < script.sql')

to.read<-file(description="tmp.bin","rb")
tmp<-readBin(to.read,"integer",n=10000,size=2)
plot(tmp)
close(to.read)

I want to directly read the binary field with R, without creating a temporary file via sqlite3.

According to suggestions (thanks a lot for that !), I tried the following:

con <- dbConnect(SQLite(),dbname="database.sqlite")
tmp<-dbGetQuery(con,"SELECT quote(Curve) FROM Table1 WHERE Id=41")
tmp

which retruns:

quote(Curve)
1             '\023'

It doesn't looks good, as I am supposed to get a vector of 870 integer elements, starting with :

head(good)
[1] 19 19  0 19 19 19

What's going wrong with my sql statement ? How can I directly read the binary field with R ?

Thank you very much in advance

Answer 1

Similarly to the solution in How to write binary data into SQLite with R DBI's dbWriteTable()?, you can convert the blob into a blob literal with the quote() function. That is, execute a query like

SELECT quote(Curve) FROM Table1 WHERE ID = 41

and the result is a string in the form

X'1234ABCD....'

You would then have to convert the hex digits back into binary data.

Answer 2

Just to add, I struggled with this today and was successful with the following:

blob2string <- function(blob){
  hex_raw <- wkb::hex2raw(blob)
  rawToChar(as.raw(unlist(hex_raw)))
}