R : readBin and writeBin ( for storing/retrieving MySQL BLOBs or LONGBLOBs )

Question

I'm using the readBin function to save a file as MySQL BLOB as described in this article ( http://www.r-bloggers.com/save-r-plot-as-a-blob/ )

plot_binary <- paste(readBin("temp.png", what="raw", n=1e6), collapse="")

My question is : Once this is in the database how do I dump it back into a file ?

> f = file ( "backIntoFile.png", "wb")
> writeBin(object = plot_binary, con = f ) 
> close(f)

This does not work ; the file does not seem to be a valid png ;

CHeers!

score 3 · Answer 1 · answered Jul 23 '12 at 08:48

3

Best if you do not use "paste" because it change the raw data vector into a string which cannot be written back as a binary file. Try

plot_binary <- readBin("temp.png", what="raw", n=1e6)

> f = file ( "backIntoFile.png", "wb")
> writeBin(object = plot_binary, con = f ) 
> close(f)

Have I answered your question?

answered Jul 23 '12 at 08:48

hey, it partially answers my question ; now the only issue is that I need it to be a string in order to insert it in MySQL as BLOB; :-( – MadSeb Jul 23 '12 at 17:39

score 3 · Accepted Answer · edited May 23 '17 at 12:14

Here's the best solution I found so far. The DbDownloadImages function takes a very short time to execute ( pretty much no time at all actually ) .

# Helper function I use
Paste <- function(string, vals)
{
    string <- gsub(x = string, pattern = '\\?', replacement = "%s")
    result <- do.call(sprintf, as.list(c(string,vals)))
    return(result)
}
# conn is a RMySQL connection object
DbInsertImage <- function( conn, link.to.file ) 
{

        binary = readBin ( link.to.file , what = "raw", n = 1e6 ) 
        binary.str = paste ( binary, collapse = "" ) 
        statement = Paste ( "CALL InsertImage('?')" , c(binary.str))
        dbSendQuery(conn, statement )
        return(GetIdentity(conn)) # one of my helper functions ; 
            # it returns the "LAST INSERT ID" 
}

#conn is a RMySQL connection object 
DbDownloadImage <- function( conn, id, destination) 
{

    query = "SELECT Data FROM Image WHERE Id = ? LIMIT 1" 
    query = Paste( query, c( id ) )
    result = dbGetQuery(conn, query )$Data[1]

    sst <- strsplit(result, "")[[1]]
    result <- paste0(sst[c(TRUE, FALSE)], sst[c(FALSE, TRUE)])
    result <- as.raw ( as.hexmode ( result ) ) 

    f = file ( destination, "wb")

    writeBin(object = result, con = f ) 
    close(f)
}

Also see: How to split a string into substrings of a given length?

score 1 · Answer 3 · answered Jul 23 '12 at 18:12

Here's my solution:

binary.string <- paste(readBin("temp.png", what="raw", n=1e6), collapse="-")

Save this objet into the database as a BLOB

How to re-save this as png after downloading from the database ?

> split = strsplit ( binaryString, split = "-" )
> split = unlist ( split )
> back.to.binary = as.raw ( as.hexmode ( split ) ) 
> f = file ( "backIntoFile.png", "wb")
> writeBin(object = back.to.binary, con = f ) 
> close(f)

score 0 · Answer 4 · answered Jul 24 '12 at 02:02

Ok here I add another solution. First, to get the size of the file you may use

sz <- as.integer(system("stat --format %s temp.png", intern=T))

Besides this, MadSeb's answer made me understand what the original question was really for. Adding "-" between two bytes is a good solution, however, in case you have to save the 1/3 disk space, here is a stupid way: (it takes a long time)

plot_binary <- paste(readBin("temp.png", what="raw", n=1e6), collapse="")
theBinary <- unlist(lapply((1:(nchar(plot_binary)/2))*2, function(i)return(as.raw(as.hexmode(substr(plot_binary,i-1,i))))))

Thanks for this solution ; I found a solution that has a very good execution time ; I posted it below; Cheers ! — MadSeb, Jul 24 '12 at 20:29

R : readBin and writeBin ( for storing/retrieving MySQL BLOBs or LONGBLOBs )

4 Answers4