bps::Query Class Reference

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

Public Types
enum	Location { BeforeFirstRow = -1 , AfterLastRow = -2 }
	Special cursor locations. More...

Public Member Functions
Number	at ()
void	bind (Mixed aValue)
	Bind a value to a SQL parameter. More...
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. More...
Boolean	first ()
	Retrieves the first record in the result, if available, and positions the query on the retrieved record. More...
Boolean	isActive ()
Boolean	isNull (Number aField)
	Check if a value within the current cursor row is NULL. More...
Boolean	isSelect ()
Boolean	isValid ()
Boolean	last ()
	Retrieves the last record in the result, if available, and positions the query on the retrieved record. More...
Boolean	next ()
	Retrieves the next record in the result, if available, and positions the query on the retrieved record. More...
Number	numRowsAffected ()
void	prepare (String aSql)
	Prepare a SQL statement. More...
Boolean	previous ()
	Retrieves the previous record in the result, if available, and positions the query on the retrieved record. More...
	Query (Datastore aDatastore)
	Constructor for a new Query. More...
Array	recordAsArray ()
	Get the record from the current cursor row. More...
Object	recordAsObject ()
	Get the record from the current cursor row as an object. More...
Boolean	seek (Number aIndex, Boolean aRelative)
	Retrieves the record at position index, if available, and positions the query on the retrieved record. More...
Number	size ()
	return The size of the result (number of rows returned). More...
String	sql ()
Mixed	value (Number aField)
	Get a field value from the current cursor row. More...
SqlValues	values ()
	Get the SQL values from the current cursor row. More...

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

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

Location

enum bps::Query::Location

Special cursor locations.

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

Constructor & Destructor Documentation

Query()

bps::Query::Query

(

Datastore

aDatastore

)

Constructor for a new Query.

Parameters

aDatastore

A datastore object in connected state.

Member Function Documentation

at()

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.

bind()

void bps::Query::bind

(

Mixed

aValue

)

Bind a value to a SQL parameter.

Parameters

aValue

The value to bind.

execute()

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");

first()

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.

isActive()

Boolean bps::Query::isActive

(

)

Returns

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

isNull()

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.

isSelect()

Boolean bps::Query::isSelect

(

)

Returns

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

isValid()

Boolean bps::Query::isValid

(

)

Returns

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

last()

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.

next()

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.

numRowsAffected()

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.

prepare()

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.

previous()

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.

recordAsArray()

Array bps::Query::recordAsArray

(

)

Get the record from the current cursor row.

Returns

Array holding the records values.

recordAsObject()

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.

seek()

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.

size()

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.

sql()

String bps::Query::sql

(

)

Returns

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

value()

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.

values()

SqlValues bps::Query::values

(

)

Get the SQL values from the current cursor row.

Returns

The current row as bps.SqlValues.

Property Documentation

forwardOnly

Boolean bps::Query::forwardOnly

readwrite

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.

---

The documentation for this class was generated from the following file:

• bps.Query.js