RODBC string getting truncated

Question

I am fetching data from MySql Server into R using RODBC.
So in one column of the database is a character vector

SELECT MAX(CHAR_LENGTH(column)) FROM reqtable;

RETURNS 26566

Now I will show you an example how I am running into the problem

`library(RODBC)
 con <- odbcConnect("mysqlcon")
 rslts <- as.numeric(sqlQuery(con,
                          "SELECT CHAR_LENGTH(column) FROM reqtable LIMIT 10",
                          as.is=TRUE)[,1])

` returns

> rslts
 [1]  62  31  17 103  30 741  28  73  25 357

where as rslts <- nchar(as.character(sqlQuery(con, "SELECT column FROM reqtable LIMIT 10", as.is=TRUE)[,1])) returns

> rslts
 [1]  62  31  17 103  30 255  28  73  25 255

So strings with length > 255 is getting truncated at 255. Is there a way I can get the full string.

Thanks

Answer 1

The PostgreSQL ODBC driver has a variable called MaxLongVarcharSize that I have found set to 8190 by default (I've used it both on Windows and Ubuntu). It is possible that the MySQL ODBC driver has a similar variable set to 255.

Answer 2

You could try to use another db driver such as JDBC. In my experience this has sometimes solved the problem.

Also, try the RMySQL package (current binaries need to be compiled. if you do compile them yourself, request you to please share with the community)

Probably the source of the RODBC package "could" provide insights into the default length limitations if any. (I haven't looked at it yet, but I will soon and post an update here)