[R-sig-DB] RODBC / MySQL magical limit of 32 Kbyte

[Peter Meißner]

Dear listeners,

I try to use a MySQL database (with RODBC) for storing files. Above a 
file size of 32 KB I seem to hit a wall and the code below that serves 
me well otherwise fails to store and retrieve the whole file - only 
parts come back (although it seems that the whole file was stored in the 
database).

Is this normal? Does somebody have an idea if this is database related 
or due to R or RODBC or if my code just is nuts?

Any suggestions for where to look for for a solution are more than welcome.

Best, Peter


System:
R version         2.15.3 (2013-03-01) -- "Security Blanket"
Platform:         x86_64-w64-mingw32/x64 (64-bit)
DBMS Version:    "5.6.13"
Driver_ODBC_Ver: "03.51"
Driver Name:     "myodbc5w.dll"
Driver Version:  "05.02.0005"

SQL ot create the table:
CREATE TABLE files (id INTEGER AUTO_INCREMENT, file longtext, PRIMARY 
KEY (id));


CODE:

# define file for upload
     fileIn  <- "Hallo1.docx"

# define name of file to store data in
     fileOut <- "Hallo1Copy.docx"

# read in file
     dataIn <- readBin(fileIn, what="raw", n=file.info(fileIn)$size)

# collapse string
     dataInString <- paste(dataIn,collapse="")

# loading package
     require(RODBC)

# establishing connection
     con <- odbcConnect("MyDatabase",rows_at_time = 1, believeNRows = FALSE)

# uploading data to column file in table files
     dataDBString <- dataInString
     sql <- paste0("INSERT INTO files (file) VALUES ('",dataDBString,"') ;")
     sqlQuery(con, sql)

# downloading data again
     res <- sqlQuery(con,
         "SELECT file FROM files WHERE id = (SELECT max(id) FROM files) ;",
         stringsAsFactors=F, rows_at_time=1,
         as.is=T)
     dataOutString <- res$file

# closing connection
odbcClose(con)

# splitting data
dataOut <- substring(   dataOutString,
                         seq(1, nchar(dataOutString), 2),
                         seq(2, nchar(dataOutString), 2))

# checking how many bytes of dataIn and dataOut are identical
# --> there seems to be a magical wall above 32813 bytes (665626 characters)
print(table(dataIn==dataOut))

# write to file and check if file works
writeBin(as.raw(as.hexmode(dataOut)) , fileOut)
browseURL(fileOut)