I haven't been able to find any help on this and am really struggling.
I've been using RODBC 1.1-3 and R(D)COM v1.35 for a little over a year
successfully with my Access Database, and now im switching to Oracle 9i.
I use R 2.0.1 on a Windows XP platform, 1GB ram, 2GHz Intel Processor)
When I connect through the Rgui R console, the connection is successful
(but through VB the attempt fails):>library(RODBC)
>channel <- odbcConnect(dsn="MY_DB", uid="MYUID",
pwd="MYPWD", case "oracle")
>channel
RODB Connection 4
Details:
case=toupper
DSN=MY_DB
UID=MYUID
PWD=MYPWD
DBQ=LT10G
DBA=W
APA=T
EXC=F
FEN=T
QTO=T
FRC=10
FDL=10
LOB=T
RST=T
GDE=F
FRL=F
BAM=IfAllSuccessful
NUM=NLS
DPM=F
MTS=T
MDI=F
CSR=F
FWC=F
FBS=64000
TLO=0
When I run the same code through VBA (and VB .NET). I would have:
Rinterface.RRun("channel<- odbcConnect(dsn='MY_DB',
uid='MYUID',
pwd='MYPWD', case = 'oracle')")
And then to check that there is a connection (since the rest of the code
fails I debug by putting the channel variable to a .Rdata file and then
inspect the value from the RGUI console:
Rinterface.Rrun("dput(channel, 'c:/channel.RData')")
In the Rconsole I can see that the value is -1, which occurs when a
connection fails:>channel<-dget("c:/channel.Rdata")
>channel
[1] -1
I have identified the problem to the a piece of code trying to establish
the connection (residing in the odbcConnect function):
odbcDriverConnect(st, case = case, believeNRows = believeNRows)
Where st has the value "DSN=MY_DB;UID=MYUID;PWD=MYPWD", and this I
believe is the case in both instances since I tried to replicate the
odbcConnect code manually in VB, and the two connection strings checked
out.
It seems to boil down to the following code in odbcDriverConnect, which
seems to either succeed or fail depending on whether I am attempting it
from VB or from the Rconsole:
stat <- .Call("RODBCDriverConnect", as.character(connection),
id, as.integer(believeNRows), PACKAGE = "RODBC")
I am wondering whether a check is performed on the environment calling
it, such as is the code being run from the Rconsole or not? This is the
only thing I can think of which might affect its behaviour. I tried
inspecting the C code for the .DLL but am in over my head.
I am using the Microsoft ODBC for Oracle provider and connections and
queries succeed when ADO connects, and when R connects through the
console.
Any ideas for the disparities would be greatly appreciated. Anyone
experienced anything similar?
Kind Regards,
Tariq Khan
Quantiative Research Analyst
Fauchier Partners, LLC
London
[[alternative HTML version deleted]]
