4.11 Cursors

Class RWDBCursor is the DBTools.h++ encapsulation of database cursors. Class RWDBCursor is a relatively low-level construct that maps directly onto a database cursor. It supports the cursor operations of bind, fetch, insert, update, and delete.

Despite the efforts of various standards bodies, cursor capabilities vary widely among database vendors.

NOTE: DBTools.h++ won't emulate functionality not supported by an underlying database engine.

For example, if a database vendor's implementation does not support scrollable cursors, an application requesting a scrollable RWDBCursor from that RWDBDatabase receives an RWDBCursor with a status of RWDBStatus::notSupported. For this reason, using RWDBCursor makes programs less portable than they might otherwise be.

NOTE: See your DBTools.h++ access library documentation for details concerning RWDBCursor restrictions for your particular database.

RWDBCursor captures common features of database cursors. Specifically:

There can be multiple RWDBCursor instances open on a single connection.
RWDBCursor supports positioned inserts, updates, and deletes.
RWDBCursor may be scrollable or sequential.
RWDBCursor operates on application-supplied buffers. It is pointer-based rather than value-based.

Instances of RWDBCursor are created in much the same manner as RWDBSelector instances. The cursor() member function of the RWDBDatabase class produces an instance of RWDBCursor. There are also cursor() member functions for RWDBTable and RWDBSelector, each forwarding the actual creation of the cursor to its associated database. Only RWDBTable instances representing database tables produce usable RWDBCursor instances. In particular, RWDBMemTable and RWDBTPtrMemTable produce invalid cursors. The following lines create RWDBCursor instances:

RWDBCursor cursor1 = aDatabase.cursor(aSelector);
RWDBCursor cursor2 = aTable.cursor();
RWDBCursor cursor3 = aSelector.cursor();

Cursors can also be created with alternative access attributes. Some databases support scrollable cursors. These cursors need not access data linearly; they can jump around. Scrollable cursors are created with the special flag:

RWDBCursor::Scrolling;
RWDBCursor cursor3 = aSelector.cursor();

The insertion operator << is used to implement the cursor bind operation. When you use instances of RWDBCursor, you must provide the addresses of variables that will hold the data arriving from the database. In the following example, the cursor object returns three columns of data. Therefore, three variables are bound to hold the incoming data:

int x;
float y;
RWCString z;
cursor << &x << &y << &z;

The addresses of the variables generally get passed all the way down to the level of the database vendor's API. Of course, in the example above, no vendor's API can understand how to insert data into an RWCString instance. DBTools.h++ recognizes this, so it allocates a buffer of the appropriate size and hands it down to the database level. As rows are fetched, DBTools.h++ arranges for results to be copied into the correct variables.

It is important to note that an application continues to own the memory pointed to and supplied to an RWDBCursor instance. It is the application's responsibility to ensure that a pointer remains valid for as long as the RWDBCursor requires it. In the following contrived example, a function is defined to produce an instance of RWDBCursor. However, the local variables bound to the cursor have gone out of scope when the function returns the RWDBCursor instance.

RWDBCursor initializeMyCursor (RWDBTable t) {
  RWDBCursor c = t.cursor();
  int i, j, k;
  c << &i << &j << &k; //binding the cursor to local variables
  return c;            //error! i, j and k are about to go 
                       //out of scope
}

When the addresses of variables are inserted for binding, they are associated in order, beginning with the first column (index 0). The indexing operator
operator[] can set the position to a specified index, column, or column name. This allows you to bind variables out of order. Any of the following forms may be used:

int x, y
cursor << &x << &y;                                           //1
cursor[7] << &x; cursor[5] << &y;                             //2
cursor["col1"] << &x; cursor["col2"] << &y;                   //3
cursor[table1["col1"]] << &x; cursor[table2["col2"]] << &y;   //4

The insertion on //1 binds the addresses of the variables x and y to the first and second columns of the cursor's result set, assuming that these are the first insertions since the construction or execution of the cursor. On //2, the variables are bound to columns numbered 7 and 5, respectively, where column indexing begins with 0. Line //3 binds the variables to the columns named col1 and col2. Finally, //4 binds to the columns in the cursor's result set which represent the columns table1.col1 and table2.col2 in the database.

Once the binding process is complete, the cursor is ready to fetch data. Invoking the member function fetchRow() accomplishes this. Using the addresses given to the cursor in the bind stage, one row of data is retrieved from the database. The following is an example of using a cursor for fetching data:

RWDBTable inventory = aDatabase.table("Inventory");
RWDBSelector aSelector = aDatabase.selector();
aSelector << inventory["partNumber"] << inventory["onHand"];
aSelector.orderBy (inventory["partNumber"]);

RWDBCursor aCursor = aDatabase.cursor(aSelector);
unsigned long partNumber;
unsigned int numberOnHand;
aCursor << &partNumber << &numberOnHand;
cout << "Part Number\tQuantity" << endl;
while (aCursor.fetchRow().isValid())
  cout << partNumber << '\t' << numberOnHand;

Instances of RWDBCursor can also be used to delete rows from a table. This use is an encapsulation of the SQL statement:

DELETE tableName WHERE CURRENT OF cursorName

The following example allows users to delete names from a table:

RWDBConnection conn = aDatabase.connection();
RWDBTable customers = aDatabase.table("Customer");
RWDBSelector aSelector = aDatabase.selector();
aSelector << customers["name"];
conn.beginTransaction();
RWDBCursor aCursor = 
     aSelector.cursor(RWDBCursor::Sequential, RWDBCursor::Write);
RWCString customerName;
aCursor << &customerName;
while (aCursor.fetchRow().isValid()) {
  cout << "Delete " << customerName << " (Y or N)? ";
  char answer;
  cin >> answer;
  if (answer == 'y' || answer == 'Y')
    aCursor.deleteRow(customers.name());
}
conn.commitTransaction();

Instances of RWDBCursor are also useful for updating rows in a table. This use of RWDBCursor is an encapsulation of the SQL statement:

UPDATE tableName SET <set-clause> 
          WHERE CURRENT OF cursorName

The following example allows a user to correct names in a table:

RWDBConnection conn = aDatabase.connection();
RWDBTable customers = aDatabase.table("Customer");
RWDBSelector aSelector = aDatabase.selector();
aSelector << customers["name"];
conn.beginTransaction();
RWDBCursor aCursor = 
    aSelector.cursor(RWDBCursor::Sequential, RWDBCursor::Write);
RWCString customerName;
aCursor << &customerName;
while (aCursor.fetchRow().isValid()) {
  cout << "Is " << customerName << "correct (Y or N)? ";
  char answer;
  cin >> answer;
  if (answer == 'n' || answer == 'N') {
    cout << "Enter the correction: ";
    RWCString  firstName, lastName;
    cin >> firstName;
    cin >> lastName;
    customerName = lastName + ',' + firstName;
    aCursor.updateRow (table.name());
  }
}
conn.commitTransaction();