Re: [R] truncated fields with RODBC

From: Mikkel Grum <mi2kelgrum_at_yahoo.com>
Date: Sat, 15 Dec 2007 05:37:28 -0800 (PST)


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

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

______________________________________________
R-help_at_r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
Received on Sat 15 Dec 2007 - 13:48:39 GMT

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Sat 15 Dec 2007 - 14:00:19 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.