Skip to content
Prev 101139 / 398500 Next
Original Raw

RODBC: longest string is truncated

Jerome Asselin 
Hello R community:

I'm encountering unexpected behaviour as I download string data from
MySQL using the RODBC package. The unexpected behaviour is that the last
character of the string is truncated. As far as I can see, this happens
when some strings are of length 501 or more.

Here's how to reproduce this behaviour.

## In MySQL, pick a database and run this:
CREATE TABLE test (
  v1 text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO test
set v1=concat(repeat('a',499),'1'); # length 500
INSERT INTO test
set v1=concat(repeat('a',499),'12'); # length 501
INSERT INTO test
set v1=concat(repeat('a',499),'123'); # length 502

## In R, first setup a channel to connect to your MySQL database.
# library(RODBC)
# channel <- odbcConnect("mydb")
## Now run this:
sqlQuery(channel, "select * from test", as.is=T)$v1

You'll see the entire strings, except for the last character of the 3rd
record, which is "3". Strange enough, if I remove the last record from
the MySQL table and reload the data in R, I see that the "2" of the 2nd
record is truncated. Looks like only the string with greatest length is
truncated.

Can you reproduce this behaviour? Is this a bug? Can someone please
explain why the last character is truncated?

Many thanks for your help.
Jerome

OS: RHEL 4 AS
R version 2.3.1
RODBC version 1.1-7