Sql class

From PhiWiki
Jump to: navigation, search

The sql class contains functions and data related for access databases.

Overview

Properties
String lasterror
Boolean next
Number numrowsaffected
StringArray properties
Number rows
StringArray tables
VariantArray values
Functions
.bind() v2.0.1 Binds a value to a placeholder
.clone() v1.3.0 Clones the sql object
.commit() Commits a transaction
.exec() v2.0.1 Executes a prepared query
.prepare() v2.0.1 Prepares a SQL query
.query() Executes a SQL statement
.rollback() Rolls a transaction back
.transaction() Begins a transaction
.value() Returns a value of a result set

Usage

The sql class is a global object in the Serverscript.pngServerScript environment and uses the database settings stored along with the page.

loadModule('sql');

var id=3;
sql.prepare( "SELECT firstname, lastname FROM persons WHERE id=?" );
sql.bind( 0, id );
sql.exec();
while ( sql.next ) {
  var firstname=sql.value( 0 );
  var lastname=sql.value( 1 );
  dosomething( firstname, lastname );
}

Properties

  • String lasterror [read]
  • Boolean next [read]
  • Number numrowsaffected [read]
  • StringArray properties [read]
  • Number rows [read]
  • StringArray tables [read]
  • VariantArray values [read]

String lasterror

Returns the last error occured or an empty string if there was no error.

var error=sql.lasterror;

Boolean next

Sets the pointer to the next record. Returns true if the next record is valid or false if the record is invalid. You need always to call sql.next to point to the first valid record, even if the result of the query is a single row.

sql.query( 'SELECT name, street FROM person WHERE id=2' );
if ( sql.next ) {
  var street=sql.value( 'street' );
}

See also: .value().

Number numrowsaffected

Returns the number of affected rows of the last sql query.

var rows=sql.numrowsaffected;

StringArray properties

Returns the available properties for the sql class in a string array.

var i=0;
while ( sql.properties[i] ) print( sql.properties[i++] );

Number rows

Returns the number of the records the last query caused.

var num=sql.rows;

StringArray tables

Returns the table names of the current used database.

var tables=sql.tables;
var i=0;
while ( tables[i] ) print( tables[i++] );

VariantArray values

Returns all values of the current record in an array.

var values=sql.values;
var i=0;
while ( values[i] ) doSomething( values[i++] );

Functions

.bind(), .clone(), .commit(), .exec(), .prepare(), .query(), .rollback(), .transaction(), .value()

.bind()


Synopsis

.bind(num|placeholder,val)
Returns (read): n/a
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v2.0.1
Brief description: Binds a value to a placeholder.
Attribute Type Default Note
num Number Placeholder position in the query.
placeholder String Placeholder name in the query.
val Variant Value as string, array or number.

Overview

Description

Binds a value to a placeholder in a query string (i.e. replaces : or ? with the value). If you want to set a NULL value use undefined. If you pass an array as val only the first element is used.

Read: This is a write-only function.

Write: Binds the value val at position num or name placeholder. Positions are starting with 0.

Note: Binding values to a prepared query string is more secure because of nested SQL queries will not be executed. Using named placeholders is usually slower.

See also: .prepare(), .exec(), .query().

Example

var id=7;
sql.prepare( "INSERT INTO person (id,name,street,email) VALUES( ?, ?, ?, ? )" );
sql.bind( 0, id );
sql.bind( 1, 'Tina' );
sql.bind( 2, undefined ); // NULL value
sql.bind( 3, 'tina@phisketeer.org' );
if ( !sql.exec() ) print( sql.lasterror );
sql.prepare( "INSERT INTO person (id,name,street,email) VALUES( :id, :name, :street, :email )" );
sql.bind( ':id', 3 );
sql.bind( ':name', 'Tina' );
sql.bind( ':street', undefined ); // NULL value
sql.bind( ':email', 'tina@phisketeer.org' );
if ( !sql.exec() ) print( sql.lasterror );

.clone()


Synopsis

.clone()
Returns (read): Object
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v1.3.0
Brief description: Clones current connection.
Attribute Type Default Note

Overview

Description

Clones the current SQL connection. The current result set is not affected and not copied.

Read: Clones the connection and returns a new sql object.

Write: This is a read-only function.

Note:

See also: .query().

Example

loadModule('sql');
var sql2=sql.clone();
sql2.query( "USE newdb" );

.commit()


Synopsis

.commit()
Returns (read): Boolean
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v1.0.0
Brief description: Commits a transaction.
Attribute Type Default Note

