ROWID and OCI

From: Al Slater <al.slater_at_scluk.com>
Date: Tue, 4 Jan 2000 13:01:28 -0000
Message-ID: <84sqtn$m3c$1_at_quince.news.easynet.net>

Hi

To implement a bi-directional cursor, I am using a STL map to store ROWID and status information for each row in a table as they are read. The following is the structure holding the information:

struct keyset_element {

   OCIRowid *row_id;
   UWORD status;
};

and the map for implementing the keyset:

map<SDWORD, keyset_element, less<SDWORD> > keyset_map;

As each row is read from the database I then perform the following to get the ROWID and store it (abridged):

OCIRowid *row_id;

statement->set_retcode(
  OCIStmtFetch(statement->oci_stmt,statement->oci_error,

               1, OCI_FETCH_NEXT, OCI_DEFAULT)); if (!statement->success()) {
  if (statement->get_retcode() == SQL_NO_DATA_FOUND) {

     statement->at_end_of_result_set = true;   }
  return statement->get_retcode();
}

statement->highest_row_fetched++;

{
  keyset_element element;
  statement->keyset_map[statement->highest_row_fetched] = element;
  statement->keyset_map[statement
>highest_row_fetched].row_id = NULL;

// Allocate a ROWID descriptor
statement->set_retcode(
  OCIDescriptorAlloc(
    statement->connection->environment >oci_env,
(dvoid**)(&row_id),

[Quoted] [Quoted] [Quoted]     OCI_DTYPE_ROWID, (size_t)0, (dvoid **)0),     statement->connection->environment->oci_env);

if (!statement->success()) {
  statement->push_error("AL001","Could not allocate a ROWID descriptor");
  return statement->get_retcode();
}

// Get the ROWID attribute for the current row. // Note, you pass a pointer to OCIRowid, not a pointer to the pointer
// as the documentation says (Ask Oracle ID 2722). statement->set_retcode(
  OCIAttrGet(
(const dvoid *) statement->oci_stmt,
(ub4) OCI_HTYPE_STMT,
(dvoid *) row_id,
(ub4 *) 0,
(ub4) OCI_ATTR_ROWID,
(OCIError *) statement->oci_error ));

if (!statement->success()){
  statement->push_error("AL002","Could not retrieve ROWID into descriptor");
  return statement->get_retcode();
}

statement->keyset_map[statement->highest_row_fetc hed].row_id

• row_id;

statement->keyset_map[statement->highest_row_fetched].status

• SQL_ROW_SUCCESS;

When the calling program then requests a row that has already been read, I then retrieve the ROWID from the map (based on row number), and attempt to retrieve the record with the following:

string sql;
sql = "SELECT " + statement->select_list + " FROM " + statement->table_name +

         " WHERE ROWID = (:1)"; statement->set_retcode(
  OCIStmtPrepare(
    rowid_select,
    statement->oci_error,
    reinterpret_cast<text*>(
      const_cast<char*>(sql.c_str())),
    sql.length(),
    OCI_NTV_SYNTAX,
    OCI_DEFAULT)); if (!statement->success()){
  return statement->get_retcode();
}

OCIBind *row_id_bind = 0;
 row_id = statement->keyset_map[irow].row_id;

statement->set_retcode(
  OCIBindByPos(
(OCIStmt *) rowid_select,
(OCIBind **) &row_id_bind,
(OCIError *) statement->oci_error,
(ub4) 1,
(dvoid *) &row_id,
(sb4) 0,
(ub2) SQLT_RDD,
(dvoid *) 0,
(ub2 *) 0,
(ub2 *) 0,
(ub4) 0,
(ub4 *) 0,
(ub4) OCI_DEFAULT));

if (!statement->success()){
  return statement->get_retcode();
}

/* Perform the column bindings here */

statement->set_retcode(
  OCIStmtExecute(
    oci_svc_ctx,
    rowid_select,
    statement->oci_error,
    0, 0, 0, 0, OCI_DEFAULT)); if (!statement->success()){
  return statement->get_retcode();
}

This is where it goes wrong. The above statement execute allways fails with an "ORA-01410: invalid ROWID" error.

I am retrieving the correct data from the map; it looks to me as if an incorrect rowid attribute is being retrieved from the statement in the first place.

This is being run with the OCI with Oracle 8.0.5 client (Win98 and Solaris 2.6).

If I run it with the OCI from Oracle 8.1.5 client it fails when it does the OCIAttrGet to retrieve the rowid.

Can you spot where I am going wrong?

Thanx in advance

Al Slater Received on Tue Jan 04 2000 - 14:01:28 CET