develop wherever and whenever you want...
Docs > SQLite Library Summary

SQLite Library Summary

Jasic provides a native interface to the popular SQLite database library. With SQLite, one can use powerful SQL based databases for utilities, business applications, offline storage, and more. Instead of offering new / unknown (or unproven) object based APIs, Jasic uses the same approach WebGL does to OpenGL and offers a procedural interface callable from JavaScript. This procedural interface is designed to match the C API as close as possible to make learning and using the API very straight forward. Addditional object oriented implementations can then be layered on top of this procedural API.

Example sql = require("sqlite3");

v = sql.libversion(); 
document.writeln(v);

db = sql.open("addressBook.db"); 
var s = "CREATE TABLE IF NOT EXISTS contacts"; 
s += " (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT"; 
s += ", age INTEGER)";

sql.exec(db, s);

sql.exec(db, "DELETE FROM contacts"); 
var changes = sql.changes(db); 
document.writeln(changes + " deleted");

sql.exec(db, 'INSERT INTO contacts (name,age) VALUES ("Shark", 26)'); 
sql.exec(db, 'INSERT INTO contacts (name,age) VALUES ("Cuda", 28)'); 
sql.exec(db, 'INSERT INTO contacts (name,age) VALUES ("Dolphin", 72)'); 
var id = sql.lastinsertrowid(db); 
document.writeln("Dolphin id is " + id);

t = sql.prepare(db, 'SELECT * FROM contacts'); 
var total = 0;

var result = sql.step(t); 
var count = sql.columncount(t); 
for(var c=0; c < count; c++) { 
type = sql.column
type(t,c); 
name = sql.column_name(t,c); 
document.writeln(name + ":" + type); 
}

while(result == sql.SQLITEROW) { 
total++; 
id = sql.column
int64(t,0); 
name = sql.columntext(t,1); 
age = sql.column
int(t,2); 
document.writeln(id + ":" + name + ":" + age); 
result = sql.step(t); 
}

sql.finalize(t); 
document.writeln(total + " rows");

sql.close(db);

object *require*("sqlite3") 
This is the main mechanism to obtain the SQLite object. All other methods are performed on the returned object. The object also has constants designed to match their SQLite counterparts.

SQLite Methods 
string libversion() 
Returns a string representing the current SQLite3 version.

object open(file) 
Open an existing database file or create a new database. The database is created relative to the local folder that the script runs from, Returns a database object that can be used in subsequent calls. Please note that if the database object is freed, the database will be automatically closed.

void close(database) 
Close database object. Optional since the database will automatically close when the last reference to the database object is removed. However, it is recommended that you manually close database when all database work is complete.

void exec(database, sql) 
Execute a SQL statement, typically, this is used for CREATE, INSERT, UPDATE, and DELETE SQL statements. If any errors are encountered, an exception is raised containing details about the error.

number changes(database) 
Returns the number of changed rows in an INSERT, UPDATE, or DELETE statement.

object prepare(database, sql) 
Preparea a SQL query and return a statement object representing the query. If any errors are encountered, they're raised as JavaScript exceptions. The returned statement must be finalized using the finalize call but will also be automatically finalized if the last reference to the statement object is released. No operations on a statement are valid until step is called (see below).

number step(statement) 
Execute the query statement and fetch the first row of the result. At this point, all meta data functions are also available (see below).

number column_count(statement) 
Return the number of columns in the returned row from a SQL statement (must be called after step).

number column_type(statement, column) 
Returns the type of a given column in the statement. The type constants are on the SQLite object.

string column_name(statement, column) 
Return the name of a given column in the returned result rows.

number column_int64(statement, column) 
Fetch the value of a column on the current row as an integer.

number column_int(statement, column) 
Fetch the value of a column on the current row as an integer. number column_double(statement, column) Fetch the value of a column on the current row as an double.

string column_text(statement, column) 
Fetch the value of a column on the current row as a string.

void finalize(statement) 
Close the statement and free all associated resources. This will also be performed automatically when the statment object is no longer referenced.