On Tue, 14 Aug 2007, Maciej Hoffman-Wecker wrote:
Dear Professor Ripley,
Thank you very much for your response. I send the problem, as I didn't
have any more ideas were to search for the reason. I didn't say this is
a R bug, knowing the responses on such mails.-)
But I succeeded in developing a tiny example, that reproduces the bug
(wherever it is).
Thank you, that was helpful: much easier to follow that the previous code.
...
library(RODBC)
.con <- odbcConnectAccess("./test2.mdb")
(.d <- try(sqlQuery(.con, "select * from Tab1")))
F1 NO F2
1 1 1 1
2 2 2 2
3 0 NA 1
4 1 0 0
(.d <- try(sqlQuery(.con, "select F1 , NO , F2 from Tab1")))
F1 Expr1001 F2
1 1 0 1
2 2 0 2
3 0 0 1
4 1 0 0
close(.con)
So the problem occurs if the column names are specified within the query.
Is the query "select F1 , NO , F2 from Tab1" invalid?
I believe so. 'NO' is an SQL92 and ODBC reserved word, at least according
to http://www.bairdgroup.com/reservedwords.cfm
See also http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
which says
For existing objects with names that contain reserved words, you can
avoid errors by surrounding the object name with brackets ([ ]).
and lists 'NO' as a reserved word. RODBC quotes all column names it uses
to be sure (and knows about most non-standard quoting mechanisms from the
ODBC driver in use). But this was a query you generated and so you need
to do the quoting.
Regarding the memory issue, I _knew_ that there must be a reason for the
running out of memory space. Sorry for not being more specific. My
question than is:
Is there a way to 'reset' the environment without quitting R and
restarting it?
Sorry, no. You cannot move objects in memory.
But why '477Mb' is coming up is still unexplained, and suggests that the
machine has a peculiar amount of memory or some flag has been used.
Thank you for your help.
Kind regards,
Maciej
-----Ursprüngliche Nachricht-----
Von: Prof Brian Ripley [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 14. August 2007 11:51
An: Maciej Hoffman-Wecker
Cc: r-help@stat.math.ethz.ch
Betreff: Re: [R] Import of Access data via RODBC changes column name ("NO" to
"Expr1014") and the content of the column
On Tue, 14 Aug 2007, Maciej Hoffman-Wecker wrote:
Dear all,
I have some problems with importing data from an Access data base via
RODBC to R. The data base contains several tables, which all are
imported consecutively. One table has a column with column name "NO".
If I run the code attached on the bottom of the mail I get no
complain, but the column name (name of the respective vector of the
data.frame) is "Expr1014" instead of "NO". Additionally the original
column (type
"text") containes "0"s and missings, but the imported column contains
"0"s only (type "int"). If I change the column name in the Access data
base to "NOx", the import works fine with the right name and the same
data.
Previously I generated a tiny Access data base which reproduced the
problem. To be on the safe site I installed the latest version (2.5.1)
and now the example works fine, but within my production process the
error still remaines. An import into excel via ODBC works fine.
So there is no way to figure it out whether this is a bug or a
feature.-)
It's most likely an ODBC issue, but you have not provided a reproducible
example.
The second problem I have is that when I rerun "rm(list = ls(all =
T)); gc()" and the import several times I get the following error:
Error in odbcTables(channel) : Calloc could not allocate (263168 of 1)
memory In addition: Warning messages:
1: Reached total allocation of 447Mb: see help(memory.size) in:
odbcQuery(channel, query, rows_at_time)
2: Reached total allocation of 447Mb: see help(memory.size) in:
odbcQuery(channel, query, rows_at_time)
3: Reached total allocation of 447Mb: see help(memory.size) in:
odbcTables(channel)
4: Reached total allocation of 447Mb: see help(memory.size) in:
odbcTables(channel)
which is surprising to me, as the first two statements should delete
all
How do you _know _what they 'should' do? That only deletes all objects in the
workspace, not all objects in R, and not all memory blocks used by R.
Please do read ?"Memory-limits" for the possible reasons.
Where did '447Mb' come from? If this machine has less than 2Gb of RAM, buy
some more.
objects and recover the memory. Is this only a matter of memory? Is
there any logging that reduces the memory? Or is this issue connected to
the upper problem?
I added the code on the bottom - maybe there is some kind of misuse I
lost sight of. Any hints are appreciated.
Kind regards,
Maciej
version
_
platform i386-pc-mingw32
arch i386
os mingw32
system i386, mingw32
status
major 2
minor 5.1
year 2007
month 06
day 27
svn rev 42083
language R
version.string R version 2.5.1 (2007-06-27)
## code
get.table <- function(name, db, drop = NULL){
.con <- try(odbcConnectAccess(db), silent = T)
if(!inherits(.con, "RODBC")) return(.con)
## exclude memo columns
.t <- try(sqlColumns(.con, name))
if(inherits(.t, "try-error")){close(.con); return(.t)}
.t <- .t[.t$"COLUMN_SIZE" < 255, "COLUMN_NAME"]
.t <- paste(.t, collapse = ",")
## get table
.t <- paste("select", .t, "from", name)
.d <- try(sqlQuery(.con, .t), silent = T)
if(inherits(.d, "try-error")){close(.con); return(.d)}
.con <- try(close(.con), silent = T)
if(inherits(.con, "try-error")) return(.con)
.d <- .d[!names(.d) %in% drop]
return(.d)
}
get.alltables <- function(db){
.con <- try(odbcConnectAccess(db), silent = T)
if(!inherits(.con, "RODBC")) return(.con)
.tbls <- try(sqlTables(.con)[["TABLE_NAME"]])
if(inherits(.tbls, "try-error")){close(.con); return(.tbls)}
.con <- try(close(.con), silent = T)
if(inherits(.con, "try-error")) return(.con)
.tbls <- .tbls[-grep("^MSys", .tbls)]
.d <- lapply(seq(along = .tbls), function(.i){
.d <-
try(get.table(.tbls[.i], db = db))
return(invisible(.d))
})
names(.d) <- .tbls
.ok <- !sapply(.d, inherits, "try-error")
return(list(notdone = .d[!.ok], data = .d[.ok]))
}
library(RODBC)
alldata <- get.alltables(db = "./myaccessdb.MDB")
## code end
______________________________________________
R-help@stat.math.ethz.ch 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.
--
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
______________________________________________
R-help@stat.math.ethz.ch 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.
