Rogue Wave Views
Data Access Package API Reference Guide
Product Documentation:

Rogue Wave Views
Documentation Home
List of all members | Public Member Functions | Friends
IliSQLCursor Class Reference

Database access class. More...

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

Inheritance diagram for IliSQLCursor:
IliRefCounted

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 IliSchemagetSchema () const
 Returns the schema of the current result set. More...
 
IliSQLSessiongetSQLSession () 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.

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

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
paramNameThe parameter name, it should not include the colon prefix.
typeThe 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
stmtAny SQL statement except the SQL SELECT statement.
pRowCountIf not 0, 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
pRowCountIf not 0, 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
colnoThe 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
colnoThe 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
colnoThe 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
colnoThe 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
colnoThe 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
paramnoThe parameter position returned by one of the defineParam() or getParamIndex() member functions.
valThe value.
Returns
IlTrue if successful.
IlInt IliSQLCursor::getParamIndex ( const char *  paramName) const

Returns the position of the defined parameter.

Parameters
paramNameThe 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 0 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
colnoThe value position.
Returns
A character string if the value is a character string. Otherwise, it returns 0.
IlBoolean IliSQLCursor::getValue ( IlInt  colno,
IliValue val 
)

Returns a value.

Parameters
colnoThe value position.
valThe 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
colnoThe 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
stmtThe SQL statement.
useBoundVarsReserved 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
stmtThe 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
countThe count.
IlBoolean IliSQLCursor::setParam ( IlInt  paramno,
const IliValue val 
)

Sets the value of the parameter.

Parameters
paramnoThe 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.
valThe 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.

© Copyright 2016, Rogue Wave Software, Inc. All Rights Reserved.
Rogue Wave is a registered trademark of Rogue Wave Software, Inc. in the United States and other countries. All other trademarks are the property of their respective owners.