[R] RODBC: longest string is truncated

Jerome Asselin jerome.asselin at crchum.qc.ca
Wed Oct 4 23:55:02 CEST 2006


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

-- 
Jerome Asselin, M.Sc., Agent de recherche, RHCE
CHUM -- Centre de recherche
3875 rue St-Urbain, 3e etage // Montreal QC  H2W 1V1
Tel.: 514-890-8000 Poste 15914; Fax: 514-412-7106



More information about the R-help mailing list