[R-sig-DB] problem with ROracle calls from a package
Don MacQueen
m@cq @end|ng |rom ||n|@gov
Thu Aug 6 16:35:12 CEST 2009
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
More information about the R-sig-DB
mailing list