[R-sig-DB] RODBC/ROracle Oracle and VB automation with R(D)COM

[¨Tariq Khan]

Dear All,
 Months ago, I posted the question below and wanted to keep the question and
the thread alive since Im sure many would agree that it is an important one,
especially since many like myself find it very easy to make VB the interface
with R the statistical back-end engine. Maybe in today's world there is a
solution?
 As i am revisiting the problem, I just downloaded and compiled ROracle on
Windows XP along with the DBI package. I can connect to my database fine
from the RConsole using either ROracle or RODBC.
 So fine, these packages work, and so does my database. My computer can
resolve my DSN, tnsping works, and my system is accessing the right
tnsnames.ora file.
 The problem arises when I use R(D)COM to request that R connect to the
database. After tracing and manipulating the RODBC.c source code I finally
got the following error message (from sqlDriverConnect()):
state was 08004, code 12154, message [Oracle][ODBC][Ora]ORA-12154: TNS:could
not resolve service name
ROracle confirms this error code and message.
 So why not post this on the R Com help list? because all the variables are
the same whether I am running it directly from the Rconsole or not (except
that Rconsole is only defined in one case but this is not the problem).
Something is different in the context of the connection request, but ive
confirmed that the variables can still be read. Furthermore, database
requests with (D)COM work with microsoft products (Access and ive heard it
works with SQL server). It just does not work with Oracle.
 I found out that the flow is something like this:
App->ODBC32.dll->sqora32.dll->SQL net->SQL-Net Listener->database
 I used the microsoft driver for oracle as well, without avail.. i then get
the following generic looking error:
state was NA000, code 6413, message [Microsoft][ODBC driver for
Oracle][Oracle]ORA-06413: Connection not open.
 I opened up security on ORACLE_HOME and subdirectories ensuring 'Everyone'
as access and did the same to Oracle Registry directories.
 Why not contact Oracle? Indeed, and I believe we will; but either way maybe
someone knows the answer because this problem with Oracle ony occurs when
trying to use RODBC/ROracle together with VB or some other C language, so
maybe by now someone knows the answer? Please let me know if you have seen
this before or you know the answer.
 My sincere thanks to all of you,
 Tariq

---------- Forwarded message ----------
From: �Tariq Khan <tariq.khan using gmail.com>
Date: May 12, 2005 7:33 AM
Subject: RODBC Oracle and VB automation with R(D)COM
To: rcom-l using mailman.csd.univie.ac.at

I haven't been able to find any help on this and am really struggling.
Prof. Ripley thought this might be an appropriate forum for the
question.
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, using R(D)COM, 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

	[[alternative HTML version deleted]]