This discussion is archived
4 Replies Latest reply: Oct 1, 2012 4:03 AM by 964013 RSS

ROracle dbPrepareStatement silently dropped?

964013 Newbie
Currently Being Moderated
Hi,

currently I am trying to upgrade my database package which used to work with the ROracle versions prior to 1.x series. Now, it seems that some functions are missing (perhaps on purpose ?). So my code uses e.g. dbPrepareStatement Does someone know what has happened to these calls and how to emulate dbPrepareStatement in the latest ROracle?

Big thanks!

Oliver
  • 1. Re: ROracle dbPrepareStatement silently dropped?
    Denis Mukhin Journeyer
    Currently Being Moderated
    Oliver,
    Could you, please, paste a small chunk of code that you are trying to convert. I'll show you how this can be done with the new version of ROracle.

    Denis
  • 2. Re: ROracle dbPrepareStatement silently dropped?
    964013 Newbie
    Currently Being Moderated
    Hi Denis,

    this sounds great. Thanks! Below you will find some code. I hope it is somewhat self-explanatory enough.
    Just to give you some more background to understand the data-structure that's been used in the example

    dfExp <- exprs( eset ) denotes access to a data-structure called ExpressionSet which is part of Bioconductor's Biobase package ( [see Biobase|http://www.bioconductor.org/packages/release/bioc/html/Biobase.html] )

    `marsAddData` <-
    function( oraConnect, studyId, eset ) {

    dfExp <- exprs( eset )
    if( (nrow(dfExp) <= 0) || (ncol(dfExp) <= 0) )
    return( FALSE )

    ##
    ## Find all the parameters defined for the given
    ## studyId
    ##

    pDefSt <- paste( "select * from t_mars_parameter where studyid=", studyId, sep="" )
    res <- dbSendQuery( oraConnect, statement=pDefSt )
    dfParams <- fetch( res, n=-1 )
    dbClearResult(res)

    if( nrow( dfParams ) <= 0 )
    stop("Cannot find parameter definitions for study id: ", studyId, collapse="\n")

    ## print( dfParams )

    ##
    ## Create a data frame for each column and the values respectively
    ## Add it to the database column by column
    ##

    cols <- colnames(dfExp)

    for( j in 1:ncol(dfExp) ) {
    paramId <- dfParams$PARAMID[which(dfParams$COLNAME==cols[j])]
    dfVal <- data.frame( ROWNO=as.integer(c(1:nrow(dfExp))),
    NUMVAL=as.character(dfExp[,j]),
    stringsAsFactors=FALSE )

    insertSt <- paste( "insert into t_mars_param_value values( ",paramId,", :1, TO_NUMBER( :2 ) )", sep="" )
    ps <- dbPrepareStatement( oraConnect, statement=insertSt, bind=c("integer","character") )
    dbExecStatement( ps, data=dfVal )
    ## summary( ps )
    dbCommit( oraConnect )
    dbClearResult( ps )
    }

    return( TRUE )
    }

    Thanks very much in advance!
    Oliver

    Edited by: user1867199 on Sep 25, 2012 1:05 AM
  • 3. Re: ROracle dbPrepareStatement silently dropped?
    Denis Mukhin Journeyer
    Currently Being Moderated
    Oliver,

    Here is my recommendation for using ROracle in your function. I changed both SELECT and INSERT queries to use bind data. I also changed the INSERT query to add column names (please, change the names if I guessed them wrong). Having column names in the INSERT statement makes it more robust since it does not depend on the order of columns in the DB. Additionally it is easier to understand which columns are updated without looking at the table definition. Also if new columns are added to the table the INSERT statement will still work inserting default values (NULL by default). This won't work if new columns are added with NOT NULL constraint. Similar example can be found in the ROracle man pages (?Oracle).
    `marsAddData` <-
    function( oraConnect, studyId, eset ) {
    
      dfExp <- exprs( eset )
      if( (nrow(dfExp) <= 0) || (ncol(dfExp) <= 0) )
        return( FALSE )
    
    ##
    ## Find all the parameters defined for the given
    ## studyId
    ##
    
      dfParams <- dbGetQuery(oraConnect, "select * from t_mars_parameter where studyid = :1",
                             data = data.frame(studyid = studyId)
    
      if( nrow( dfParams ) <= 0 )
        stop("Cannot find parameter definitions for study id: ", studyId, collapse="\n")
    
    ##  print( dfParams )
    
    ##
    ## Create a data frame for each column and the values respectively
    ## Add it to the database column by column
    ##
    
      cols <- colnames(dfExp)
    
      insertSt <- "insert into t_mars_param_value (paramid, rowno, numval) values (:1, :2, :3)"
      for( j in 1:ncol(dfExp) ) {
        paramId <- dfParams$PARAMID[which(dfParams$COLNAME==cols[j])]
        dfVal <- data.frame( PARAMID=rep(paramId,nrow(dfExp)),
                             ROWNO=as.integer(c(1:nrow(dfExp))), 
                             NUMVAL=as.character(dfExp[,j]),
                             stringsAsFactors=FALSE )
    
        if( j == 1L )
          rs <- dbSendQuery(oraConnect, insertSt, data = dfVal)
        else
          execute(rs, data = dfVal)
        dbCommit( oraConnect )
      }
      if( ncol(dfExp) > 0L)
        dbClearResult( rs )
    
      return( TRUE )
    }
    Denis
  • 4. Re: ROracle dbPrepareStatement silently dropped?
    964013 Newbie
    Currently Being Moderated
    Dear Denis,

    thanks a lot! Indeed, it works perfectly as you have outlined. In fact, I had some trouble with the data binding in former versions of ROracle. So I had to run through a lot of trial-and-error to figure out what actually worked and what didn't. Now, with the new versions it seems that the situation has improved significantly....which is really great!

    Thanks again.
    Oliver

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points