Accessors for class IliSQLCursor

Properties

bufferedRowsCount
columnsCount
lastExecuteRowsCount
sqlSession

Methods

execute
fetchNext
getColumn
getColumnAt
getErrorMessage
hasTuple
isErrorRaised
select

Description

The IliSQLCursor class allows you to submit SQL statements to a database and to retrieve any result sets produced by these statements. Before you can use an IliSQLCursor object, you must have an IliSQLSession object that is connected to a database.

Types of SQL Statements

There are two types of statements that can be submitted through a cursor:

Statements that yield a result set
Statements that do not yield a result set

SQL SELECT statements fall into the first category. The select method should be used to submit these statements. After the select method has been successfully called, the columns of the result set are available through the getColumn or getColumnAt methods.
SQL INSERT, UPDATE, and DELETE statements fall into the second category. SQL DDL statements such as CREATE TABLE also fall into this category. The execute method should be used to submit these statements.

Result Sets

Once an SQL SELECT statement has been successfully executed using the select method, it leaves a result set available for inspection through the cursor. A result set is an ordered collection of rows. Each of these rows has the same number of columns.
At any given point in time, the cursor is positioned either before the first row, on a row, or after the last row. Initially, just after the call to the select method succeeds, the cursor is positioned before the first row. The fetchNext method is then repeatedly used to move to the next row until the hasTuple method returns false.
A result set also has columns with each column having a name and an index. The getColumn method can be used to retrieve a column by its name. In addition, the columns of the current result set become properties of the IliSQLCursor object and can be accessed as such (that is, with the dot notation).
Here is an example (omitting error checking):

	session = IliSQLSession.MAIN;
	if (!session.isConnected())
	  session.connect();
	cursor = session.newCursor();
	cursor.select("SELECT NAME, ID FROM EMP");
	while (cursor.fetchNext() && cursor.hasTuple()) {
	   name = cursor.NAME.value;
	   id = cursor.ID.value;
	   writeln(name + "s id is " + id);
	}

Row Buffering

By default, each time the fetchNext method is called, a corresponding database API function is called. As a consequence it is possible, depending on the database system being used, that each call to the fetchNext method induces a network round-trip between the application and the database server. This may have adverse consequences on the performance of the application and it may impose an unnecessary overhead on network traffic.
An alternative is to use row buffering. This is a technique by which more than one row is requested at one time from the underlying database API. When the database system supports this feature, the number of network round trips may be considerably reduced since more than one row can be transmitted at once.
It should be noted that although increasing the number of buffered rows boosts the performance of the application, it imposes an overhead on the memory allocated by the application since a buffer sufficiently large to hold the buffered rows has to be pre-allocated.

Type Name Description Notes

Int bufferedRowsCount Contains the number of buffered rows. The default value for this property is 1, which means that each call to fetchNext requests one row from the database API. Setting the value property to N where N > 1 has the effect that the first call to fetchNext will request N rows at once from the database API. The N-1 succeeding calls to fetchNext will simply move a mark into an internal buffer where all N rows are stored. The N+1th call to fetchNext will then request the next N rows, and so on.

Int columnsCount Contains the number of columns of the result set produced by the last SQL statement submitted (if it produced a result set). Contains 0 otherwise. read-only

Int lastExecuteRowsCount Contains the number of database rows that were affected by the last non SELECT SQL statement submitted. This is typically useful with SQL UPDATE and SQL DELETE statements. read-only

IliSQLSession sqlSession Contains the session from which this cursor has been obtained. read-only

Boolean execute(String statement) Executes the SQL statement returning true if successful. The statement parameter can contain any SQL statement except the SQL SELECT statement.

Boolean fetchNext() Positions the cursor over the next row in the result set. It returns true if successful. To determine if the result set has been exhausted, you can call the hasTuple method.

IliSQLCursorColumn getColumn(String name) Returns the column named name in the result set produced by the last SQL statement submitted. Returns null if no such column exists, if there is no current result set, or if there is no current row. Note that since the result set columns become properties of the IliSQLCursor object, they can be directly accessed. For example:

var age = cursor.AGE.value;

IliSQLCursorColumn getColumnAt(Int index) Returns the column at position index or null if index is out of bounds. The index parameter must be >= 0 and < columnsCount.

IliErrorMessage getErrorMessage() Returns the error message caused by the failure of last method call or null if no such failure occurred.

Boolean hasTuple() Returns true if the cursor is currently positioned on a valid row in the result set. It returns false if the result set has been exhausted.

Boolean isErrorRaised() Returns true if the last method call has failed.

Boolean select(String statement) Executes the SQL SELECT statement specified in statement. It returns true if successful. The result set produced by the SQL SELECT statement can then be obtained one row at a time by calling repeatedly the fetchNext member function. Note that the result set will remain available until the next call to one of the execute or select methods.

Type	Name	Description	Notes
`Int`	`bufferedRowsCount`	Contains the number of buffered rows. The default value for this property is 1, which means that each call to `fetchNext` requests one row from the database API. Setting the value property to `N` where `N > 1` has the effect that the first call to `fetchNext` will request `N` rows at once from the database API. The `N-1` succeeding calls to `fetchNext` will simply move a mark into an internal buffer where all `N` rows are stored. The `N+1th` call to `fetchNext` will then request the next `N` rows, and so on.
`Int`	`columnsCount`	Contains the number of columns of the result set produced by the last SQL statement submitted (if it produced a result set). Contains `0` otherwise.	read-only
`Int`	`lastExecuteRowsCount`	Contains the number of database rows that were affected by the last non `SELECT` SQL statement submitted. This is typically useful with SQL UPDATE and SQL DELETE statements.	read-only
`IliSQLSession`	`sqlSession`	Contains the session from which this cursor has been obtained.	read-only
`Boolean`	`execute(String statement)`	Executes the SQL statement returning `true` if successful. The `statement` parameter can contain any SQL statement except the SQL SELECT statement.
`Boolean`	`fetchNext()`	Positions the cursor over the next row in the result set. It returns `true` if successful. To determine if the result set has been exhausted, you can call the `hasTuple` method.
`IliSQLCursorColumn`	`getColumn(String name)`	Returns the column named `name` in the result set produced by the last SQL statement submitted. Returns `null` if no such column exists, if there is no current result set, or if there is no current row. Note that since the result set columns become properties of the `IliSQLCursor` object, they can be directly accessed. For example: var age = cursor.AGE.value;
`IliSQLCursorColumn`	`getColumnAt(Int index)`	Returns the column at position `index` or `null` if `index` is out of bounds. The `index` parameter must be `>= 0` and `< columnsCount`.
`IliErrorMessage`	`getErrorMessage()`	Returns the error message caused by the failure of last method call or `null` if no such failure occurred.
`Boolean`	`hasTuple()`	Returns `true` if the cursor is currently positioned on a valid row in the result set. It returns `false` if the result set has been exhausted.
`Boolean`	`isErrorRaised()`	Returns `true` if the last method call has failed.
`Boolean`	`select(String statement)`	Executes the SQL SELECT statement specified in `statement`. It returns `true` if successful. The result set produced by the SQL SELECT statement can then be obtained one row at a time by calling repeatedly the fetchNext member function. Note that the result set will remain available until the next call to one of the `execute` or `select` methods.