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

[adam_pgsql]

On 1 Oct 2008, at 11:42, Christian Ruckert 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?

we have used something like this (these are snippets which will not  
work as is... but should give you a starting point)

To Save:
     s_rdata <- if(is.null(rdata)) "" else serialize(rdata,  
connection=NULL, ascii=TRUE)

     if(class(s_rdata) == "raw") s_rdata <- rawToChar(s_rdata)

     sql <- paste("INSERT INTO my_table (dataset_id, rdata) ", "VALUES  
(", dataset_id, ", E'", s_rdata, "')", sep="")
     dbSendQuery(dbh, sql)

To Load:
   sql <- paste("SELECT * FROM my_table WHERE dataset_id =  
",dataset_id, sep="")
   dataset <- dbGetResult(dbSendQuery(dbh, sql))

   for(n in 1:length(dataset$rdata_type)) {
     rdata_type <- dataset$rdata_type[n]
     s_rdata <- dataset$rdata[n]

     rdata <- if(nchar(s_rdata) > 0) unserialize(s_rdata) else NULL
   }

(this was for use with PostgreSQL by the way, hence the E'' syntax  
above)

HTH

adam