[R-sig-DB] Saving R-objects to a database

[Sean Davis]

On Wed, Oct 1, 2008 at 8:54 AM, Prof Brian Ripley <ripley using 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 using 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