I could be wrong, but I would guess that doing what you are describing is very unusual. Most of the time the data frame is mapped to a table in the database so the rows can be searched. Storing data frames as BLOBs really seems odd.
Note that there is an R-sig-db mailing list for questions of this type. --------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnew...@dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k --------------------------------------------------------------------------- Sent from my phone. Please excuse my brevity. Simon Zehnder <szehn...@uni-bonn.de> wrote: >Dear R-Users, > >I need a very fast and reliable database solution so I try to serialize >a data.frame (to binary data) and to store this data to an SQLite >database. > >This is what I tried to do: > >library(RSQLite) >con <- dbDriver("SQLite") >db <- dbConnect(con, "test") >dbSendQuery(db, 'CREATE TABLE frames("simID" INT, "data" BLOB)') >data.bin <- serialize(iris, NULL, ascii = FALSE) >dbSendQuery(db, paste("INSERT INTO frames VALUES(1, X'", data.bin, >"')", sep = "")) >data.bin2 <- dbGetQuery(db, "SELECT DATA FROM frames WHERE simID = 1") >data.bin2 > data >1 58 > >So, only the first entry of data.bin is saved to the database. I tried >to first convert the binary data to raw data: >data.raw <- rawToChar(data.bin) >Error in rawToChar(data.bin) : >embedded nul in string: >'X\n\0\0\0\002\0\003\0\001\0\002\003\0\0\0\003\023\0\0\0\005\0\0\0\016\0\0\0\x96@\024ffffff@\023\x99\x99\x99\x99\x99\x9a@\022\xcc\xcc\xcc\xcc\xcc\xcd@\022ffffff@\024\0\0\0\0\0\0@\025\x99\x99\x99\x99\x99\x9a@\022ffffff@\024\0\0\0\0\0\0@\021\x99\x99\x99\x99\x99\x9a@\023\x99\x99\x99\x99\x99\x9a@\025\x99\x99\x99\x99\x99\x9a@\023333333@\023333333@\021333333@\027333333@\026\xcc\xcc\xcc\xcc\xcc\xcd@\025\x99\x99\x99\x99\x99\x9a@\024ffffff@\026\xcc\xcc\xcc\xcc\xcc\xcd@\024ffffff@\025\x99\x99\x99\x99\x99\x9a@\024ffffff@\022ffffff@\024ffffff@\023333333@\024\0\0\0\0\0\0@\024\0\0\0\0\0\0@\024\xcc\xcc\xcc\xcc\xcc\xcd@\024\xcc\xcc\xcc\xcc\xcc\xcd@\022\xcc\xcc\xcc\xcc\xcc\xcd@\023333333@\025\x99\x99\x99\x99\x99\x9a@\024\xcc\xcc\xcc\xcc\xcc\xcd@\026\0\0\0\0\0\0@\023\x99\x99\x99\x99\x99\x9a@\024\0\0\0\0\0\0@\026\0\0\0\0\0\0@\023\x99\x99\x99\x99\x99\x9a@\021\x99\x99\x99\x99\x99\x9a@\024ffffff@\024\0\0\0\0\0\0@\022\0\0\0\0\0\0@\021\x99\x99\x99\x99\x99\x9a@\024\0\0\0\0\! > > > 0\0 > >I don't know what this error should tell me. Then I tried to use the >ASCII format > >data.ascii <- serialize(iris, NULL, ascii = TRUE) >data.raw <- rawToChar(data.ascii) >dbSendQuery(db, "DELETE FROM frames") >dbSendQuery(db, paste("INSERT INTO frames VALUES(1, X'", data.raw, >"')", sep = "")) >Error in sqliteExecStatement(conn, statement, ...) : > RS-DBI driver: (error in statement: unrecognized token: "X'A > >This also does not work. It seems the driver does not deal that nicely >with the regular INSERT query for BLOB objects in SQLite. Then I used a >simpler way: > >dbSendQuery(db, "DELETE FROM frames") >dbSendQuery(db, "DROP TABLE frames") >dbSendQuery(db, 'CREATE TABLE frames("simID" INT, "data" TEXT DEFAULT >NULL)') >dbSendQuery(db, paste("INSERT INTO frames VALUES(1, '", data.raw, "')", >sep = "")) >data.bin2 <- dbGetQuery(db, "SELECT data FROM frames WHERE simID = 1") > >Nice, that worked. Now I want to unserialize the data: > >unserialize(data.bin2) >Error in unserialize(data.bin2) : 'connection' must be a connection > >unserialize(data.bin2[1, 'data']) >Error in unserialize(data.bin2[1, "data"]) : > character vectors are no longer accepted by unserialize() > >I feel a little stuck here, but I am very sure, that converting >data.frames to binary data and storing them to a database is not that >unusual. So I hope somebody has already done this and could give me the >missing piece. > > >Best > >Simon > >______________________________________________ >R-help@r-project.org mailing list >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide >http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code. ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.