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

[Rajarshi Guha]

Thanks for the pointer. However, I have a number of functions that require a
connection, and I wanted to avoid keeping on opening and closing
connections.

In the end it turned out that my package was loading a Bioconductor package
after loding the ROracle package. The Bioconductor package then went and
loaded RSQLite, whose 'isValidConnection' overwrote that from ROracle and
hence the error!

On Thu, Aug 6, 2009 at 10:35 AM, Don MacQueen <macq using llnl.gov> wrote:

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



-- 
Rajarshi Guha

	[[alternative HTML version deleted]]