Dear Christian,
At 08:41 AM 2/11/2003 +0100, Christian Schulz wrote:
>i found tis message in the archive and have
>got the same problems ?
>John, perhaps you have found now a way
>install RPgSQL for windows2000, or anybody other ?
>
>many thanks for advance
>christian
>
>
>[SNIP]
>I wonder whether anyone has succeeded in building either the RPgSQL or the
>Rdbi.PGSQL and Rdbi packages for Windows. If so, would you be willing to
>share either the binary package(s) or instructions about how to set up the
>configure scripts? My preliminary efforts to get things going under Windows
>2000 haven't met with much success.
I haven't been able to install RPgSQL for Windows, but I found (following
Brian Ripley's suggestion) that RODBC works well, both with Postgresql and
MySQL.
I ended up implementing something similar to the proxy data frames in
RPgSQL, which I've attached to this message. This is another example of
some code that seems too small to bundle in a package, so it's not
documented, but the general idea is to create an odbcDataset object via the
function of that name, which can then be indexed, summarized, etc. Row
indices can be SQL queries. For example, assuming that the table usarrests
is in the MySQL database test:
channel <- odbcConnect("test", case="mysql")
USarrests <- odbcDataset(channel, "usarrests")
USarrests["rape > 20 AND assault < 200 ORDER BY rape",
c("rape",
"assault")]
lm(murder ~ ., data=USarrests[])
I hope that this is of some use to you.
John
----------------------- snip -------------------------
.DBMS <- matrix(c("MySQL", "Column_name",
"Type_name",
"PostgreSQL", "COLUMN_NAME",
"TYPE_NAME"),
byrow=TRUE, ncol=3)
colnames(.DBMS) <- c("dbname", "column.selector",
"type.selector")
odbcDataset <- function(channel, table, rownames){
if (length(channel) != 1 || !is.integer(channel) || channel < 0)
stop("channel must be a non-negative integer")
if (length(table) != 1 || !is.character(table)) stop("table must be a
data-base table name")
odbcTableExists(channel, table)
info <- odbcGetInfo(channel)
db <- sapply(.DBMS[,"dbname"], function(x) 1 == length(grep(x,
info)))
if (!any(db)) stop(paste("database not located in the .DBMS
table\ndatabase info:", info))
if (missing(rownames)){
col.info <- sqlColumns(channel, table)
rownames <- 1 == length(grep("char", col.info[1, .DBMS[db,
"type.selector"]]))
}
result <- list(channel=channel, table=table, column.selector=.DBMS[db,
"column.selector"], rownames=rownames)
class(result) <- c("odbcDataset", "data.frame")
result
}
print.odbcDataset <- function(x, ..., verbose=FALSE){
if (verbose) print(x[,])
else {
x <- unclass(x) # necessary because of $.odbcDataset
cat( "channel: ", x$channel)
cat("\ntable: ", x$table)
cat("\nrow names: ", x$rownames)
class(x) <- c("odbcDataset", "data.frame")
cat("\nnumber of rows: ", nrow(x))
cat("\nnumber of columns: ", ncol(x), "\n")
invisible(x)
}
}
summary.odbcDataset <- function(object, ...) summary(object[,])
"[.odbcDataset" <- function(x, i, j, ...){
same.sign <- function(x) {
any(x > 0) == all(x >= 0)
}
x <- unclass(x)
names <- sqlColumns(x$channel, x$table)[[x$column.selector]]
selection <- if (missing(j)) "*"
else if (is.numeric(j)) {
j <- j[j != 0]
if (length(j) == 0) return(NULL)
if (!same.sign(j)) stop("cannot mix positive and
negative subscripts")
if (j[1] > 0) names[j + x$rownames]
else names[-1 * c(rep(1, x$rownames), (abs(j)
+ x$rownames))]
}
else j
selection <- if (selection == "*" || !x$rownames) selection
else c(names[1], selection)
if (selection != "*" && (any(is.na(selection)) ||
any(!is.element(selection, names)))) stop("bad column index")
result <- if (missing(i)) sqlQuery(x$channel, paste("select",
paste(selection, collapse=","), "from", x$table))
else {
if (!is.character(i) && length(i) != 1)
stop("row
'subscript' must be an SQL row selector")
sqlQuery(x$channel, paste("select",
paste(selection,
collapse=","), "from", x$table, "where", i))
}
if (x$rownames) {
rownames(result) <- as.character(result[,1])
result <- result[, -1]
}
if (length(dim(result)) == 2 && dim(result)[2] == 1)
result[,,drop=TRUE] else result
}
"$.odbcDataset" <- function(x, i){
x <- unclass(x)
sqlQuery(x$channel, paste("select", i, "from",
x$table))[[1]]
}
"[[.odbcDataset" <- function(x, i){
x <- unclass(x)
if (is.numeric(i)) i <- sqlColumns(x$channel,
x$table)[[x$column.selector]][i + x$rownames]
sqlQuery(x$channel, paste("select", i, "from",
x$table))[[1]]
}
as.data.frame.odbcDataset <- function(x, rownames, optional) x[,]
as.matrix.odbcDataset <- function(x) as.matrix(x[,])
as.list.odbcDataset <- function(x, ...) as.list(x[,])
row.names.odbcDataset <- function(x) {
x <- unclass(x)
if (x$rownames) {
name <- sqlColumns(x$channel, x$table)[[x$column.selector]][1]
as.character(sqlQuery(x$channel, paste("select", name,
"from",
x$table))[[1]])
}
else NULL
}
names.odbcDataset <- function(x){
x <- unclass(x)
names <- sqlColumns(x$channel, x$table)[[x$column.selector]]
if (x$rownames) names[-1] else names
}
dimnames.odbcDataset <- function(x) list(row.names(x), names(x))
dim.odbcDataset <- function (x) c(length(x[[1]]), length(names(x)))