On Nov 16, 2009, at 2:39 PM, helpme wrote:
I am receiving an error when trying to connect to the Oracle
Database using
RODBC on a 64-bit Windows Server OS. The version of R is 2.10.0-
win32.exe
Is this the wrong version. Does RODBC only work with 32-bit ODBC
drivers?
've read over all the posts and documentation manuals.
The system is Windows Server 2003 with R 2.81. and the latest
downloadable
RODBC package. The Oracle SID/DSN is mfopdw. I made sure to add it to
Control Panel->Administrative Priviledges->Microsoft ODBC system/
user DNS.
I've also tried the following in no particular order:
1.) Turn on all oracle services in control panel->administrative
priviledges.
2.) Checked tsnnames.ora for SID.
3.) Add microsoft ODBC service to Control Panel services for SID
4.) Use Sqldeveler to test connection another way besides R (It was
successful)
5.) channel<-odbcDriverConnect(
connection="Driver={Microsoft ODBC for Oracle};
DSN=abc,UID=abc;PWD=abc;"case="oracle")
received error drivers SQLAllocHandle on SQL_HANDLE_ENV failed one
time;
another time I got the error that Oracle client and networking
components
7.3 or greater is not found.
6.) tnsping mfopdw
lsnrctl start mfopdw
tried to add oracle/bin to path
Nothing is working.
Three quick comments:
1. A better place to post these types of queries would be on the R-SIG-
DB e-mail list, which is focused in this domain. More info here:
https://stat.ethz.ch/mailman/listinfo/r-sig-db
2. Prof. Ripley will be a more definitive resource, so I would wait
until he might respond.
3. If you have not yet, be sure to read the RODBC vignette, which is
available either via:
vignette("ROBDC")
or online at:
http://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
That all being said, since you have now posted what may be the root
cause of your problem, which is the 64/32 bit details, I will venture
a guess to say that this may be the problem. Since there is not a 64
bit version of R for Windows (save I believe the Revolution commercial
release), if you are using 64 bit Oracle client binaries and ODBC
drivers (if they exist), they will not be compatible with 32 bit R/
RODBC.
I know that on OSX, with 64 bit R/RODBC and 32 bit ODBC drivers for
Oracle, the connectivity would not work, so it seems reasonable that
the reverse configuration would not be compatible either.
So, first, I would be sure that you are using 32 bit ODBC drivers for
Oracle on Windows and not 64 bit. If you installed any other Oracle
client related software, that likely also needs to be 32 bit as well.
Then I would review the above vignette document and be sure that any
general installation references and those specifically pertaining to
Windows have been followed consistently, especially configuring $PATH
and other environmental configuration items required for Oracle
itself, which on some platforms usually include things like
$ORACLE_HOME, $TNS_ADMIN and so forth. You indicate above:
"tried to add oracle/bin to path"
which does not definitively indicate that you actually did so. Did
you? Also, check the capitalization, as the path is normally
something like c:\Oracle\bin.
If you can connect to the Oracle server using Oracle's own clients
such as the InstantClient, that typically means that most of the
system configuration issues are correctly set up. If that connection
is successful, then it may bring us back to the 32/64 bit conflict.
HTH,
Marc Schwartz
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.