Querying Oracle DB from Revolution R using RODBC

Question

RODBC error in Revolution R 64bit on winxp64 bit connected to Oracle using a 64bit ODBC driver thru a DSN

library(RODBC)
db <- odbcConnect("oraclemiso",uid="epicedf",pwd="…")
rslts = sqlQuery(db, "select count(*) from FTRAuction")

Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize,  : 
  negative length vectors are not allowed

I am able to connect but get an error when I query for stuff, also the below works

 library(RODBC)
 channel <- odbcConnect("OraLSH", <user>, <password>))
 odbcQuery (channel, "select sysdate from dual")
 sqlGetResults(channel, as.is=FALSE, errors=FALSE, max=1, buffsize=1,
nullstring=NA, na.strings="NA", believeNRows=TRUE, dec=getOption("dec"))
              SYSDATE
1 2010-01-24 15:10:02

but what if I dont know the rowsize(max=1) before hand

Thanks, Arun

Answer 1

believeNRows=FALSE seems to be the key. Best to use it when opening the connection:

db <- odbcConnect(dsn="testdsn", uid="testuser", pwd="testpasswd", believeNRows=FALSE )

When testing with unixODBC's isql, it reports SQLRowCount to be 4294967295 (even if there's just one row) on 64bit Linux while it reports -1 on 32 bit Linux. This is probably an optimization as it enables quicker answers. It saves the database the burden of retrieving the complete response data set immediately. E.g. there might be lots of records while only the first few hits will ever be fetched.

4294967295 is (2^32)-1 which is the maximum value for an unsigned int, but will be tretated as -1 with a signed int. Thus R complains on a vector with negative length. So I assume it's an issue about signed vs. unsigned integer (or sizeof(long) between 32 and 64 bit).

Setting believeNRows=FALSE solved the issue for me so I can use the same R code on both systems.

BTW: I'm using R 2.10.1, RODBC 1.3.2, unixODBC 2.3.0 with Oracle 10.2.0.4 on Linux 64 bit. Be sure to use

export CFLAGS="-DBUILD_REAL_64_BIT_MODE -DSIZEOF_LONG=8 -fshort-wchar"

when doing configure for unixODBC as the Oracle ODBC driver expects REAL_64_BIT_MODE, not LEGACY_64_BIT_MODE.

And be aware of internationalization issues: R uses $LANG while Oracle uses $NLS_LANG.

I experienced problems with UTF8 so I use e.g.

LANG=en_US; NLS_LANG=American_America