[R] truncated fields with RODBC

[Prof Brian Ripley]

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