[R] RMySQL and Blob

[Sean Davis]

Jonathan,

Just a suggestion, but why not just export the images to disk, save a 
URL to them, and do your queries as a two-step process.  1) get URL and 
2) load URL.  This seems to solve both problems.  You just add a table 
to the MySQL database with the urls, fast and easy to work with, and 
use the pixmap library 
(http://cran.us.r-project.org/src/contrib/Descriptions/pixmap.html) to 
load the images from the resulting url.  This may be faster than using 
the database anyway, but even if not, I don't think it would be much 
slower and certainly seems simpler (comments?).

Sean

On Sep 23, 2004, at 8:31 PM, <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.
>
> 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
>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>> PLEASE do read the posting guide! 
>>> http://www.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
>
> ______________________________________________
> 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