The query class is used to process SQL queries. More...

Public Types
enum	Location { BeforeFirstRow = -1, AfterLastRow = -2 }
	Special cursor locations. More...
enum	ParamType { In = 1, Out = 2, InOut = 3, Binary = 4 }
	Parameter types. More...
Public Member Functions
Number	at ()
void	bind (Mixed aValue, ParamType aParamType=In)
	Bind a value to a SQL parameter.
Mixed	boundValue (Number aField)
	Get an Out or InOut bound value from the last execute.
void	clear ()
	Clears the result set and releases any local or database ressources held by the query.
void	execute (Mixed aSqlOrParams=Null)
	Execute a query.
Boolean	first ()
	Retrieves the first record in the result, if available, and positions the query on the retrieved record.
Boolean	isActive ()
Boolean	isNull (Number aField)
	Check if a value within the current cursor row is NULL.
Boolean	isSelect ()
Boolean	isValid ()
Boolean	last ()
	Retrieves the last record in the result, if available, and positions the query on the retrieved record.
Boolean	next ()
	Retrieves the next record in the result, if available, and positions the query on the retrieved record.
Number	numRowsAffected ()
void	prepare (String aSql)
	Prepare a SQL statement.
Boolean	previous ()
	Retrieves the previous record in the result, if available, and positions the query on the retrieved record.
	Query (Datastore aDatastore)
	Constructor for a new Query.
Array	recordAsArray ()
	Get the record from the current cursor row.
Object	recordAsObject ()
	Get the record from the current cursor row as an object.
Boolean	seek (Number aIndex, Boolean aRelative)
	Retrieves the record at position index, if available, and positions the query on the retrieved record.
Number	size ()
	return The size of the result (number of rows returned).
String	sql ()
Mixed	value (Number aField)
	Get a field value from the current cursor row.
SqlValues	values ()
	Get the SQL values from the current cursor row.
Properties
Boolean	forwardOnly
	If forwardOnly is true, only next() and seek() with positive values are allowed for navigating the results.

Detailed Description

The query class is used to process SQL queries.

Example:

 importExtension("bps");
 with (bps) {
 
     log("connect to database");
     var ds = new bps.Datastore();
     with (ds) {
         connection  = "test";    // name of this connection
         username    = "test12";  // schema test; bps user id 12
         password    = "test44";  // schema test; pin 44
         connect();
     } // with ds
 
     log("query the table");
     var q = new bps.Query(ds);
 
     // uncomment next line to check out random mode:
     // q.forwardOnly = false;
 
     q.execute("select ident, value from mytable order by ident");
 
     log("found "+q.size()+" rows:");
     while (q.next())
         log(q.value(0)+" - "+q.value(1));
 
     if (!q.forwardOnly) {
         // NOTE: the navigations below will not work in forwardOnly mode
         log("now list result set backward");
         while (q.previous()) bps.log(q.value(0)+" - "+q.value(1));
 
         log("display middle row");
         if (q.seek(1)) bps.log(q.value(0)+" - "+q.value(1));
     } // if
 
     log("disconnect from database");
     ds.disconnect();
 
 } // with bps

Member Enumeration Documentation

enum bps::Query::Location

Special cursor locations.

Enumerator:

BeforeFirstRow	Cursor is before first row or invalid.
AfterLastRow	Cursor is after last row.

enum bps::Query::ParamType

Parameter types.

Enumerator:

In	Input parameter.
Out	Output parameter.
InOut	Input and output parameter.
Binary	Add this to In, Out or InOut for raw binary data.

Constructor & Destructor Documentation

bps::Query::Query ( Datastore aDatastore )

Constructor for a new Query.

Parameters:

aDatastore A datastore object in connected state.

Member Function Documentation

Number bps::Query::at ( )

Returns:: The current internal position of the query.

Note:: The first record is at position zero.; If the position is invalid, the value is BeforeFirstRow or AfterLastRow, which are special negative values.

void bps::Query::bind	(	Mixed	aValue,
		ParamType	aParamType = `In`
	)

Bind a value to a SQL parameter.

Parameters:

aValue	The value to bind.
aParamType	Optional parameter type of `In`, `Out` or `InOut`. In case of binary data add Binary. Default parameter type is In.

Mixed bps::Query::boundValue ( Number aField )

Get an Out or InOut bound value from the last execute.

Parameters:

aField The index of the bound field.

Returns:: The bound value of the field.

 // Note: bound value indexes start at 0
 q.prepare("call myprocedure(?, ?, ?)");
 q.bind(3);                   // par 0 is In
 q.bind(5, bps.Query.InOut);  // par 1 is InOut
 q.bind(0, bps.Query.Out);    // par 2 is Out (value 0 is just a dummy)
 q.execute();
 print("par 1 = "+q.boundValue(1));
 print("par 2 = "+q.boundValue(2));

void bps::Query::execute ( Mixed aSqlOrParams = Null )

Execute a query.

Parameters:

aSqlOrParams If the parameter is an Array, it is taken as a number of bind values where type In is assumed. If the parameter is a String, it is taken as SQL statement. No former prepare/bind is needed in this case.

 // Example 1: no parameter
 q.prepare("insert into foo (bar, doo) values (?, ?)");
 q.bind(1); q.bind('ball'); q.execute();
 q.bine(2); q.bind('bike'); q.execute();

 // Example 2: parameter = bind values
 q.prepare("insert into foo (bar, doo) values (?, ?)");
 q.execute([1, 'ball']);
 q.execute([2, 'bike']);

 // Example 3: parameter = sql statement
 q.execute("delete from foo where bar = 2");

