IliSQLCursor Class Reference

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
	Returns IlTrue 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
	Returns IlTrue if the cursor is currently positioned on a valid row in the result set. More...
IlBoolean	isNull (IlInt colno) const
	Returns IlTrue 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.

Member Function Documentation

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 null.

Parameters

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

Returns

The position of the newly defined parameter.

IlBoolean IliSQLCursor::errorRaised

(

)

const

Returns IlTrue 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 the select, execute, parse, or fetchNext member functions failed. In this case, an error message is available through the getErrorMessage member function.

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 NULL, it will receive the number of rows affected by the execution of the statement, when applicable.

Returns

IlTrue if successful.

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 NULL, it will receive the number of rows affected by the execution of the statement, when applicable.

Returns

IlTrue if successful.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

const char* IliSQLCursor::getParsedStatement

(

)

const

Returns the SQL statement that has been successfully parsed.

Returns

The SQL statement that has been successfully parsed. Returns NULL if no parsed statement is active.

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.

IliSQLSession & IliSQLCursor::getSQLSession

(

)

const

Returns the IliSQLSession which the cursor belongs to.

Returns

The IliSQLSession which the cursor belongs to.

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 NULL.

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

Returns a value.

Parameters

colno	The value position.
val	The value.

Returns

IlTrue if successful.

IlBoolean IliSQLCursor::hasTuple

(

)

const

Returns IlTrue 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.

IlBoolean IliSQLCursor::isNull

(

IlInt

colno

)

const

Returns IlTrue if the value is null.

Parameters

colno

The value position.

Returns

IlTrue if the value is null.

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.

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.

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.

IlBoolean IliSQLCursor::setBufferedRowsCount

(

IlInt

count

)

Sets the number of buffered rows.

Parameters

count

The count.

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.