2

I'm trying to store large list objects created in R to an SQLite database via RSQLite. Since These list objects contain several 2d and 3d matrices, I'd like store them as individual entries. I read serializing these and storing them as blobs does the trick.

The Problem is however that my code does not appear to store the blobs as individual rows, instead rather storing each separate byte as row. Here is my code:

    library(RSQLite)
out1 <- serialize(model1,NULL)
out2 <- serialize(model2,NULL)
out3 <- serialize(model3,NULL)

model4 <- serialize(rnorm(10),NULL)
model5 <- serialize(rnorm(20),NULL)
model6 <- serialize(rnorm(30),NULL)

db <- dbConnect(SQLite(), dbname="Test.sqlite")
dbGetQuery(conn = db, 
            "CREATE TABLE IF NOT EXISTS models 
            (_id INTEGER PRIMARY KEY AUTOINCREMENT, 
            model BLOB)")


test4 <- data.frame(g=I(model4))
test5 <- data.frame(g=I(model5))
test6 <- data.frame(g=I(model6))
dbGetPreparedQuery(db, "INSERT INTO models (model) values (:g)", bind.data=test4)
dbGetPreparedQuery(db, "INSERT INTO models (model) values (:g)", bind.data=test5)
dbGetPreparedQuery(db, "INSERT INTO models (model) values (:g)", bind.data=test6)

dbListTables(db)

p1 = dbGetQuery( db,'select * from models' )

Also, while the writing process works fine in this case, it is incredibly slow with files larger than 1000kb...

user3641187
  • 405
  • 5
  • 10
  • What version of RSQLite are you using? You might need to wrap your serialized objects twice: `g = I(list(model4))`. – krlmlr Sep 25 '16 at 13:01
  • I'm using the latest package offering, which should still be 1.0. But your suggestion actually did the tricck! – user3641187 Sep 25 '16 at 15:31

0 Answers0