I certainly encountered this problem recently. Here is my solution.
Basically you have to reorder columns based on the column information fetched from the database first. Columns could mix with positive and negative types. So sorting them with positive first, then negative will do the trick.
It works perfectly with my data when having "Invalid Descriptor Index" issue. Please let me know whether it works for you too.
sqlFetchData <- function(connection, database, schema, table, nobs = 'All') {
#'wrap function to fetch data from SQL Server
#
#@ connection: an established odbc connection
#@ database: database name
#@ schema: a schema under the main database
#@ table: the name of the data table to be fetched.
#@ nobs: number of observation to be fetched. Either 'All' or an integer number.
# The default value is 'All'. It also supports the input of 'all', 'ALL' and
# etc. .
if (is.character(nobs)) {
if (toupper(nobs) == 'ALL') {
obs_text <- 'select'
} else {
stop("nobs could either be 'ALL' or a scalar integer number")
}
} else {
if (is.integer(nobs) && length(nobs) == 1) {
obs_text <- paste('select top ', nobs, sep = '')
} else {
stop("nobs could either be 'ALL' or a scalar integer number")
}
}
initial_sql <- paste("select * from ", database, '.', schema, ".", table,
sep = '')
dbquery <- dbSendQuery(connection, initial_sql)
cols <- dbColumnInfo(dbquery)
dbClearResult(dbquery)
#' sort the rows by query type due to error message:
#' Invalid Descriptor Index
colInfo <- cols
colInfo$type <- as.integer(colInfo$type)
cols_neg <- colInfo[which(colInfo$type < 0), ]
cols_neg <- cols_neg[order(cols_neg[, 2]), ]
cols_pos <- colInfo[which(colInfo$type >= 0), ]
cols_pos <- cols_pos[order(cols_pos[, 2]), ]
cols <- rbind(cols_pos, cols_neg)
add_comma <- "c(cols$name[1], paste(',', cols$name[-1L], sep = ''))"
sql1 <- paste(c(obs_text, eval(parse(text = add_comma))),
collapse = ' ', sep = '')
data_sql <- paste(sql1, ' from ', database, '.', schema, '.', table,
sep = '')
dataFetch <- dbGetQuery(connection, data_sql)[, colInfo$name]
return(dataFetch)
}
dbFetch()
, does rs come through as a dataframe? – ParfaitdbGetQuery() ... calls 'dbSendQuery()', then 'dbFetch()', ensuring that the result is always free-d by 'dbClearResult()'
. – r2evans