Dear R-users,
I was able to make ROracle package to connect to the DB (Oracle91,
64bit, on Solaris). But after executing siple SQL query, "fetch"
commaned gives me an empty dataframe.
### RORACLE INSTALATION PROCEDURE ###
R CMD INSTALL --configure-args='--enable-extralibs' --enable-oracle32=no
~/tmp/ROracle_0.5-5.tar.gz #since we have 64bit Oracle9i instalation
PROBLEM: after executing simple statement, from within R, "fetch"
function gives me the empty dataframe:
### CONNECTION, and SQL QUERY EXECUTION
> ora <- dbDriver("Oracle")
> con <- dbConnect(ora, "rado/only2admin"
> dbListTables(con)
character(0)
> rs <- dbSendQuery(con, "desc * from si_r where id=498")
> d <- fetch(rs, n= -1)
> dim(d)
[1] 0 3
str(d)
data.frame': 0 obs. of 3 variables:
$ ID : int
$ DAY: chr
$ R : num
#### seems like connections has been established
> dbGetInfo(ora)
$drvName
[1] "Oracle (ProC/C++)"
$connectionIds
$connectionIds[[1]]
<OraConnection:(14939,0)>
$fetch_default_rec
[1] 500
$managerId
<OraDriver:(14939)>
$length
[1] 10
$num_con
[1] 1
$counter
[1] 1
$clientVersion
[1] "0.5-4"
Maybe it is something stupid (and I need just a hint), or may be it is
something major.
Thanks in advance,
Rado Bonk
--
Dr. Radoslav Bonk
European Commission - DG Joint Research Centre (JRC)
Institute for Environment and Sustainability (IES)
LM Unit - Natural Hazards
Weather Driven Natural Hazards Action
Via E. Fermi, TP 261, 21020 Ispra (Va), Italy
Tel.: 0039-0332-786013
Fax: 0039-0332-786653
Webpage: http://natural-hazards.jrc.it/floods/
Dear Rado, I think you didn't get a proper db connection at all. You forgot to specify the database to connect to. On Wed, 14 Jul 2004, Rado Bonk wrote:> ### CONNECTION, and SQL QUERY EXECUTION > > ora <- dbDriver("Oracle") > > con <- dbConnect(ora, "rado/only2admin"Which database???> > dbListTables(con) > character(0)dbListTables would display something, if you had connected. You should get this working before you proceed. Regards, Michael
This is what I would try next if I were in your situation; I don't
know it will help.
Try
con <- dbConnect(ora, "rado/only2admin at dbname")
instead of
con <- dbConnect(ora, "rado/only2admin"
The default dbname is Sys.getenv("ORACLE_SID"), have you checked that?
For myself, when I make connections to Oracle 9i using ROracle, I use the form
dbConnect,ora,
user='oracleuid',dbname='adbname',password='whatever')
-Don
At 10:38 AM +0200 7/14/04, Rado Bonk wrote:>Dear R-users,
>
>I was able to make ROracle package to connect to the DB (Oracle91,
>64bit, on Solaris). But after executing siple SQL query, "fetch"
>commaned gives me an empty dataframe.
>
>### RORACLE INSTALATION PROCEDURE ###
>R CMD INSTALL --configure-args='--enable-extralibs'
>--enable-oracle32=no ~/tmp/ROracle_0.5-5.tar.gz #since we have
>64bit Oracle9i instalation
>
>PROBLEM: after executing simple statement, from within R, "fetch"
>function gives me the empty dataframe:
>
>### CONNECTION, and SQL QUERY EXECUTION
> > ora <- dbDriver("Oracle")
> > con <- dbConnect(ora, "rado/only2admin"
> > dbListTables(con)
>character(0)
>> rs <- dbSendQuery(con, "desc * from si_r where id=498")
>> d <- fetch(rs, n= -1)
>> dim(d)
>[1] 0 3
>str(d)
>data.frame': 0 obs. of 3 variables:
>$ ID : int
>$ DAY: chr
>$ R : num
>
>#### seems like connections has been established
>> dbGetInfo(ora)
>$drvName
>[1] "Oracle (ProC/C++)"
>
>$connectionIds
>$connectionIds[[1]]
><OraConnection:(14939,0)>
>
>
>$fetch_default_rec
>[1] 500
>
>$managerId
><OraDriver:(14939)>
>
>$length
>[1] 10
>
>$num_con
>[1] 1
>
>$counter
>[1] 1
>
>$clientVersion
>[1] "0.5-4"
>
>Maybe it is something stupid (and I need just a hint), or may be it
>is something major.
>
>Thanks in advance,
>
>Rado Bonk
>
>
>--
>Dr. Radoslav Bonk
>European Commission - DG Joint Research Centre (JRC)
>Institute for Environment and Sustainability (IES)
>LM Unit - Natural Hazards
>Weather Driven Natural Hazards Action
>Via E. Fermi, TP 261, 21020 Ispra (Va), Italy
>Tel.: 0039-0332-786013
>Fax: 0039-0332-786653
>Webpage: http://natural-hazards.jrc.it/floods/
>
>______________________________________________
>R-help at stat.math.ethz.ch mailing list
>https://www.stat.math.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide!
http://www.R-project.org/posting-guide.html
--
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA
Michael,
Yes you are right, I forgot to specify the dbname when posting in R-help
list. But not in my R-code, since it is specified in ORACLE_SID variable
on Linux. Using the proper syntax I still get an empty data frame:
> library(ROracle)
> drv <- dbDriver("Oracle")
> con <- dbConnect(drv, "rado/mypassword at dea")
> dbListTables(con)
character(0)
> q.sql <- c("select * from si_r where id=498")
> rs <- dbSendQuery(con, q.sql)
> data <- fetch(rs, n= -1)
> dim(data)
[1] 0 3
> dbGetStatement(rs)
[1] "select * from si_r where id=498"
> dbGetInfo(con)
$dbname
[1] "dea"
$user
[1] "rado"
$passwd
[1] "mypassword"
$conType
[1] NA
$serverVersion
[1] NA
$protocolVersion
[1] NA
$threadId
[1] -1
$resultSetIds
$resultSetIds[[1]]
<OraResult:(15551,0,2)>
> data
[1] ID DAY R
<0 rows> (or 0-length row.names)
>
>>### CONNECTION, and SQL QUERY EXECUTION
>> > ora <- dbDriver("Oracle")
>> > con <- dbConnect(ora, "rado/only2admin"
>
>
> Which database???
--
Radoslav Bonk
European Commission - DG Joint Research Centre (JRC)
Institute for Environment and Sustainability (IES)
LM Unit - Natural Hazards
Weather Driven Natural Hazards Action
Via E. Fermi, TP 261, 21020 Ispra (Va), Italy
Tel.: 0039-0332-786013
Fax: 0039-0332-786653
Webpage: http://natural-hazards.jrc.it/floods/