Database access class. More...

#include <ilviews/dataccess/dbms/cursor.h>

Inheritance diagram for IliSQLCursor:

Public Member Functions
IlInt	defineParam (const char paramName, const IliDatatype type)
	Defines a parameter. More...

IlBoolean	errorRaised () const
	Indicates if the last call to one of the `select()`, `execute()`, `parse()`, or `fetchNext()` member functions failed. More...

IlBoolean	execute (const char stmt, IlInt pRowCount=0)
	Executes a SQL statement. More...

IlBoolean	execute (IlInt *pRowCount=0)
	Executes a parsed SQL statement. More...

IlBoolean	fetchNext ()
	Positions the cursor on the next row in the result set. More...

IliBinary	getBinaryValue (IlInt colno) const
	Returns a binary value. More...

IlInt	getBufferedRowsCount () const
	Returns the number of buffered rows. More...

IliDate	getDateValue (IlInt colno) const
	Returns a date value. More...

IlDouble	getDoubleValue (IlInt colno) const
	Returns a double value. More...

IliErrorMessage	getErrorMessage () const
	Returns the error message relative to the last call to one of the `select()`, `execute()`, `parse()` or `fetchNext()` member functions. More...

IlFloat	getFloatValue (IlInt colno) const
	Returns a float value. More...

IlInt	getIntegerValue (IlInt colno) const
	Returns an integer value. More...

IlBoolean	getOutputParam (IlInt paramno, IliValue &val) const
	Retrieves the value of the output parameter. More...

IlInt	getParamIndex (const char *paramName) const
	Returns the position of the defined parameter. More...

const char *	getParsedStatement () const
	Returns the SQL statement that has been successfully parsed. More...

const IliSchema *	getSchema () const
	Returns the schema of the current result set. More...

IliSQLSession &	getSQLSession () const
	Returns the `IliSQLSession` which the cursor belongs to. More...

const char *	getStringValue (IlInt colno) const
	Returns a string value. More...

IlBoolean	getValue (IlInt colno, IliValue &val)
	Returns a value. More...

IlBoolean	hasTuple () const
	Indicates if the cursor is currently positioned on a valid row in the result set. More...

IlBoolean	isNull (IlInt colno) const
	Indicates if the value is null. More...

IlBoolean	parse (const char *stmt, IlBoolean useBoundVars=IlTrue)
	Parses an SQL statement for future execution. More...

IlBoolean	select (const char *stmt)
	Executes a SQL `SELECT` statement. More...

IlBoolean	select ()
	Executes a parsed SQL `SELECT` statement. More...

IlBoolean	setBufferedRowsCount (IlInt count)
	Sets the number of buffered rows. More...

IlBoolean	setParam (IlInt paramno, const IliValue &val)
	Sets the value of the parameter. More...

Public Member Functions inherited from IliRefCounted
IlInt	getRefCount () const
	Returns the reference count of the object. Initially, this property is set to `0`. More...

void	lock () const
	Increments the reference count of the object.

void	unLock () const
	Decrements the reference count of the object. More...

Friends
class	IliSQLSession

Additional Inherited Members
Protected Member Functions inherited from IliRefCounted
virtual	~IliRefCounted ()
	This is the virtual destructor of the `IliRefCounted` class. More...

Detailed Description

Database access class.

Library: dbaccess
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.

About Parsing and Executing

An alternative way to submit an SQL statement to a database is to request that an SQL statement is parsed before being executed any number of times. Although this technique is a little more sophisticated, it provides some added benefits. Firstly, if an SQL statement is to be submitted more than once, it reduces the overall processing time: the parsing of the statement is only done once. Secondly, it allows the use of SQL statements containing parameters.

A parameter is an identifier prefixed by a colon (for example, :name) that appears in the text of an SQL statement instead of a literal value. It may be considered as a kind of variable. A statement containing parameters can be parsed, when values can be specified for the parameters, and then executed.

Once a statement has been parsed, it can be executed any number of times, each time with a different set of values for the parameters.

About result set

Once an SQL SELECT statement has been successfully executed using one of the two select() member functions, 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.

At any given point in time, the cursor is positioned either before the first row, on a row, or after the last row. At the beginning of the inspection process, just after one of the select() member functions 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).

About values

Once the cursor is positioned on a valid row, the member functions in this section can be used to retrieve the values of the columns of the row. A column is identified by its position in the schema of the result set, starting from 0.

See also: IliDatatype, IliErrorMessage, IliSchema, IliSQLSession, IliValue.

Accessors

Accessors provide a scriptable and uniform way to inspect and modify an object by using its base class methods IlvValueInterface::queryValue(), IlvValueInterface::queryValues(), IlvValueInterface::changeValue(), IlvValueInterface::changeValues(). This class defines the following accessors:

Data accessors

Name	Type	Equivalent methods
`bufferedRowsCount`	Int	`getBufferedRowsCount()`, `setBufferedRowsCount()`
`columnsCount`	Int	`getSchema()->getColumnsCount()`
`lastExecuteRowsCount`	Int	See [lastExecuteRowsCount] below.
`sqlSession`	`IliSQLSession`	`getSQLSession()`

