[R] truncated fields with RODBC

[Mikkel Grum]

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


      ____________________________________________________________________________________
Be a better sports nut!  Let your teams follow you