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 <[EMAIL PROTECTED]> 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,
> [EMAIL PROTECTED]
> 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
______________________________________________
R-help@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.