[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 DELETE statements.

Method accessors

Name	Return type	Equivalent methods
`execute(String statement)`	Boolean	`execute(statement)`
`fetchNext()`	Boolean	`fetchNext()`
`getColumn(String name)`	Object	See [getColumn] below.
`getColumnAt(Int index)`	Object	See [getColumn] below.
`getErrorMessage()`	`IliErrorMessage`	`getErrorMessage()`
`hasTuple()`	Boolean	`hasTuple()`
`isErrorRaised()`	Boolean	`errorRaised()`
`select(String statement)`	Boolean	`select(statement)` . See [select] below.

[getColumn] Returns the column named name or at position index 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;

[select] Here is an example in Rogue Wave Views Script, 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);
}

Member Function Documentation

§ defineParam()

IlInt IliSQLCursor::defineParam	(	const char *	paramName,
		const IliDatatype *	type
	)

Defines a parameter.

This member function must be called exactly once for each parameter in the SQL statement that has been parsed. All defined parameters are lost the next time one of the parse(), select(const char*), or execute(const char*, IlInt*) member functions is called. Initially, the parameter value is set to 0.

Parameters

paramName	The parameter name, that must not include the colon prefix.
type	The parameter type.

Returns: The position of the newly defined parameter.

§ errorRaised()

IlBoolean IliSQLCursor::errorRaised ( ) const

Indicates if the last call to one of the select(), execute(), parse(), or fetchNext() member functions failed.

You can use the ForwardErrorToGlobalSink() function of the IliSQLSession class to forward the error to the global error sink.

