Skip to content

The "hack" in oraQuickSQL

1 message · Don MacQueen

Original
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:
[1] 22  8
<Expired OraResult:(11025,2,12)>
<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.
[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:
<OraResult:(11025,2,13)>
[1] 94 50

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

-Don
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"