Lack of BLOB support

[gtmaskall]

dbGetQuery(conn, "SELECT a FROM table") where a is a BLOB results in message about truncated field (and no data). Equivalent operation using RSQLite results in correct raw vector being returned, which can then be read as binary. My workaround with RMySQL is to request the server to cast the field to a HEX string, which you can then parse into a RAW and then read as binary. It would be great to have the same functionality for BLOBs in RMySQL as in SQLite.

[krlmlr]

Thanks. This looks like a known issue (#49), we will report there as soon as there is progress on that front.

[ElianoMarques]

gtmaskall - could you share your workaround in more detail? I'm facing the same issue and I'm trying to find a workaround to this.

[gtmaskall]

Hi @ElianoMarques ,

Extract the BLOB by getting the server to convert the data to a HEX string, like
SELECT HEX(myvar) AS myvar FROM mytable
Then you can use a function I wrote to convert from string to raw here. I made it as fast as I could (there are some very slow ways of doing this!) Someone may well be able to optimize this even more, but it's acceptably fast even for quite large arrays that we deal with.

Hope this helps,
Guy

[ElianoMarques]

Thanks. I wrote another way that just works nicely. If you store the object as text with the following:

paste(serialize(object),collapse = ";").

Then store the object as text into Mysql. then use the following:

#please note that object below is returned column from the mysql database. so for example a simple dbreadtable would do the job. 
unserialize(as.raw(as.hexmode(strsplit(object, ";")[[1]])))

Hope this also works for you.

[gtmaskall]

Thanks for the suggestion, but I don't have control of the stored data; I have to work with it "as is".

[krlmlr]

Closing as duplicate.