[R] RMySQL and Blob

[David James]

jonathan_li at agilent.com wrote:
> Hi,
> 
> I tried your suggestion to blindly import the blob into R, doing the following:
> 
> > con <- dbConnect("MySQL", host="host", user="user", dbname="db")
> > rs <- dbGetQuery(con, statement=paste("select picture from db where id=1")
> 
> It didn't crash R. But rs is not usable. It seems that it has been converted to a character object.

That's exactly correct -- that's what I mentioned the code does, but
what I was wondering is whether you could make use of such a character 
vector -- now I see that you can't.

> 
> In addition, we need to pass an image format to R, like png, bmp or something. It's unclear to me how to achieve this even if we can read "rs" as a binary object as you suggested.

Indeed the problem is more interesting than just bringing a blob
into R.  In general R cannot possibly know all the blob types
(there are just too many -- images, movies, sounds, binary data of
any kind, and many more yet to be created), so we have to define
a general mechanism for pluging in user-specified converters that
take a pointer to a blob and produce something that can be used
in R computations (see my comments re: a binaryConnection class to
work with binary buffers in my previous email quoted below.)

> 
> Any ideas?

I haven't worked with images, but the email from Sean Davis suggesting
dumping the images to files and then importing them with the pixmap
library seems quite reasonable to me.

Regards,

--
David

> 
> Thanks for suggestions!
> Jonathan
> 
> 
> 
> 
> 
> -----Original Message-----
> From: David James [mailto:dj at research.bell-labs.com]
> Sent: Wednesday, September 22, 2004 10:06 AM
> To: LI,JONATHAN (A-Labs,ex1)
> Cc: David James
> Subject: Re: [R] RMySQL and Blob
> 
> 
> jonathan_li at agilent.com wrote:
> > Hi David,
> > 
> > The application I have in mind is for images. In my case, size of images is known and they are not big. As an example, a 64*32 image will have 2048 pixels. If they are 8-bit grey-level pixels, the image occupies 2KB memory. 
> > 
> > I may venture to guess that the unknown size and type of a blob object in MySQL prevent it from being very usable in R since R doesn't have a datatype for a binary blob?
> 
> You could just blindly try to import it into R (but do it on a clean
> workspace, since it may crash R and you could loose your data!).
> The underlying C code clearly identifies FIELD_TYPE_BLOB and goes
> ahead and puts it in an R character vector (with comments clearly
> stating that it is a hack).  Once it moves the data from the MySQL
> result set buffer to the R vector, it computes the length in both
> places and prints a warning if they differ.
> 
> Or you could try to hack something.  For instance, what happens if
> instead of bringing the blob you import, say, as a string?
>     con <- dbConnect("MySQL", ....)
>     rs <- dbSendQuery(con, "select SUBSTRING(blob, 0) from table")
>     dd <- fetch(rs)
> 
> One possible general solution would be to define a new class
> "binaryConnection" simmilar to textConnection, so that you
> can readBin() and writeBin() from it.  In this way, blobs could
> return a binary buffer (just a pointer to a block of C memory) 
> that could be given to binaryConnection:
> 
>    data <- fetch(rs)
>    for(i in seq(nrow(data)){
>       ## extract blobs from each row and create a binary connection
>       bcon = binaryConnection(blobs$image[1])
>       img = readBin(bcon, "integer", n = 2048)
>       
>       ## work with the image
>    }
> 
> let me know what happens if you try to naively import a blob...
> 
> --
> David
> 
> > 
> > Thanks!
> > Jonathan
> > 
> > 
> > -----Original Message-----
> > From: David James [mailto:dj at research.bell-labs.com]
> > Sent: Wednesday, September 22, 2004 7:05 AM
> > To: LI,JONATHAN (A-Labs,ex1)
> > Cc: r-help at stat.math.ethz.ch
> > Subject: Re: [R] RMySQL and Blob
> > 
> > 
> > Hi Jonathan,
> > 
> > Currently RMySQL doesn't handle blob objects.  The mechanics of
> > inserting and extracting blob objects by itself is not too hard,
> > but issues such as how should blobs be made available to R, how to
> > prevent buffers overflows, how to prevent huge blobs from exhausting
> > the available memory, should R callback functions be invoked
> > as chunks of the blob are brought in, etc., need more consideration.
> > And these issues are not R/MySQL specific, but also relevant to
> > other databases and other non-dbms interfaces.
> > 
> > BTW there are R facilities (e.g., external pointers, finalizers) that 
> > seems quite important for this type of implementation.  
> > 
> > What type and how big are the blobs that want to import?
> > 
> > --
> > David
> > 
> > jonathan_li at agilent.com wrote:
> > > Dear R experts,
> > > 
> > > Does RMySQL package handle Blob datatype in a MySQL database? Blob can represent an image, a sound or some other 
> > > large and complex binary objects. In an article published by R-database special interest group, named "A common database interface (DBI)" (updated June 2003),  it's mentioned in "open issues and limitations" that "We need to carefully plan how to deal with binary objects". 
> > > 
> > > Before I invest time to try, I would appreciate any experts' opinions.
> > > 
> > > Thanks,
> > > Jonathan
> > > 
> > > ______________________________________________
> > > R-help at stat.math.ethz.ch mailing list
> > > https://stat.ethz.ch/mailman/listinfo/r-help
> > > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html
> > 
>