[R] truncated fields with RODBC

[Mikkel Grum]

For the record, the problem with truncated fields below was solved by increasing the Max LongVarChar variable in the data source settings page 1 from 8190 to 32760. So it was a psqlODBC problem not an RODBC problem. The command nchar(Grids$Grids) helped me see how large the fields actually were and what size of number I was looking for. 

cheers,
Mikkel


----- Original Message ----
From: Prof Brian Ripley <ripley at stats.ox.ac.uk>
To: Mikkel Grum <mi2kelgrum at yahoo.com>
Cc: r-help at stat.math.ethz.ch
Sent: Sunday, November 25, 2007 2:05:37 PM
Subject: Re: [R] truncated fields with RODBC


You need to study the RODBC documentation: you haven't set the type of
 the 
character fields in the database table correctly (in fact, you seem not
 
to have set them at all, hence will get the default of varchar(255)).

The 64k limit is for reading, not writing.

As ever, full details and a reproducible example are needed for people
 to 
help you fully.

On Sat, 24 Nov 2007, Mikkel Grum wrote:

> I'm changing some functions from storing data in
> SQLite (using RSQLite) to storing it in PostgreSQL
> (using RODBC). When trying to store very long
> character fields I get the following message:
>
>>    sqlSave(pg, Grids, rownames = FALSE, append =
> TRUE)
> Warning messages:
> 1: In odbcUpdate(channel, query, mydata, paramdata,
> test = test, verbose = verbose,  :
>  character data truncated in column 'grids'
> 2: In odbcUpdate(channel, query, mydata, paramdata,
> test = test, verbose = verbose,  :
>  character data truncated in column 'grids'
> 3: In odbcUpdate(channel, query, mydata, paramdata,
> test = test, verbose = verbose,  :
>  character data truncated in column 'grids'
>
> The structure of the dataframe that I'm trying to
> store looks like this:
>> str(Grids)
> 'data.frame':   9 obs. of  4 variables:
> $ ScoutDate: chr  "2007-10-11" "2007-10-11"
> "2007-10-11" "2007-10-11" ...
> $ SectorId : int  93 93 93 93 93 93 93 93 93
> $ Trait    : chr  "eTop" "eMB" "nTop" "nMB" ...
> $ Grids    : chr  "0 0 0 0 0 0 0 53 6064 2364 61 0 0
> 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 74 482
> 524 51 0 0 157 316 0 0 0 0 0 0 0 0 0 0 0"|
> __truncated__ "45 45 45 45 45 45 45 1 0 0 0 45 45 45
> 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45 45
> 45 45 45 45 50 68 70 49 46 46 0 0 3"| __truncated__ "0
> 0 0 0 0 0 0 84 18766 7266 111 0 0 0 0 0 0 0 0 0 0 0 0
> 0 0 0 0 0 0 0 0 0 0 0 0 0 192 1628 1777 112 0 0 409
> 903 0 0 0 0 0 0 0 0"| __truncated__ "94 94 94 94 94 94
> 94 94 94 94 94 94 94 94 94 94 94 94 94 94 94 94 94 94
> 94 94 94 94 94 94 94 94 94 94 94 94 137 312 331 128 94
> "| __truncated__ ...
>
> The same fields could be copied from SQLite into
> PostgreSQL through a | delimited file without any
> error message, so it is not PostgreSQL that is the
> limitation. dbWriteTable in RSQLite was also able to
> handle this without truncating the data. I think these
> fields are 4-5000 characters wide, but don't actually
> know how to get the exact figure.
>
> The offending field is set as a text field in
> PostgreSQL. I'm using psqlODBC on Windows Server 2003
> and R-2.6.0.
>
> Have I missed an argument somewhere that could solve
> the problem? I've read that RODBC has a field length
> limit of 64k. This could be the problem. Is there
> somewhere I could change this in the source code?
> Would that just give me other problems?
>
> Any assistance highly appreciated.
>
> cheers,
> Mikkel

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595





      ____________________________________________________________________________________
Be a better friend, newshound, and