Copy Occi::ResultSet before closing Occi::Connection

Question

I am using OCCI with C++ to get data from Oracle. The code works well, however I noticed some performance decrease. This happens because in the rset->next() iteration some computation takes time. The impact of this delay is that the oracle connection pool has one connection busy. If concurrently requests demand the same computation, the maybe all connection in the pool will be BUSY.

     Statement *stmt = conn->createStatement (sqlQuery);

      ResultSet *rset = stmt->executeQuery ();
      while (rset->next ())
      {
        //Slow computation takes time
        compute()
      }

      stmt->closeResultSet (rset);

      conn->terminateStatement (stmt);
      env->terminateConnection (conn);

So my question is: Can I copy the Occi::ResultSet (using a shared pointer?) in order to close the connection AFTER the copy and do the computation after releasing the connection?

go_to_oracle( ResultSet &result) {
 Statement *stmt = conn->createStatement (sqlQuery);

  ResultSet *rset = stmt->executeQuery ();

  copy_rset_to_result;


  stmt->closeResultSet (rset);

  conn->terminateStatement (stmt);
  env->terminateConnection (conn);
}

my_method() {

 ResultSet *result = NULL
 go_to_oracle(result);
 //here connection is closed, but we have the data
 compute(result) // do this without have connection occupied

}

Any examples available on GitHub?

Answer 1

It is not possible to close the connection to the database and save the result set (occi::ResultSet) for later use. One reason is that occi::ResultSet::next retrieves data from the database. Instead you may use array fetch and a user allocated data buffer to store the results.

Example of use of occi::ResultSet::setDataBuffer:

oracle::occi::ResultSet* rs=nullptr;
//.....
// query
//.....
static const size_t max_numrows=5000;
char var_buf[max_numrows][7];
char sym_buf[max_numrows][9];
rs->setDataBuffer(1,var_buf,oracle::occi::OCCI_SQLT_STR,sizeof(var_buf[0]),(ub2*)0);
rs->setDataBuffer(2,sym_buf,oracle::occi::OCCI_SQLT_STR,sizeof(sym_buf[0]),(ub2*)0);
size_t fetch_count=0;
while(rs->next(max_numrows)==ResultSet::DATA_AVAILABLE)
{
    /* This would probably be an error as you would like
       the whole result to fit in the data buffer.*/
}
stmt->closeResultSet (rs);
conn->terminateStatement (stmt);
compute(var_buf,sym_buf);

Note that array fetch acts like prefetch in that

Status next(
   unsigned int numRows =1);

fetches up to numRows per call.