Returns: IlTrue if the last call to one of theselect(), execute(),parse(), orfetchNext()` member functions failed. In this case, an error message is available through the getErrorMessage() member function.

§ execute() [1/2]

IlBoolean IliSQLCursor::execute	(	const char *	stmt,
		IlInt *	pRowCount = `0`
	)

Executes a SQL statement.

Parameters

stmt	Any SQL statement except the SQL `SELECT` statement.
pRowCount	If not `0`, it will receive the number of rows affected by the execution of the statement, when applicable.

Returns: IlTrue if successful.

§ execute() [2/2]

IlBoolean IliSQLCursor::execute ( IlInt * pRowCount = 0 )

Executes a parsed SQL statement.

It may be called for any SQL statement except the SQL SELECT statement. This member function can be called any number of times as long as the SQL statement remains parsed.

Parameters

pRowCount If not 0, it will receive the number of rows affected by the execution of the statement, when applicable.

Returns: IlTrue if successful.

§ fetchNext()

IlBoolean IliSQLCursor::fetchNext ( )

Positions the cursor on the next row in the result set.

Returns: IlTrue if successful. To determine if the result has been exhausted, you can call the hasTuple() member function.

§ getBinaryValue()

IliBinary IliSQLCursor::getBinaryValue ( IlInt colno ) const

Returns a binary value.

Parameters

colno The value position.

Returns: The binary value if the value is binary. Otherwise, the return value is a default IliBinary object.

§ getBufferedRowsCount()

IlInt IliSQLCursor::getBufferedRowsCount ( ) const

Returns the number of buffered rows.

Returns: 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 of this 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+1 call to fetchNext() will then request the next N rows, and so on.

§ getDateValue()

IliDate IliSQLCursor::getDateValue ( IlInt colno ) const

Returns a date value.

Parameters

colno The value position.

Returns: The number cast into a date if the value is a date. If it is a character string containing a valid representation for a date, then it returns the date. Otherwise, the return value is a default IliDate object.

§ getDoubleValue()

IlDouble IliSQLCursor::getDoubleValue ( IlInt colno ) const

Returns a double value.

Parameters

colno The value position.

Returns: The number cast into a double if the value is a number. If it is a character string containing a valid representation for a number, then it returns the number cast into a double. Otherwise, the return value is 0.0.

§ getErrorMessage()

IliErrorMessage IliSQLCursor::getErrorMessage ( ) const

Returns the error message relative to the last call to one of the select(), execute(), parse() or fetchNext() member functions.

You can use the ForwardErrorToGlobalSink() function of the IliSQLSession class to forward the error to the global error sink.

Returns: The error message relative to the last call to one of the select(), execute(), parse() or fetchNext() member functions.

§ getFloatValue()

IlFloat IliSQLCursor::getFloatValue ( IlInt colno ) const

Returns a float value.

Parameters

colno The value position.

Returns: The number cast into a float if the value is a number. If it is a character string containing a valid representation for a number, then it returns the number cast into a float. Otherwise, the return value is 0.0.

§ getIntegerValue()

IlInt IliSQLCursor::getIntegerValue ( IlInt colno ) const

Returns an integer value.

Parameters

colno The value position.

Returns: The number cast into an integer if the value is a number. If it is a character string containing a valid representation for a number, then it returns the number cast into an integer. Otherwise, the return value is 0.

§ getOutputParam()

IlBoolean IliSQLCursor::getOutputParam	(	IlInt	paramno,
		IliValue &	val
	)		const

Retrieves the value of the output parameter.

Parameters

paramno	The parameter position returned by one of the `defineParam()` or `getParamIndex()` member functions.
val	The value.

Returns: IlTrue if successful.

§ getParamIndex()

IlInt IliSQLCursor::getParamIndex ( const char * paramName ) const

Returns the position of the defined parameter.

Parameters

paramName The parameter name, it should not include the colon prefix.

Returns: The position of the defined parameter or -1 if no such parameter is defined.

§ getParsedStatement()

const char* IliSQLCursor::getParsedStatement ( ) const

Returns the SQL statement that has been successfully parsed.

Returns: The SQL statement that has been successfully parsed, or 0 if no parsed statement is active.

§ getSchema()

const IliSchema* IliSQLCursor::getSchema ( ) const

Returns the schema of the current result set.

Returns: The schema of the current result set. If there is no result set currently active, the return value is undefined. Note that the object returned belongs to the cursor and that it may well be overwritten the next time a new result set is computed.

§ getSQLSession()

IliSQLSession& IliSQLCursor::getSQLSession ( ) const

Returns the IliSQLSession which the cursor belongs to.

Returns: The IliSQLSession which the cursor belongs to.

§ getStringValue()

const char* IliSQLCursor::getStringValue ( IlInt colno ) const

Returns a string value.

Parameters

colno The value position.

Returns: A character string if the value is a character string. Otherwise, it returns 0.

§ getValue()

IlBoolean IliSQLCursor::getValue	(	IlInt	colno,
		IliValue &	val
	)

Returns a value.

Parameters

colno	The value position.
val	The value.

Returns: IlTrue if successful.

§ hasTuple()

IlBoolean IliSQLCursor::hasTuple ( ) const

Indicates if the cursor is currently positioned on a valid row in the result set.

Returns: IlTrue if the cursor is currently positioned on a valid row in the result set. If this is the case, you can call any of the member functions described in the next section to inspect the values of the current row.

§ isNull()

IlBoolean IliSQLCursor::isNull ( IlInt colno ) const

Indicates if the value is null.

Parameters

colno The value position.

Returns: IlTrue if the value is null.

§ parse()

IlBoolean IliSQLCursor::parse	(	const char *	stmt,
		IlBoolean	useBoundVars = `IlTrue`
	)

Parses an SQL statement for future execution.

Once an SQL statement has been successfully parsed, it can be executed any number of times. The effects of parsing a statement will be lost if, later on, one of the parse(), select(const char*), or execute(const char*, IlInt*) member functions is called.

After calling this member function, you should call the defineParam() member function for each parameter that appears in the statement.

Parameters

stmt	The SQL statement.
useBoundVars	Reserved for future use and must always be set to `IlTrue`.

Returns: IlTrue if successful.

§ select() [1/2]

IlBoolean IliSQLCursor::select ( const char * stmt )

Executes a SQL SELECT statement.

The result set of the SQL SELECT execution statement can be inspected with the fetchNext() member function. Note that the result set will remain available until the next call to one of the execute(), select() or parse() member functions.

Parameters

stmt	The SQL statement.

Returns: IlTrue if successful.

§ select() [2/2]

IlBoolean IliSQLCursor::select ( )

Executes a parsed SQL SELECT statement.

The result set of the SQL SELECT statement execution can be inspected by the fetchNext() member function. Note that the result set will remain available until the next call to one of the execute(), select(), or parse() member functions.

This member function can be called any number of times as long as the SQL SELECT statement remains parsed.

Returns: IlTrue if successful.

§ setBufferedRowsCount()

IlBoolean IliSQLCursor::setBufferedRowsCount ( IlInt count )

Sets the number of buffered rows.

Parameters

count The count.

§ setParam()

IlBoolean IliSQLCursor::setParam	(	IlInt	paramno,
		const IliValue &	val
	)

Sets the value of the parameter.

Parameters

paramno	The parameter position returned by one of the `defineParam()` or `getParamIndex()` member functions. It should not be confused with the position of the parameter in the SQL statement.
val	The value. The type of the value must be the same as the type that was specified when the `defineParam()` member function was called.

Returns: IlTrue if successful.

Public Member Functions

Friends

Additional Inherited Members

Detailed Description

Accessors

Data accessors

Method accessors

Member Function Documentation

§ defineParam()

§ errorRaised()

§ execute() [1/2]

§ execute() [2/2]

§ fetchNext()

§ getBinaryValue()

§ getBufferedRowsCount()

§ getDateValue()

§ getDoubleValue()

§ getErrorMessage()

§ getFloatValue()

§ getIntegerValue()

§ getOutputParam()

§ getParamIndex()

§ getParsedStatement()

§ getSchema()

§ getSQLSession()

§ getStringValue()

§ getValue()

§ hasTuple()

§ isNull()

§ parse()

§ select() [1/2]

§ select() [2/2]

§ setBufferedRowsCount()

§ setParam()