[R-sig-DB] problem with ROracle calls from a package

[Don MacQueen]

When I did this I defined the connection inside the function.

That is,

myfun <- function( dbc, ...) {
   ## dbc is a list with username, password, etc to the database

   dbm <- Oracle()
   con <- dbConnect(dbm, user = dbc$user, dbname = dbc$db, password = dbc$pass)

   ## build the query, then
   res <- dbGetQuery(con, sql)
  ....
}

And it works.

I also found it helpful to have something like this after defining 
dbm and before defining con

     tmp <- dbListConnections(dbm)
     if (length(tmp) > 5)
         for (ic in tmp) dbDisconnect(ic)


I also put this in there somewhere:

     on.exit({
         dbDisconnect(con)
         dbUnloadDriver(dbm)
     })

-Don



At 9:59 PM -0400 8/3/09, Rajarshi Guha wrote:
>Hi, I have some R code that uses a OracConnection object to query a
>database. The function is something like
>
>myfunction <- function(con, .....) {
>   sql <- paste("SELECT pd.plate_info_id, pd.col_index, ",data.cols,
>                " FROM plate_data pd ",
>                " WHERE pd.plate_info_id IN ",
>                " (SELECT pi.plate_info_id ",
>                " FROM plate_info pi ",
>                " WHERE pi.protocol_name = '",protocol.name,"')",
>                " AND pd.type_index = ", type.index," and pd.layer_index =
>",layer.index,
>                " order by pd.plate_info_id ,pd.col_index", sep='',
>collapse='')
>   res <- dbSendQuery(con, statement=sql)
>....
>....
>}
>
>When I source the file containing this function and pass it a connection
>object it works fine.
>
>However, when I bundle this function into a package and install the package
>and then call the function, I get
>
>Error in oraExecStatement(ps, ora.buf.size = as(ora.buf.size, "integer")) :
>   expired or invalid prepared statement
>Error in oraExecDirect(conn, statement, ...) :
>   could not exec direct statement SELECT pd.plate_info_id, pd.col_index,
>pd.value0, pd.value1, pd.value2, pd.value3, pd.value4, pd.value5, pd.value6,
>pd.value7, pd.value8, pd.value9, pd.value10, pd.value11, pd.value12,
>pd.value13, pd.value14, pd.value15 FROM plate_data pd  WHERE
>pd.plate_info_id IN  (SELECT pi.plate_info_id  FROM plate_info pi  WHERE
>pi.protocol_name = 'cptk-3-vo') AND pd.type_index = 22 and pd.layer_index =
>0 order by pd.plate_info_id ,pd.col_index
>
>Has anybody seen this behavior before?
>--
>Rajarshi Guha
>
>	[[alternative HTML version deleted]]
>
>_______________________________________________
>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
925-423-1062