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?

If it is possible create a class object and store each raw information into the class object and store it in a map. Do it when the program load. — Abhijit Pritam Dutta, Oct 17 '17 at 20:52

score 1 · Accepted Answer · answered Oct 16 '17 at 09:20

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.

If it is possible create a class object and store each raw information into the class object and store it in a map. Do it when the program load. — Abhijit Pritam Dutta, Oct 17 '17 at 20:51

score -1 · Answer 2 · answered Oct 13 '17 at 11:22

-1

Execute query does not retrieve data. You read data from server using rset->next (). So, if you terminate connection - you cannot read data

answered Oct 13 '17 at 11:22

Oleh Mykytyuk

15
2

I know that. I am looking for a solution that takes all the resultset without doing computation close the connection and validate the resultset later – cateof Oct 13 '17 at 14:24

Copy Occi::ResultSet before closing Occi::Connection

2 Answers2