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

[Prof Brian Ripley]

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.

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595