Revolution R Oracle DB Query Using RODBC

Question

RODBC error in Revolution R 64bit on winxp64 bit connected to Oracle using 64-bit ODBC driver via 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 can connect, but get an error when I request material, also 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 don't know the lines (max = 1) before hand

Thanks Arun

Answer 1

believeNRows = FALSE seems to be the key. It is best to use it when opening a connection:

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

When testing with unixODBC isql, it reports SQLRowCount as 4294967295 (even if there is only one row) on 64-bit Linux, while it reports -1 on 32-bit Linux. This is probably an optimization because it allows you to respond faster. This relieves the database of the burden of receiving a complete set of response data immediately. For instance. there can be many entries, while only the first few hits will ever be retrieved.

4294967295 is (2 ^ 32) -1, which is the maximum value for an unsigned int, but will be declared as -1 with an int signed. So R complains about a negative length vector. So I assume this is a problem with signed vs. unsigned integer (or sizeof (long) between 32 and 64 bits).

Setting trustNRows = FALSE solved the problem for me, so I can use the same R code on both systems.

BTW: I am 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 configuring for unixODBC, because the Oracle ODBC driver expects REAL_64_BIT_MODE, not LEGACY_64_BIT_MODE.

And remember the internationalization issues: R uses $ LANG, and Oracle uses $ NLS_LANG.

I'm having problems with UTF8, so I use for example

LANG = en_US; NLS_LANG = American_America

Answer 2

Error

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

very similar to the problem with 32-bit / 64-bit port, so I kindly suggest that you contact the two commercial vendors that have this fix. I prefer the direct database driver available through ODBC, but there is no reason why it should not work, since 64-bit Linux is fun to play.

Answer 3

Dirk is right - RODBC does not support 64-bit drivers for Oracle, at least not a few months ago. Perhaps you are out of luck. We had a similar problem trying to get R to access the Oracle database from a 64-bit Linux package using the following tools: 64-bit R, RODBC, unixODBC, Oracle Instant Client. I asked the list of R-sig-db, including the author of the package (Prof. Ripley) about this, and there was no final answer. Then I asked the Revolution if they want to solve this problem if we buy licenses from them (5 digits / year!), And they said no.

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

The main problem is that very few large R users also use Oracle. R is mainly used by scientists (Excel, MySQL), types of finance (Postgres) and more advanced analytic teams. Oracle is used by older enterprises that value reliability over innovation, exactly the opposite of what most R seeks. Therefore, this explains why Oracle support has fallen, in my opinion.

Answer 4

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