[R] RODBC: longest string is truncated

[Jerome Asselin]

On Wed, 11 Oct 2006 18:00:12 -0400
  "Yeh, Richard C" <richard.c.yeh at bankofamerica.com> 
wrote:
> Dear Jerome,
> 
> Sorry, I'm not running your configuration.  I cannot 
>reproduce this
> problem with Sybase Adaptive Server IQ 12.6 and R 2.3.1 
>+ RODBC 1.1.7 on
> WinXP SP2 or Linux 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 
>17:52:23 EDT
> 2005 i686 i686 i386 GNU/Linux.
> 
> -- ASIQ uses STRING() to mean CONCAT()
> -- ASIQ doesn't have the 'TEXT' data type
> CREATE TABLE test500
> (
>        a       VARCHAR(600)	--	explicit length
> )
> GO
> INSERT INTO test500
> SELECT STRING(REPEAT('a',499),'1')	-- should be a 
>length-500 string
> GO
> INSERT INTO test500
> SELECT STRING(REPEAT('a',499),'12')
> GO
> INSERT INTO test500
> SELECT STRING(REPEAT('a',499),'123')
> GO
> SELECT LENGTH(a) FROM test500
> -- returns 500, 501, 502
> 
> Then, in R (either platform):
> 
>> library(RODBC)
>> channel = odbcConnect('DSN', believeNRows=FALSE)	#
> believeNRows=FALSE required for ASIQ
>> b = sqlQuery(channel,"SELECT a FROM test500")
>> nchar(as.vector(b[[1]]))
> [1] 500 501 502

Thanks for the test. Seems like not all database engines 
are affected. I've tried your test on R 2.4.0 with RODBC 
1.1-7 and MySQL 4.1.21 on Gentoo Linux. As with RHEL, I 
still observe:

> nchar(as.vector(b[[1]]))
[1] 500 501 501

Can anyone confirm the same?

Regards,
Jerome

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