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

[Christian Ruckert]

Sean Davis schrieb:
> 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

(Un-)encode with rawToChar now worked for a small object, but for bigger 
objects the string is truncated.

Thanks for your efforts, I've received an impression whats possible and 
what not. I think I'll give the file system solution a try.

Christian