[R] truncated fields with RODBC

[Mikkel Grum]

Thanks. I tried the following syntax and got the same
error message:

sqlSave(pg, Grids, append = TRUE, rownames = FALSE,
   typeInfo = list(double = "double precision",
      integer = "integer", character = "text"))

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 database table was created with the following
createdb pgBase

CREATE TABLE grids
(
  scoutdate date,
  sectorid integer,
  trait text,
  grids text
) 
CREATE INDEX gridssst
  ON grids
  USING btree
  (scoutdate, sectorid, trait);
ALTER TABLE grids CLUSTER ON gridssst;

Once the database tables are created and an ODBC
driver set up, the following code should reproduce the
issue in R:
library(RODBC)
pg <- odbcConnect("pgBase", uid="postgres",
   pwd="whatever", case="tolower")
ScoutDate <- rep(Sys.Date(), 2)
SectorId <- rep(888, 2)
Trait <- c("Seed colour", "Plant height")
Grids <- as.data.frame(cbind(ScoutDate, SectorId,
   Trait))
Grids$Grids[1] <- paste(c(round(rnorm(5000, 10, 5))),
   collapse = " ")
Grids$Grids[2] <- paste(c(round(rnorm(5000, 10, 5))),
   collapse = " ")
sqlSave(pg, Grids, append = TRUE, rownames = FALSE,
   typeInfo = list(double = "double precision",
      integer = "integer", character = "text"))

> odbcGetInfo(pg)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver
Data_Source_Name 
    "PostgreSQL"          "8.2.4"          "03.00"    
  "pgBase" 
     Driver_Name       Driver_Ver         ODBC_Ver    
 Server_Name 
  "PSQLODBC.DLL"     "08.02.0300"     "03.52.0000"    
 "localhost" 
> sessionInfo()
R version 2.6.0 (2007-10-03) 
i386-pc-mingw32 

locale:
LC_COLLATE=English_Ireland.1252;LC_CTYPE=English_Ireland.1252;LC_MONETARY=English_Ireland.1252;LC_NUMERIC=C;LC_TIME=English_Ireland.1252

attached base packages:
[1] stats     graphics  grDevices utils     datasets 
methods   base     

other attached packages:
[1] RODBC_1.2-2



--- Prof Brian Ripley <ripley at stats.ox.ac.uk> wrote:

> 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 sports nut!  Let your teams follow you