-----Original Message-----
From: Don MacQueen [mailto:macq at llnl.gov]
Sent: Monday, September 25, 2006 7:45 PM
To: Armstrong, Whit; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] possible bug in ROracle
I don't have a solution, but here's what I would try.
I have found it very useful, when debugging my queries, to
take them outside of R and run them in some other sort of query tool.
I have never found a case where a correct query outside of R
returned incorrect results using ROracle.
Is there some reason you're not using dbGetQuery()
(instead of dbSendQuery, fetch, and dbClearResult)?
Try dbGetQuery(), and see if it's better.
-Don
At 5:43 PM -0400 9/22/06, Armstrong, Whit wrote:
Content-class: urn:content-classes:message
Content-Type: text/plain;
charset="us-ascii"
Content-Disposition: inline
the code which no one else will be able to execute is at the
bottom of
the email The query I'm sending is this:
"select IDENT,FIELDDATE,to_number(FIELDVALUE) from
TS_DATALOOKUP_VIEW
where FIELDNAME='PRICE' and IDENT in
('USSWAP10','BPSW10','JYSW10','CDSW10','DMSW10','GJGB10','GCA
N10YR','GD
B
R10')"
which should just grap the IDENTS that I asked for.
However, when I inspect the result (stored in qry.data). I see that
the colnmes to not mach the IDENTS that I asked for. Some of these
IDENTS do not even exist in the database.
cnms <- unique(qry.data[,"IDENT"])
Browse[1]> cnms
[1] "GCAN10YR" "GDBR10YR" "GJGB10YR" "USSWAP10" "BPSW1010"
"JYSW1010"
"CDSW1010" "DMSW1010" "GJGB1010" "GDBR1010"
and the IDENTS I asked for:
Browse[1]> tickers
[1] "USSWAP10" "BPSW10" "JYSW10" "CDSW10" "DMSW10" "GJGB10"
"GCAN10YR" "GDBR10"
the culprits:
Browse[1]> cnms[!cnms%in%tickers]
[1] "GDBR10YR" "GJGB10YR" "BPSW1010" "JYSW1010" "CDSW1010" "DMSW1010"
"GJGB1010" "GDBR1010"
Only 2 of the resulting IDENTS match the ones passed into the query:
Browse[1]> cnms[cnms%in%tickers]
[1] "GCAN10YR" "USSWAP10"
We are using Oracle 9.2.0.
Is there something seriously wrong w/ my query or is there a bug
somewhere?
Thanks,
Whit
actual code:
grab.data <- function(tickers) {
drv <- Oracle()
con <- dbConnect(drv,user="fi",password="fi",dbname="FINP1")
tickers.string <-
paste(paste("\'",tickers,"\'",sep=""),collapse=",")
ticker.qry <- paste("select
IDENT,FIELDDATE,to_number(FIELDVALUE)
from TS_DATALOOKUP_VIEW where FIELDNAME='PRICE' and IDENT in
(",tickers.string,")",sep="")
res <- dbSendQuery(con,ticker.qry )
qry.data <- fetch(res,n=-1)
dbClearResult(res)
dbDisconnect(con)
cnms <- unique(qry.data[,"IDENT"])
if(length(cnms)!=length(tickers)) {
browser()
}
}
System details:
Package: ROracle
Version: 0.5-7
Date: 2006-02-13
Title: Oracle database interface for R
Author: David A. James <dj at bell-labs.com> Jake Luciani
<jakeluciani at yahoo.com>
Maintainer: David A. James <dj at bell-labs.com>
Description: Oracle database interface (DBI) driver for R.
This is a
DBI-compliant Oracle driver based on the ProC/C++ embedded SQL. It
implements the DBI version
0.1-8 plus one extension.
SaveImage: yes
Depends: R (>= 2.0.0), methods, DBI (>= 0.1-8)
License: LGPL version 2 or newer
URL: http://stat.bell-labs.com/RS-DBI
Packaged: Mon Feb 13 16:16:30 2006; dj
Built: R 2.3.1; i686-pc-linux-gnu; 2006-08-14 15:29:35; unix
Description:
Package: DBI
Version: 0.1-10
Date: 2006-01-28
Title: R Database Interface
Author: R Special Interest Group on Databases (R-SIG-DB)
Maintainer: David A. James <dj at bell-labs.com>
Depends: R (>= 1.8.0), methods
Description: A database interface (DBI) definition for
communication
between R and relational database management systems. All
classes in
this package are virtual and
need to be extended by the various R/DBMS
implementations.
License: GPL (version 2 or later)
URL: http://stat.bell-labs.com/RS-DBIhttp://developer.r-project.org/db
Packaged: Sat Jan 28 14:42:20 2006; dj
Built: R 2.3.1; ; 2006-08-14 15:29:07; unix
This e-mail message is intended only for the named
recipient(s) above.
It may contain confidential information. If you are not the intended
recipient you are hereby notified that any dissemination,
distribution
or copying of this e-mail and any attachment(s) is strictly
prohibited.
If you have received this e-mail in error, please immediately notify
the sender by replying to this e-mail and delete the message and any
attachment(s) from your system. Thank you.
--
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA
--------------------------------------
This e-mail message is intended only for the named recipient(s) above. It may contain confidential information. If you are not the intended recipient you are hereby notified that any dissemination, distribution or copying of this e-mail and any attachment(s) is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by replying to this e-mail and delete the message and any attachment(s) from your system. Thank you.