R help - Sep 2004 - RMySQL and Blob

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

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
> stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
R-project.org/posting-guide.html

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?

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
> stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
R-project.org/posting-guide.html

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.

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.

Any ideas?

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
> > stat.ethz.ch/mailman/listinfo/r-help
> > PLEASE do read the posting guide!
R-project.org/posting-guide.html
> 
-- 
David A. James
Statistics Research, Room 2C-276            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636