2

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

Morgan
  • 21
  • 1
  • 3

2 Answers2

0

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.

Community
  • 1
  • 1
CL.
  • 173,858
  • 17
  • 217
  • 259
  • "You would then have to convert the hex digits back into binary data." How would you go about doing that in R? – user5359531 Jan 24 '17 at 04:23
  • The answer to the linked question shows how to convert the hex representation to raw bytes. – CL. Jan 24 '17 at 06:20
  • I've read the linked answer several times but still cannot tell which part is doing the actual conversion. Would you be able to point it out, or list a simple example? – user5359531 Jan 24 '17 at 15:50
0

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)))
}
Mario Angst
  • 26
  • 3