[R] RODBC Oracle and VB automation with R(D)COM

From: Tariq Khan <tk_at_tariqkhan.org>
Date: Thu 12 May 2005 - 09:14:16 EST


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]]



R-help@stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html Received on Thu May 12 16:33:44 2005

This archive was generated by hypermail 2.1.8 : Fri 03 Mar 2006 - 03:31:42 EST