Hi Jeff,
I think you are more right than me. I have not much experience with R-specific
objects and Databases. I just know, that for languages like Java, C, etc. this
process is a usual one for storing for example matrices (in case of course that
I do not have to access specific elements inside of the matrix but only the
matrix as a whole).
Thank you for the tip with the R-sig-DB list. I switch over to this list.
Best
Simon
On Jul 16, 2013, at 2:34 AM, Jeff Newmiller <jdnew...@dcn.davis.ca.us> wrote:
> 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.
