RODBC string getting truncated

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 开发者_如何学Pythonshow 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

---

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.

---

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)

---

Another possibility why the retrieved number of characters might be limited is a 'sanity' check restriction to 65535 bytes in the RODBC package itself -- as mentioned here.