Using cursors

Cursors can be used to efficiently perform SELECT statements with parameters and to pass binary data as parameters. Cursors can also be used to update database tables. The encapsulates cursor functionality into the ITCursor class.

The following excerpts from the cursupd.cpp example illustrate the use of ITCursor.

To use a cursor, the application creates an instance of ITCursor on the opened connection.
```
ITCursor cursor(conn);
```
The cursor is opened in a transaction. The preparation of the SELECT statement creates statement parameters.
```
conn.SetTransaction(ITConnection::Begin);

if(!cursor.Prepare("select b from bar where b < ?::integer;"))
{
```
If the application does not specify a parameter type name list, default parameter types are used (see The ITStatement class). Created parameters have NULL values.
When the application must set a parameter value, it obtains the ITValue * of the parameter through the call to the Param() function.
```
ITValue *par = cursor.Param(0);
if(!par)
```
The application can call the NumParams() function to obtain the number of parameters.
The application sets the parameter value by using ITValue::FromPrintable().
```
if(!par->FromPrintable("3"))
{
```
Alternatively, the application can obtain the required interface by calling QueryInterface() and use the update functions provided by the interface.
After all parameter values are set, the application opens the cursor with the flags representing the sum of ITCursor::Flags values.
```
if(!cursor.Open(0, "bar"))
{
```
By default, the cursor is opened as updateable and nonscrollable. The cursor cannot be opened as updateable and scrollable at the same time. If the application uses the UpdateCurrent() or DeleteCurrent() functions of the cursor, it must provide the name of the table that the cursor is created on as a second argument of Open().
The application can use a fetch function to find the row from the cursor. The fetch function accepts a pointer to the outer unknown interface for delegation (for more details about delegation, see Object Containment and Delegation). The pointer is null by default.
The fetch function can perform the positional fetch. If the cursor was not opened as scrollable, positional fetch fails. The application can call the IsScrollable() function to check whether the cursor is scrollable. The fetch function returns the pointer to the ITValue interface of the retrieved row. The NextRow() function returns the pointer to the ITRow interface of that row.
```
ITRow *row;
while(row = cursor.NextRow()) 
    {
    ITValue *col = row->Column(0); 
    if(!col) 
        {
        cerr << "Couldn't get the column from the cursor's row" << endl;
        return -1;
        } 

    cout << "Column 0 was " << col->Printable() << endl;
```
The following excerpts from the curstst.cpp example program illustrate the use of a scrollable cursor.
1. Fetch rows from the beginning to the end of the result set.
```
cout << "FORWARDS" << endl;
while ((rowValue = cursor.Fetch()) != NULL) 
{
    rowcount++;
    cout << rowValue->Printable() << endl;
    rowValue->Release();
}
```
2. Fetch rows from the end to the beginning of the result set.
```
cout << "BACKWARDS" << endl;
for (;;)
{
    if (!(row = cursor.NextRow(0, ITPositionPrior)))
        break;
    rowcount++;
    cout << row->Printable() << endl;
    row->Release();
}
```
3. Fetch every second row from the beginning to the end of the result set.
```
cout << "EVERY SECOND" << endl;
for (;;)
{
    if (!(row = cursor.NextRow(0, ITPositionRelative, 2 )))
        break;
    rowcount++;
    cout << row->Printable() << endl;
    row->Release();
}
```
4. Fetch the third row from the result set.
```
cout << "THIRD" << endl;
row = cursor.NextRow(0, ITPositionAbsolute, 3);
if (row != NULL)
{
    rowcount++;
    cout << row->Printable() << endl;
    row->Release();
}
```
5. Fetch the first row of the result set.
```
cout << "FIRST" << endl;
row = cursor.NextRow(0, ITPositionFirst);
if (row != NULL)
{
    rowcount++;
    cout << row->Printable() << endl;
    row->Release();
}
```
6. Fetch the last row of the result set.
```
cout << "LAST" << endl;
row = cursor.NextRow(0, ITPositionLast);
if (row != NULL)
{
    rowcount++;
    cout << row->Printable() << endl;
    row->Release();
}
```
7. Fetch the 500th row from the result set.
```
cout << "500th" << endl;
row = cursor.NextRow(0, ITPositionAbsolute, 500);
if (row != NULL)
{
    rowcount++;
    cout << row->Printable() << endl;
    row->Release();
}
```
  The cursor model in the Object Interface for C++ adheres to the following rules:
  - When the cursor is first opened, it is moved before the first row. When you retrieve a row, the cursor advances to the row and then retrieves the data.
  - When a cursor reaches the last row in a set it has scrolled through and a subsequent fetch returns NULL, the cursor remains moved on the last row. If you reverse the direction of the subsequent fetch to retrieve the previous row, then the second-to-last row is fetched.
  - If you fetch from the last row up to the first row until there are no more rows, the cursor remains moved on the first row.
  - Cursors do not wrap around. For example, you cannot open a cursor and retrieve the previous row in an attempt to wrap around to the last row. Similarly, you cannot wrap around from the last row to the first row.
  - When using ITPositionAbsolute to move the cursor, use 1 for the first row.

The application can modify the columns of the fetched row by using, for example, FromPrintable().

if(!colduprow->FromPrintable("2"))
    {
    cerr << "Couldn't set the column value" << endl;
    return -1;
    }
else
    {
    cout << "Column 0 is now " << colduprow->Printable() << endl;
    }

If the cursor was opened as updateable, the application can update the current row by using the UpdateCurrent() function, or delete it using DeleteCurrent(). The application can use the IsUpdatable() function to check whether the cursor can be updated. Calling UpdateCurrent() causes modifications that have been made to the current row to be reflected in the database. The current row being the row that was most recently returned by the Fetch() or the NextRow() function.
```
if(!cursor.UpdateCurrent())
{                                                     
    cerr << "Could not update the current row" << endl;
    return -1;
}
```

If the application fetches the row, holds its reference, and then fetches another row, the first row is no longer current, and updates to it are not reflected in the database when the application calls UpdateCurrent().

The application can close the cursor, modify parameters, and reopen the cursor. Reopening a cursor closes the current one. Parameter values that have not been reset stay the same.

After the application finishes with the cursor, it drops the cursor by using the Drop() function. The same instance of ITCursor can be used to prepare another cursor by calling Prepare(), which calls Drop() for the current cursor.