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.
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.
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); |
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. 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.
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;
|
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 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');
conn.commitTransaction();
|
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();
|
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 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();
|
Copyright © Rogue Wave Software, Inc. All Rights Reserved.
The Rogue Wave name and logo, and SourcePro, are registered trademarks of Rogue Wave Software. All other trademarks are the property of their respective owners.
Contact Rogue Wave about documentation or support issues.