[R-sig-DB] The "hack" in oraQuickSQL

[Don MacQueen]

Hi, David,

I have discovered what looks like an unexpected side-effect of a 
(recent?) change in oraQuickSQL().

The symptom I encountered was that within one R session, using 
ROracle and dbGetQuery(), one query succeeds, yet another does not. 
Both of them succeed if executed outside of R using Oracle's sqlplus. 
This is a new behavior; 2 or 3 months ago both succeeded. Since then, 
I have upgraded R from the 2.2.* series to the 2.3.* series, when I 
do that I also reinstall all of my favorite packages.

Here is an example:

>  slocs <- dbGetQuery(con,sel.locs)
>  dim(slocs)
[1] 22  8
>  dat <- dbGetQuery(con,dat.sql)
>  dat
<Expired OraResult:(11025,2,12)>
>
>  summary(con)
<OraConnection:(11025,2)>
   User: macq
   Dbname: taurusdb.llnl.gov


I traced the problem to this expression in oraQuickSQL

   hack <- grep("^[ \\t]*select ", tolower(dbGetInfo(rs)$statement))

I have (for quite some time now) made it a practice to start many but 
not all of my queries with
     "select\n"
Note the absence of a space character after "select", which the hack 
looks for. This then causes the expression block

     if (dbHasCompleted(rs) || length(hack) == 0) {
         dbClearResult(rs)
         return(invisible(rs))
     }

to be executed.

Here is the beginning of my query that failed. I put all the "\n" in 
there so that when I cat(dat.sql) it's formatted nicely.

>  dat.sql
[1] "select\n   l.locgrp,\n   l.lgrpseq,\n   l.locsubgrp,\n 
l.lsubgrpseq,\n   c.anlgrp,\n   c.agrpseq,\n   c.saerdesc,\n 
c.anlseq,\n   l.saerloc,\n   l.locseq,\n   to_


By way of testing, I first found that if I don't use dbGetQuery(), 
retrieval succeeds:
>  res <- dbSendQuery(con,dat.sql)
>  res
<OraResult:(11025,2,13)>
>  foo <- fetch(res,n=-1)
>  dim(foo)
[1] 94 50

I also added the space character, i.e., changed my query to start 
with "select \n", and then dbGetQuery() succeeds.

-Don

>  sessionInfo()
Version 2.3.1 (2006-06-01)
powerpc-apple-darwin8.6.0

attached base packages:
[1] "stats"   "utils"   "methods" "base"

other attached packages:
    gdata    rmacq  ROracle      DBI
  "2.1.2"    "1.0"  "0.5-7" "0.1-10"

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