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

This question solved the problem for me: http://stackoverflow.com/questions/22366195/rodbc-sqlquery-returns-varchar255-when-it-should-return-varcharmax — bart, Jun 03 '16 at 00:01

score 6 · Accepted Answer · answered Jun 15 '11 at 01:34

6

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.

answered Jun 15 '11 at 01:34

MiG62

198
4
10

Hey any idea how to check for it and edit it ? I guess it is a similar issue – sayan dasgupta Sep 08 '11 at 16:05
2

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. – MiG62 Sep 13 '11 at 19:26

score 1 · Answer 2 · answered Jan 07 '11 at 08:51

1

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)

answered Jan 07 '11 at 08:51

harshsinghal

3,720
8
35
32

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 :( – sayan dasgupta Jan 09 '11 at 06:54

score 0 · Answer 3 · edited May 23 '17 at 11:53

0

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.

edited May 23 '17 at 11:53

Community

1
1

answered Nov 26 '13 at 22:04

petermeissner

12,234
5
63
63

RODBC string getting truncated

3 Answers3

Linked