Boolean bps::Query::first ( )

Retrieves the first record in the result, if available, and positions the query on the retrieved record.

Returns:: True if successful. If unsuccessful the query position is set to an invalid position and false is returned.

Note:: The result must be in an active state and select must be true before calling this function or it will do nothing and return false.

Boolean bps::Query::isActive ( )

Returns:: True if query is active (has an open cursor), false if not.

Boolean bps::Query::isNull ( Number aField )

Check if a value within the current cursor row is NULL.

Parameters:

aField The column index of the field.

Returns:: True if the value is NULL, false otherwise.

Boolean bps::Query::isSelect ( )

Returns:: True if the executed query is a select statement, false if not.

Boolean bps::Query::isValid ( )

Returns:: True if the query cursor of a select is currently at a valid position, else false.

Boolean bps::Query::last ( )

Retrieves the last record in the result, if available, and positions the query on the retrieved record.

Returns:: True if record successfully retrieved, false otherwise.

Note:: The result must be in an active state and select must be true before calling this function or it will do nothing and return false. If unsuccessful the query position is set to an invalid position.

Boolean bps::Query::next ( )

Retrieves the next record in the result, if available, and positions the query on the retrieved record.

The following rules apply:

If the result is currently located before the first record, e.g. immediately after a query is executed, an attempt is made to retrieve the first record.
If the result is currently located after the last record, there is no change and false is returned.
If the result is located somewhere in the middle, an attempt is made to retrieve the next record.
If the record could not be retrieved, the result is positioned after the last record and false is returned.

Returns:: True if the record is successfully retrieved, false otherwise.

Note:: The result must be in an active state and select must be true before calling this function or it will do nothing and return false.

Number bps::Query::numRowsAffected ( )

Returns:: The number of rows affected by the result's SQL statement, or -1 if it cannot be determined.

Note:: For SELECT statements, the value is undefined; use size() instead.; If the query is not active (active is false), -1 is returned.

void bps::Query::prepare ( String aSql )

Prepare a SQL statement.

This is normally used in conjunction with binding. If no binding is used, the SQL statement can be directly supplied to execute() instead of calling prepare.

Parameters:

aSql	The SQL statement to prepare.

Boolean bps::Query::previous ( )

Retrieves the previous record in the result, if available, and positions the query on the retrieved record.

The following rules apply:

If the result is currently located before the first record, there is no change and false is returned.
If the result is currently located after the last record, an attempt is made to retrieve the last record.
If the result is somewhere in the middle, an attempt is made to retrieve the previous record.
If the record could not be retrieved, the result is positioned before the first record and false is returned. If the record is successfully retrieved, true is returned.

Returns:: True if record successfully retrieved, false otherwise.

Note:: The result must be in an active state and select must be true before calling this function or it will do nothing and return false.

Array bps::Query::recordAsArray ( )

Get the record from the current cursor row.

Returns:: Array holding the records values.

Object bps::Query::recordAsObject ( )

Get the record from the current cursor row as an object.

The property names will be all lowercase, with the table prefixes removed. So if the query contains two columns with same name such as a.c_id and t.c_id, the column names will be considered ambiguous because both will result in the same property name c_id. For such cases use the "AS" operator in the query to force unique names.

Returns:: Object representing the record.

Boolean bps::Query::seek	(	Number	aIndex,
		Boolean	aRelative
	)

Retrieves the record at position index, if available, and positions the query on the retrieved record.

The first record is at position 0. Note that the query must be in an active state and select must be true before calling this function.

If relative is false (the default), the following rules apply:

If index is negative, the result is positioned before the first record and false is returned.
Otherwise, an attempt is made to move to the record at position index.
If the record at position index could not be retrieved, the result is positioned after the last record and false is returned.

If relative is true, the following rules apply:

If the result is currently positioned before the first record or on the first record, and index is negative, there is no change, and false is returned.
If the result is currently located after the last record, and index is positive, there is no change, and false is returned.
If the result is currently located somewhere in the middle, and the relative offset index moves the result below zero, the result is positioned before the first record and false is returned.
Otherwise, an attempt is made to move to the record index records ahead of the current record (or index records behind the current record if index is negative).
If the record at offset index could not be retrieved, the result is positioned after the last record if index >= 0, (or before the first record if index is negative), and false is returned.

Parameters:

aIndex	The absolute or relative index.
aRelative	True for relative mode, false for absolute mode.

Returns:: True if record successfully retrieved, false otherwise.

Number bps::Query::size ( )

return The size of the result (number of rows returned).

Note:: If active or select is false, size will be -1.; To determine the number of rows affected by a non-SELECT statement, use numRowsAffected.

String bps::Query::sql ( )

Returns:: The SQL of the last query, or an empty string if there is no last query.

Mixed bps::Query::value ( Number aField )

Get a field value from the current cursor row.

Parameters:

aField The column index of the field.

Returns:: The value of the field.

SqlValues bps::Query::values ( )

Get the SQL values from the current cursor row.

Returns:: The current row as bps.SqlValues.

Property Documentation

Boolean bps::Query::forwardOnly [read, write]

If forwardOnly is true, only next() and seek() with positive values are allowed for navigating the results.

Forward only mode needs far less memory since results do not need to be cached. ForwardOnly is enabled by default; disable it before executing a random access query in case.

Public Types

Public Member Functions

Properties

Detailed Description

Member Enumeration Documentation

Constructor & Destructor Documentation

Member Function Documentation

Property Documentation