constructor( [fileName] [, flags] )
Creates a new Database object.
arguments:

_string fileName: is the file name of the database, or an empty string for a temporary database.
If omitted or undefined, an in-memory database is created.
_enum flags: can be one of the following constant:
READONLY
READWRITE
CREATE
DELETEONCLOSE
EXCLUSIVE
MAIN_DB
TEMP_DB
TRANSIENT_DB
MAIN_JOURNAL
TEMP_JOURNAL
SUBJOURNAL
MASTER_JOURNAL
example:

var db = new Database(); db.Exec('create table t1 (a,b,c);');

Methods

Close

_void Close()
Close the database and all its opened Result objects.
note:

It is recommended to close all Result objects before closing the database.

Query

_Result Query( sqlStr [, map ] )
Evaluates a SQL string and returns a Result object ready to be executed.
arguments:

_string sqlStr:
_Object map: map is bind to the SQL statement and can be access using '@' char ( see. Exec ). If you create new properties on the Result object, you can access then in the sqlStr using ':' char. '?' allows you access the map as an array ( see examples ).
example 1:

var res = db.Query('SELECT :test1 + 5'); res.test1 = 123; Print( res.Row().toSource() ); // Prints: [128]
example 2:

var res = db.Query('SELECT @test2 + 5', {test2:6}); Print( res.Row().toSource() ); // Prints: [11]
example 3:

var res = db.Query('SELECT ? + ?', [ 4, 5 ]); Print( res.Row().toSource() ); // Prints: [9]
return value:

A new Result object.
beware:

There are some limitation in variable bindings. For example, they cannot be used to specify a table name. db.Query('SELECT * FROM ?', ['myTable']); will failed with this exception: SQLite error 1: near "?": syntax error
example 1:

var result = db.Query('SELECT name FROM table WHERE id=:userId' ); result.userId = 1341; Print( result.Col(0) );
example 2:

var result = db.Query('SELECT name FROM table WHERE id=@userId', { userId:1341 } ); Print( result.Col(0) );
example 3:

var result = db.Query('SELECT ? FROM table WHERE id=?', ['name', 1341] ); // array-like objects {0:'name', 1:1341, length:2} works too. Print( result.Col(0) );

Exec

_value Exec( sqlStr [, map ] )
Evaluates a SQL string and return the result in one operation.
arguments:

_string sqlStr: is the SQL statement.
_Object map: if given, this argument is bind (as a key:value variable map) to the SQL statement.
example:

db.Exec('PRAGMA user_version = @ver', { ver:5 } );
return value:

returns the first line and first column of the result.
details:

sqlite documentation
example:

var version = db.Exec('PRAGMA user_version'); db.Exec('PRAGMA user_version = 5');

Properties

lastInsertRowid

lastInsertRowid
is the rowid of the most recent successful INSERT into the database from the database connection shown in the first argument. If no successful inserts have ever occurred on this database connection, zero is returned.
details:

sqlite documentation

changes

changes
is the number of database rows that were changed or inserted or deleted by the most recently completed SQL statement on the connection specified by the first parameter. Only changes that are directly specified by the INSERT, UPDATE, or DELETE statement are counted.
details:

sqlite documentation

Static Properties

version

version
Hold the current version of the database engine.

memoryUsed

memoryUsed
Is the amount of memory currently checked out.

Remarks

Add SQL functions implemented in JavaScript.

Any function properties stored to a Database object can be used in the SQL string.
example:

var db = new Database('myDatabase'); db.multBy10 = function(a) { return a * 10 } Print( db.Exec('SELECT multBy10(123)') ); // prints: 1230

Note

jslibs Blob object is interpreted as a blob database type.

Examples

example 1:

 Print('database version: ' + Database.version ,'\n' );

 var obj = { foo:Blob('qqwe\00\00fv1234') };
 Print( 'testFunc = ' + db.Exec('SELECT length(:foo)', obj  ) ,'\n' );

example 2:

 LoadModule('jsstd');
 LoadModule('jssqlite');

 try {

  var db = new Database();
  db.Exec('create table t1 (a,b,c);');
  db.Exec('insert into t1 (a,b,c) values (5,6,7)');
  db.Exec('insert into t1 (a,b,c) values (2,3,4)');
  db.Exec('insert into t1 (a,b,c) values ("a","b","c")');

  var res = db.Query('SELECT a,c from t1');

  Print( res.Row().toSource(), '\n' );
  Print( res.Row().toSource(), '\n' );
  Print( res.Row().toSource(), '\n' );

  } catch ( ex if ex instanceof SqliteError ) {

   Print( 'SQLite error '+ex.code+': '+ex.text+'\n' );
  }

class jssqlite::Result

- top - revision -

A Result object is used to store a compiled SQL statement ready to be executed.

When a statement has been prepared with Database.Query function, you need to execute it ( with Step function ) before any data can be read. However, some properties (like columnCount, ... ) can be read before the first Step has been done.

A result has the ability to be iterated through a for each..in loop (for..in loop is note supported).
example 1:

var db = new Database(); // in-memory database db.Exec('create table t1 (name,value);'); db.Exec('insert into t1 (name,value) values ("red","#F00")'); db.Exec('insert into t1 (name,value) values ("green","#0F0")'); db.Exec('insert into t1 (name,value) values ("blue","#00F")'); for each ( row in db.Query('SELECT * from t1') ) Print( row.name + ' = ' + row.value, '\n' );
prints:
red = #F00 green = #0F0 blue = #00F

example 2:

  Print( [ color.name for each ( color in db.Query('SELECT * from t1') ) ] ); // prints: red,green,blue

note:

You cannot construct this class.

Methods

Close

_void Close()
Close the current Result object.

Step

_boolean Step()
Executes one step in the previously evaluated SQL statement.
return value:

returns true if another row is ready. false if the last line has been reached.

Col

_value Col( colIndex )
Returns the current value of the colIndex ^th column.
arguments:

_integer colIndex

Row

_value Row( [namedRows = false] )
Executes one step of the the current SQL statement and returns the resulting row of data.
arguments:

_boolean namedRows: if true, the function returns an objet containing {columnName:value} pair. else it returns an array of value.
note:

The Step function is internally called before each Row call.

Reset

_void Reset()
Resets the current Result object to its initial state.

Properties

columnCount

_integer columnCount
Hold the number of columns of the current Result

columnNames

_Array columnNames
Hold an _Array that contain the index:name of the columns.
example:

var db = new Database(); db.Exec('create table t1 (a,b,c);'); var res = db.Query('SELECT a,c from t1'); Print( res.columnNames.toSource(), '\n' ); // prints: ["a", "c"]

columnIndexes

_Object columnIndexes
Hold an _Object that contain the name:index of the columns.
example:

var db = new Database(); db.Exec('create table t1 (a,b,c);'); var res = db.Query('SELECT a,c from t1'); Print( res.columnIndexes.toSource(), '\n' ); // prints: ({a:0, c:1})

expired

_boolean expired
Indicates if the SQL statement must be re-evaluated.

class jssqlite::SqliteError

- top -

Its aim is to be throw as an exception on any SQLite runtime error.
note:

You cannot construct this class.

Properties

code

_integer code

text

_string text

Exemple

try {

  db.Exec('yfiqwygqiwye'); // generate an error

} catch ( ex if ex instanceof SqliteError ) {

   Print( 'SqliteError: ' + ex.text + '('+ex.code+')', '\n' );
} catch( ex ) {

   throw ex;
}

- top - main -