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
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
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 ----- Original Message ---- From: Prof Brian Ripley <ripley at stats.ox.ac.uk> To: Mikkel Grum <mi2kelgrum at yahoo.com> Cc: r-help at stat.math.ethz.ch Sent: Sunday, November 25, 2007 2:05:37 PM Subject: Re: [R] truncated fields with RODBC 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