SQLite
SQLite API provides simple methods for query executions with parameters binding support.
Usage
// Call the openDatabase() method to get started, passing the name for the database.
// This object will be used for all next database transactions.
var db = SQLite.openDatabase({ name: 'appdata.db' }, function (db) {
console.log("")
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE persons (firstname VARCHAR(32), lastname VARCHAR(32))');
}, function (error) {
console.log('transaction error: ' + error.message);
}, function () {
console.log('transaction ok');
});
}, function (error) {
console.log('Open database ERROR: ' + JSON.stringify(error));
});
...
//Add a row to the database using the INSERT INTO SQL statement
function addItem(first, last) {
db.transaction(function (tx) {
var query = "INSERT INTO persons (firstname, lastname) VALUES (?,?)";
tx.executeSql(query, [first, last], function(tx, res) {
console.log("insertId: " + res.insertId );
console.log("rowsAffected: " + res.rowsAffected );
},
function(tx, error) {
console.log('INSERT error: ' + error.message);
});
}, function(error) {
console.log('transaction error: ' + error.message);
}, function() {
console.log('transaction ok');
});
}
...
addItem("Peter","Smith");
addItem("Anne","Frank");
...
//Function to read from the database using a SELECT statement
function getData() {
db.transaction(function (tx) {
var query = "SELECT firstname, lastname FROM persons";
tx.executeSql(query, [], function (tx, resultSet) {
for(var x = 0; x < resultSet.rows.length; x++) {
console.log("First name: " + resultSet.rows.item(x).firstname +
", Last name: " + resultSet.rows.item(x).lastname);
}
},
function (tx, error) {
console.log('SELECT error: ' + error.message);
});
}, function (error) {
console.log('transaction error: ' + error.message);
}, function () {
console.log('transaction ok');
});
}
...
getData();// Call the openDatabase() method to get started, passing the name for the database.
// This object will be used for all next database transactions.
setDb(window.SQLite.openDatabase(
{ name: "appdata.db" },
function (db) {
console.log("");
db.transaction(
function (tx) {
tx.executeSql(
"CREATE TABLE persons (firstname VARCHAR(32), lastname VARCHAR(32))"
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
},
function (error) {
console.log("Open database ERROR: " + JSON.stringify(error));
}
));
...
let first = "nani",
last = "gotcha";
db.transaction(
function (tx) {
var query = "INSERT INTO persons (firstname, lastname) VALUES (?,?)";
tx.executeSql(
query,
[first, last],
function (tx, res) {
console.log("insertId: " + res.insertId);
console.log("rowsAffected: " + res.rowsAffected);
},
function (tx, error) {
console.log("INSERT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
...
db.transaction(
function (tx) {
var query = "SELECT firstname, lastname FROM persons";
tx.executeSql(
query,
[],
function (tx, resultSet) {
for (var x = 0; x < resultSet.rows.length; x++) {
console.log(
"First name: " +
resultSet.rows.item(x).firstname +
", Last name: " +
resultSet.rows.item(x).lastname
);
}
},
function (tx, error) {
console.log("SELECT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);// Call the openDatabase() method to get started, passing the name for the database.
// This object will be used for all next database transactions.
this.db = window.SQLite.openDatabase(
{ name: "appdata.db" },
function (db) {
console.log("");
db.transaction(
function (tx) {
tx.executeSql(
"CREATE TABLE persons (firstname VARCHAR(32), lastname VARCHAR(32))"
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
},
function (error) {
console.log("Open database ERROR: " + JSON.stringify(error));
}
);
...
//Add a row to the database using the INSERT INTO SQL statement
let first = "nani",
last = "gotcha";
this.db.transaction(
function (tx) {
var query = "INSERT INTO persons (firstname, lastname) VALUES (?,?)";
tx.executeSql(
query,
[first, last],
function (tx, res) {
console.log("insertId: " + res.insertId);
console.log("rowsAffected: " + res.rowsAffected);
},
function (tx, error) {
console.log("INSERT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
...
//Function to read from the database using a SELECT statement
this.db.transaction(
function (tx) {
var query = "SELECT firstname, lastname FROM persons";
tx.executeSql(
query,
[],
function (tx, resultSet) {
for (var x = 0; x < resultSet.rows.length; x++) {
console.log(
"First name: " +
resultSet.rows.item(x).firstname +
", Last name: " +
resultSet.rows.item(x).lastname
);
}
},
function (tx, error) {
console.log("SELECT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);// Call the openDatabase() method to get started, passing the name for the database.
// This object will be used for all next database transactions.
this.db = (<any>window).SQLite.openDatabase(
{ name: "appdata.db" },
function (db) {
console.log("");
db.transaction(
function (tx) {
tx.executeSql(
"CREATE TABLE persons (firstname VARCHAR(32), lastname VARCHAR(32))"
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
},
function (error) {
console.log("Open database ERROR: " + JSON.stringify(error));
}
);
...
// Add a row to the database using the INSERT INTO SQL statement
let first = "nani",
last = "gotcha";
this.db.transaction(
function (tx) {
var query = "INSERT INTO persons (firstname, lastname) VALUES (?,?)";
tx.executeSql(
query,
[first, last],
function (tx, res) {
console.log("insertId: " + res.insertId);
console.log("rowsAffected: " + res.rowsAffected);
},
function (tx, error) {
console.log("INSERT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
...
// Function to read from the database using a SELECT statement
this.db.transaction(
function (tx) {
var query = "SELECT firstname, lastname FROM persons";
tx.executeSql(
query,
[],
function (tx, resultSet) {
for (var x = 0; x < resultSet.rows.length; x++) {
console.log(
"First name: " +
resultSet.rows.item(x).firstname +
", Last name: " +
resultSet.rows.item(x).lastname
);
}
},
function (tx, error) {
onsole.log("SELECT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);// Call the openDatabase() method to get started, passing the name for the database.
// This object will be used for all next database transactions.
db = window.SQLite.openDatabase(
{ name: "appdata.db" },
function (db) {
console.log("");
db.transaction(
function (tx) {
tx.executeSql(
"CREATE TABLE persons (firstname VARCHAR(32), lastname VARCHAR(32))"
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
},
function (error) {
console.log("Open database ERROR: " + JSON.stringify(error));
}
);
...
//Add a row to the database using the INSERT INTO SQL statement
let first = "nani",
last = "gotcha";
db.transaction(
function (tx) {
var query = "INSERT INTO persons (firstname, lastname) VALUES (?,?)";
tx.executeSql(
query,
[first, last],
function (tx, res) {
console.log("insertId: " + res.insertId);
console.log("rowsAffected: " + res.rowsAffected);
},
function (tx, error) {
console.log("INSERT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);
...
//Function to read from the database using a SELECT statement
db.transaction(
function (tx) {
var query = "SELECT firstname, lastname FROM persons";
tx.executeSql(
query,
[],
function (tx, resultSet) {
for (var x = 0; x < resultSet.rows.length; x++) {
console.log(
"First name: " +
resultSet.rows.item(x).firstname +
", Last name: " +
resultSet.rows.item(x).lastname
);
}
},
function (tx, error) {
console.log("SELECT error: " + error.message);
}
);
},
function (error) {
console.log("transaction error: " + error.message);
},
function () {
console.log("transaction ok");
}
);Table of Contents
Classes
SQLite
close
close(): Promise <any>
Close a database instance, freeing up any resources and memory allocated to it.
create
create(option: CreateOptions|string): Promise<SQLiteObject>
Create an instance of the SQLite database with the specified name and return a Promise wich resolves a SQLiteObject.
openDatabase
openDatabase(option: OpenOptions|string): SQLiteObject
Open and return a SQLite databse instance.
transaction
transaction(executeTransaction: function(t: SQLiteTransaction)): void
Starts a new transaction.
PARAMETERS
executeTransaction: function(t: SQLiteTransaction) Transaction execution function for an SQLiteTransaction object.
RETURN
returns: void
SQLiteTransaction
The objects of SQLiteTransaction class are used for executing SQL queries by the SQLite.transaction() method.
executeSql
executeSql(query: string, values: [any], resultCallback: function(t: SQLiteTransaction, r: ResultSet)): void
Executes an statement by sending the SQL query to the database. The results will be processed by the resultCallback function.
The statement may be a query returning result set, or a non-query statement returning an update count. Non-query SQL can be insert, update, or delete statements. Non-query SQL statements can also be DML statements (such as create, alter, ...).
PARAMETERS
query: string values: [any] An array of values to be bound to the query variables resultCallback: function(t: SQLiteTransaction, r: ResultSet)
RETURN
returns: void
Interfaces Used
CreateOptions
interface CreateOptions|OpenOptions {
// The database name
name: string;
}
ResultSet
interface ResultSet {
// A collection of row objects that represents the rows returned by the query
rows: {
// Returns an individual row object in the Rows collection
item: function(rowNumber),
// Total rows returned by the query
lenght: int
},
// The number of rows affected
rowsAffected: int,
// The autoincrement value by an INSERT SQL query
insertId: int
}