Hi Rainer, dbWriteTable is a nice function but in my case I need something that can actually save a dataframe in one row of a table. That is why I want to serialize my data.frame.
Best Simon On Jul 16, 2013, at 3:05 PM, Rainer Schuermann <rainer.schuerm...@gmx.net> wrote: > Maybe a simple > > dbWriteTable( db, "frames", iris ) > > does what you want? > > > > On Monday 15 July 2013 23:43:18 Simon Zehnder 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. > - - - - - > > Der NSA keine Chance: e-mail verschluesseln! > http://www.gpg4win.org/ > > ______________________________________________ > 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.