[R-sig-DB] possible bug in ROracle

[Don MacQueen]

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','GCAN10YR','GDB
>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 using bell-labs.com> Jake Luciani
><jakeluciani using yahoo.com>
>Maintainer:    David A. James <dj using 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 http://www.omegahat.org
>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 using 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-DBI
>http://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
>
>
>>  R.Version()
>$platform
>[1] "i686-pc-linux-gnu"
>
>$arch
>[1] "i686"
>
>$os
>[1] "linux-gnu"
>
>$system
>[1] "i686, linux-gnu"
>
>$status
>[1] ""
>
>$major
>[1] "2"
>
>$minor
>[1] "3.1"
>
>$year
>[1] "2006"
>
>$month
>[1] "06"
>
>$day
>[1] "01"
>
>$`svn rev`
>[1] "38247"
>
>$language
>[1] "R"
>
>$version.string
>[1] "Version 2.3.1 (2006-06-01)"
>
>>
>
>
>
>
>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.
>
>
>_______________________________________________
>R-sig-DB mailing list -- R Special Interest Group
>R-sig-DB using stat.math.ethz.ch
>https://stat.ethz.ch/mailman/listinfo/r-sig-db


-- 
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA