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

The error

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

very much looks like a 32-bit / 64-bit porting issue so I kindly suggest you get in touch with the two commercial vendors involved to have that fixed. I prefer direct database driver where available over ODBC but there is no reason why it shouldn't work as 64-bit Linux merrily plays along.

Answer 2

Dirk is right -- RODBC doesn't support 64-bit drivers for Oracle, at least not as of a few months ago. You may be out of luck. We had a similar issue trying to get R to access an Oracle database from a 64-bit Linux box using the following tools: 64-bit R, RODBC, unixODBC, Oracle Instant Client. I asked the R-sig-db list, including the package author (Prof. Ripley) about this, and there was no conclusive answer. I then asked Revolution if they would be willing to solve the problem, if we were to purchase licenses from them (at 5-figures/year!), and they said no.

My company is now trying to minimize use of R to areas where it is best suited. We will be using other tools (web services, JVM-based systems) to access the database, and sharing data with R only when necessary.

The underlying problem is that very few major users of R also use Oracle. R is primarily used by academics (Excel, MySQL), finance types (Postgres), and more cutting-edge analytics teams. Oracle is used by old businesses that value reliability over innovation, the exact opposite of what most R uses are looking for. So this explains why support for Oracle has fallen away, in my view.

Answer 3

Try max=0 and believeNRows=FALSE - that worked for me.

Answer 4

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