Cursors
Class
RWDBCursor is the encapsulation of database cursors in the DB Interface Module. This class 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. 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 be otherwise.
NOTE >> SourcePro DB won’t emulate functionality that is NOT supported by an underlying database engine. See your DB Access Module guide for details concerning RWDBCursor restrictions for your particular database. Using RWDBCursor may reduce portability.
Class
RWDBCursor captures common features of database cursors. Specifically, the class supports:
• multiple
RWDBCursor instances open on a single connection
• positioned inserts, updates, and deletes
• scrollable or sequential cursors
• operation on application-supplied buffers.
RWDBCursor is pointer-based rather than value-based.
Instantiating an RWDBCursor
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 or derived 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(RWDBCursor::Scrolling);
Bind Operations
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 fetch data into an
RWCString instance. The DB Interface Module recognizes this, so it allocates a buffer of the appropriate size and hands it down to the database level. As rows are fetched, the DB Interface Module 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.
Fetch Operation
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;
std::cout << "Part Number\tQuantity" << std::endl;
while (aCursor.fetchRow().isValid())
std::cout << partNumber << '\t' << numberOnHand << std::endl;
Insert Operation
Instances of
RWDBCursor may be used to insert rows in a table. This use is an encapsulation of the SQL statement:
INSERT tableName WHERE CURRENT OF cursorName
The following example allows users to insert new rows in the customer table:
RWDBConnection conn = aDatabase.connection();
RWDBTable customers = aDatabase.table("customer");
RWDBSelector aSelector = aDatabase.selector();
aSelector << customers["name"] << customers["address"];
conn.beginTransaction();
RWDBCursor aCursor =
aSelector.cursor(conn, RWDBCursor::Scrolling, RWDBCursor::Write);
RWCString customerName, customerAddress;
aCursor << &customerName << &customerAddress;
char answer = 'Y';
do {
std::cout << "\n=== List of Current Customers ===" << std::endl;
if (aCursor.fetchRow(RWDBCursor::First).isValid()) {
do {
std::cout << customerName << "\t"
<< customerAddress << std::endl;
} while (aCursor.fetchRow().isValid());
}
std::cout << "\nAdd a customer (Y or N)? ";
std::cin >> answer;
if (answer == 'y' || answer == 'Y') {
std::cout << "Enter the new customer information: ";
RWCString firstName, lastName;
std::cin >> firstName >> lastName >> customerAddress;
customerName = lastName + "," + firstName;
aCursor.insertRow(customers.name());
}
} while (answer == 'y' || answer == 'Y');
Delete Operation
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(conn, RWDBCursor::Sequential, RWDBCursor::Write);
RWCString customerName;
aCursor << &customerName;
while (aCursor.fetchRow().isValid()) {
std::cout << "Delete " << customerName << " (Y or N)? ";
char answer;
std::cin >> answer;
if (answer == 'y' || answer == 'Y')
aCursor.deleteRow(customers.name());
}
conn.commitTransaction();
Update Operation
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 the customer table:
RWDBConnection conn = aDatabase.connection();
RWDBTable customers = aDatabase.table("Customer");
RWDBSelector aSelector = aDatabase.selector();
aSelector << customers["name"];
conn.beginTransaction();
RWDBCursor aCursor =
aSelector.cursor(conn, RWDBCursor::Sequential, RWDBCursor::Write);
RWCString customerName;
aCursor << &customerName;
while (aCursor.fetchRow().isValid()) {
std::cout << "Is " << customerName << "correct (Y or N)? ";
char answer;
std::cin >> answer;
if (answer == 'n' || answer == 'N') {
std::cout << "Enter the correction: ";
RWCString firstName, lastName;
std::cin >> firstName;
std::cin >> lastName;
customerName = lastName + "," + firstName;
aCursor.updateRow(table.name());
}
}
conn.commitTransaction();