Overview

Description

Commits a database transaction initiated with sql.transaction().

Read: This is a write-only function.

Write: Commits the transaction and returns true on success otherwise false.

Note: If your DBMS does not support transactions this function is silently ignored.

See also: .transaction().

Example

sql.transaction();
sql.query( "..." );
sql.query( "..." );
sql.commit();

.exec()


Synopsis

.exec()
Returns (read): Boolean
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v2.0.1
Brief description: Executes a prepared SQL query string.
Attribute Type Default Note

Overview

Description

Read: Executes a prepared query string and returns true on success or false otherwise.

Write: This is a read-only function.

Note:

See also: .prepare(), .bind(), .query().

Example

var id=7;
var age=21;
sql.prepare( "UPDATE person SET age=? WHERE id=?" );
sql.bind( 0, age );
sql.bind( 1, id );
if ( !sql.exec() ) print( sql.lasterror );

.prepare()


Synopsis

.prepare(qs)
Returns (read): Boolean
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v2.0.1
Brief description: Prepares a SQL query.
Attribute Type Default Note
qs String Query string.

Overview

Description

Prepares a SQL statement using query qs. You can use : or ? as placeholders, but you can not mix them in the same query.

Read: This is a write-only function.

Write: Creates the SQL statement qs and returns true on success otherwise false.

Note: Preparing a SQL query is more secure as using .query() directly because of nested SQL queries are not executed.

See also: .bind(), .exec(), .query().

Example

var id=7;
sql.prepare( "SELECT name,street,email FROM address WHERE id=?" );
sql.bind( 0, id );
sql.exec();
if ( sql.next ) { //point to the first record
   doSomething();
}

.query()


Synopsis

.query(qs)
Returns (read): Boolean
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v1.0.0
Brief description: Queries the database.
Attribute Type Default Note
qs String Query string.

Overview

Description

Queries the database using query qs and stores the result in a result set.

Read: This is a write-only function.

Write: Executes the SQL statement qs and returns true on success otherwise false.

Note: You should prefer .prepare() to prevent nested SQL statements.

See also: .bind(), .prepare(), .exec().

Example

var id=5;
sql.query( "SELECT name,street,email FROM address WHERE id="+id );
if ( sql.next ) { //point to the first record
   doSomething();
}

.rollback()


Synopsis

.rollback()
Returns (read): Boolean
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v1.0.0
Brief description: Rollback a transaction.
Attribute Type Default Note

Overview

Description

Rolls a database transaction back.

Read: This is a write-only function.

Write: Rollback the transaction and returns true on success otherwise false.

Note: If your DBMS does not support transactions this function is silently ignored.

See also: .transaction(), .commit().

Example

sql.transaction();
sql.query( "..." );
sql.query( "..." );
if ( !sql.commit() ) sql.rollback();

.transaction()


Synopsis

.transaction()
Returns (read): Boolean
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v1.0.0
Brief description: Starts a transaction.
Attribute Type Default Note

Overview

Description

Starts a database transaction.

Read: This is a write-only function.

Write: Starts the transaction and returns true on success otherwise false.

Note: If your DBMS does not support transactions this function is silently ignored.

See also: .commit(), .rollback().

Example

sql.transaction();
sql.query( "..." );
sql.query( "..." );
if ( !sql.commit() ) callErrorRoutine();

.value()


Synopsis

.value(pos|field)
Returns (read): Variant
Environment: ServerScriptOk.png JavaScriptNa.png
Available since: v1.0.0
Brief description: Returns a result value.
Attribute Type Default Note
pos Number Position in result set.
field String v2.0.0 field name.

Overview

Description

Returns a specific value from a result set (row). Positions start at 0. Using field names is usually slower.

Read: Returns the value from a SELECT statement at position pos or field name field.

Write: This is a read-only function.

Note: The value is returned as a variant. This means you may assign it to special values e.g. a date class.

See also: .bind(), .prepare(), .query(), sql.values, system.toFormatedString(), system.toLocaleString().

Example

var id=5;
sql.query( "SELECT name,street,email,NOW() AS curdate FROM address WHERE id="+id );
if ( sql.next ) { //point to the first record
  var name=sql.value( 0 ); // value for name
  var street=sql.value( 'street' );
  var email=sql.value( 2 );
  var d=new Date();
  d=sql.value( 'curdate' );
  var year=d.getFullYear();
} else {
  // empty result set
}

Additional notes

Currently it is not possible to use different databases at the same time in one page. This may change in the future. However you can .clone() the current connection.