Retrieve Rows from the Database

Similar to database cursors, a Data Access Builder cursor is a means of moving through a collection of row objects one at a time from top to bottom. It is a more memory-wise alternative to retrieving a collection of rows.

Using Select Method
When you retrieve a collection of rows using the select method, or your own method defined using SQL Predicate, a cursor is opened, the desired rows are fetched into memory, and the cursor is closed; after that, you access the fetched rows with items method. For example,

// Select all managers where phoneno is null
theDeptMgr.select( "PHONENO IS NULL" );
// Print each row
for(int i=0; i< theDeptMgr.items()->numberOfElements(); i++)
{
  cout << theDeptMgr.items()->elementAt(i)->forDisplay() << endl;
}

If the method retrieves many rows, they may take up a lot of memory.

Using fetchNext Method
With cursor-enabled access, you can open a cursor and leave it open. When you use fetchNext, the cursor fetches each row into a cached object, which is overwritten when it fetches the next object. Memory usage is minimal.

Using methods that the tool generates for managing multiple rows, fetch each row as you need it, for example:

// Open the cursor on the entire Department Manager database,
// ordering the records by department name
theDeptMgr.open( "ORDER BY DEPTNAME" );
// Repeat for each row
while( theDeptMgr.fetchNext() )
{
  // Get the row
  Department* theDept = theDeptMgr.element();
  // Print out the row
  cout << theDept->forDisplay() <<endl;
}
// close the cursor
theDeptMgr.close();

Use generated methods such as updateFetched and deleteFetched to update and delete rows, respectively. (These methods are not generated for read-only tables nor table-join schemas.)

You can also fetch a row directly into a PersistentObject object by using fetchedNextInto method. This allows you to directly access the attributes in the PersistentObject.

Using Vectors to Fetch Several Rows
To improve the efficiency of the code, use fill method to fetch a specified number of rows into a cached vector, process the rows, and then fetch the next set of rows. For example:

// Open the cursor on the entire Department Manager database,
// ordering the records by department name
theDeptMgr.open( "ORDER BY DEPTNAME" );
// Fetch 10 rows
int num = theDeptMgr.fill(10);
// Print each row
for(int i=0; i< num; i++)
{
  cout << theDeptMgr.items()->elementAt(i)->forDisplay() << endl;
}
// Also prints the actual number of rows retrieved
// The actual number of rows retrieved is returned by fill
cout << "number of elements is " << num << endl;
// close the cursor
theDeptMgr.close();

Each time you call the fill method, it overwrites what was previously in the cached vector.

The append method does the same thing as the fill method, except it does not overwrite the previously filled vector, but rather appends the next rows onto what is currently in the vector. You can also specify a specific vector for the append method to fetch methods into, as shown in the following example:

// Create a vector, listManager, to be used for fetched rows
ISequence<Department>    listManager;
// Open a cursor
theDeptMgr.open( "ORDER BY DEPTNAME" );
// Fetch 10 rows
int num = theDeptMgr.append( *listManager, 10 );
// Print each row
for (int i=0; i < num; i++)
{
  cout << listManager.elementAt(i)->forDisplay() << endl;
}
// Fetch 5 more rows
int num2 = theDeptMgr.append( *listManager, 5 );
// Print out the size of the listManager vector, which will be 15
cout << "Size of listManager vector: " << listManager->numberOfElements(); << endl;

See Data Access Class Library and Generated Class Reference for more information on generated classes and methods.



User-Defined Methods