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

[Edward Vanden Berghe]

Hi Peter,

I've had similar problems, with information that got truncated through an RODBC connection - on Windows 64 bits, with RODBC on a PostgreSQL database, with large PostGIS entries. Unfortunately I can't replicate the problem now as I moved on to Ubuntu now. For me the solution was to go to DBI/RPostgreSQL. 

Did you try DBI? Even if you want to stick with RODBC in the future, it might assist in isolating the problem.

Cheers,

Edward

-----Original Message-----
From: r-sig-db-bounces using r-project.org [mailto:r-sig-db-bounces using r-project.org] On Behalf Of Peter Meißner
Sent: 26 November 2013 18:02
To: r-sig-db using r-project.org
Subject: [R-sig-DB] RODBC / MySQL magical limit of 32 Kbyte

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)

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB using r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db