On Wed, Oct 1, 2008 at 8:54 AM, Prof Brian Ripley <ripley at stats.ox.ac.uk> wrote:
On Wed, 1 Oct 2008, Sean Davis wrote:
On Wed, Oct 1, 2008 at 6:42 AM, Christian Ruckert
<cruckert at uni-muenster.de> wrote:
Someone solved the problem of saving R-objects to a database or can
give
me
an answer like "this is not possible at the moment"?
All my attempts runned into problems with conversion between blob, raw
and
character representations. Saving the R-object to a file and then
getting
it
into the db leads to similar problems. Is saving to the file system the
only
practicable solution?
Look at the serialize function. You can serialize any R object which
can be stored into the database of your choice. Give that a try and
let us know with a code example if you have problems.
This is what I posted on r-mailing list before:
ser = serialize(obj, NULL)
dbSendQuery(link, paste("insert into table values(1,
'",ser,"')",sep=''))
The field to save the object in the MySQL Database is of type blob.
result = dbGetQuery(link,"select * from table where id=1")
unser = unserialize(result[1,"object_column"], NULL)
Error in unserialize(result[1, "object_column"], NULL) : unknown input
format
In addition: Warning message:
In unserialize(result[1, "object_column"], NULL) :
unserialize()from a character string is deprecated and will be
withdrawn
in R 2.8.0
Brian Ripley answered:
"DBI needs to convert blobs to raw not character."
So saving to the db works fine, but function dbGetQuery seems to convert
the
result to a character string. Can I avoid this convertion or is this a
problem with DBI package and will it get fixed?
What happens if you serialize using "ascii=TRUE"? Then, you could
probably save into a text field. Admittedly, I haven't tried this.
The result is still a raw vector. You need to encode the raw vector as text
before saving to the DBMS, and unencode after retrieval.
AFAIK the problem referred to is specific to raw vectors and DBI, not
general to all R-DBMS interfaces.
Thanks for the clarification, again.
Christian, it might be easier (and likely faster) to store the URI in
the database and the object in Rda format on disk with a UUID as a
file name. In the database world, I think the general consensus is
that it is best not to store objects like this in the database as the
overhead of backup, storage, retrieval, etc. are higher than storing
on the file system.
Sean
Sean