Data Access and SQL > Connecting to a Database > SQL Sessions and Cursor Objects > Cursors
 
Cursors
Before you can do anything useful with your session object, you must obtain an IliSQLCursor object. The cursor allows you to submit SQL statements to a database and to retrieve any result sets produced by these statements. A cursor can be created in the following way:
IliSQLCursor* cursor = session->newCursor();
if (cursor != 0) {
...
 
session->releaseCursor(cursor);
}
else
IlvPrint(“Out of cursors.”);
Once a cursor object has been created, any SQL statements (except SELECT statements) can be submitted to the database using the execute member function. An example of this is shown in the following code:
if (cursor->execute(“UPDATE EMP SET SALARY = SALARY * 1.1”)) {
IlvPrint(“Happy days!”);
}
else
IlvPrint(“Error: %s”, cursor->getErrorMessage().getMessage());
The SQL SELECT Statement and Its Result Set
To submit an SQL SELECT statement you can use the select member function as follows:
if (cursor->select(“SELECT NAME, SALARY FROM EMP”)) {
while (cursor->fetchNext() && cursor->hasTuple()) {
IlvPrint(“Employee %s : %ld”,
cursor->getStringValue(0),
cursor->getIntegerValue(1));
}
}
else
IlvPrint(“Error: %s”, cursor->getErrorMessage().getMessage());
At the beginning of the inspection process just after the select member function has been called, the cursor is positioned before the first row. Each call to the fetchNext member function moves the cursor to the next row. When all rows have been seen, a call to fetchNext positions the cursor after the last row.
The hasTuple member function can be called to determine if the cursor is positioned on a row (as opposed to being positioned before the first row or after the last row). If the cursor is positioned after the last row, the result set has been exhausted.
Once an SQL SELECT statement has been successfully executed it leaves a result set available for inspection through the cursor. A result set is an ordered collection of rows. Each of these rows conforms to the same schema.
You can retrieve the value of a column using the getValue method. An example of this is shown in the following code:
IliValue value;
if (cursor->getValue(colno, value)) {
...
}
The columns of the result set are identified by their position, starting from 0. If you know the type of a given column in the result set, you can use one of the following methods:
const char* IliSQLCursor::getStringValue(Ilnt colno) const;
IlInt IliSQLCursor::getIntegerValue(IlInt colno) const;
IlFloat IliSQLCursor::getFloatValue(IlInt colno) const;
IlDouble IliSQLCursor::getDoubleValue(IlInt colno) const;
IliDate IliSQLCursor::getDateValue(IlInt colno) const;
IliBinary IliSQLCursor::getBinaryValue(IlInt colno) const;
The character string returned by the getStringValue member function and the byte array returned by the getBinaryValue member function (it is part of the IliBinary structure) belong to the cursor. Therefore, they will be overwritten the next time one of the fetchNext, select, or execute member functions is called.
Note that the getStringValue member function will return NULL if the column is not of type character string. If you want to convert a value into a string, use the getValue member function as shown in the following example:
IliValue value;
if (cursor->getValue(colno, value)) {
IlvPrint(“%s”, value.getFormatted());
}
The member function isNull tests whether a given column is null. The testing of a column is shown in the following example:
if (cursor->isNull(colno))
IlvPrint(“NULL”);
else
IlvPrint(“%s”, cursor->getStringValue(colno));
All work done on a session object through its cursors belongs to a transaction. Because of transaction management, any work done needs to be committed or canceled (rolled back) at some point in time.
To commit or roll back the work done on a session object, use the commit and rollback member functions. An example of their usage is shown in the following code:
if (session->commit())
IlvPrint(“Work done”);
else
IlvPrint(“Error: %s”, session->getErrorMessage().getMessage());
If you forget to commit your work, it will eventually be canceled (rolled back) when the session is freed.
To obtain information on the structure of your result set after a successful call to the select member function, you can use the getSchema member function.
const IliSchema* schema = cursor->getSchema();
This member function returns a schema object that belongs to the cursor. (See the IliSchema class in the Rogue Wave Views Data Access Reference Manual.) Note that this schema object can be modified the next time you call the select or execute member functions on that cursor, so it should be used as soon as possible.
When you are finished with the cursor object you should release it using the releaseCursor member function.

Version 5.7
Copyright © 2013, Rogue Wave Software, Inc. All Rights Reserved.