CODEWITHSUNDEEP
mysqlrrodbc
sayan dasgupta
sayan dasgupta

Reputation: 1082

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 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

Upvotes: 6

Views: 4292

Answers (3)

petermeissner
petermeissner

Reputation: 12860

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.

Upvotes: 0

MiG62
MiG62

Reputation: 198

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.

Upvotes: 6

harshsinghal
harshsinghal

Reputation: 3780

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)

Upvotes: 1

Related Questions