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

Stopping

This question solved the problem for me: #22366695 – Udella

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.

Lemaster

Hey any idea how to check for it and edit it ? I guess it is a similar issue – Stopping

On Windows you select your data source (in Control Panel | Administrative Tools | ODBC Sources) and click configure, then select the button that says data source. The MaxLongVarCharSize variable is right there. On Ubuntu you add the line MaxLongVarcharSize = 256000 (or whatever) to the relevant data source in the file /etc/odbc.ini. – Lemaster

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)

Ranaerancagua

Hey thanks ya it works perfectly fine with a JDBC connection. Yet it is not clear why it does not work properly with a ODBC connection :( – Stopping

Answer 3

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.

Wittenburg