SQL Database in Apache Cordova/PhoneGap

Introduction

In Apache Cordova/PhoneGap their are various database objects. In this article I will explain the SQL objects. Some of them are SQLTransaction, SQLResultSet, SQLResultSetList and SQLError; they are described in this article.

SQLTransaction: It contains the methods that allow the user to execute SQL statements against the Database. The method we use to execute the SQL statement is executeSql. The user can create a database transaction by calling the executeSql method multiple times. The supported platforms are Android, BlackBerry WebWorks (OS 6.0 and higher), iPhone and webOS.

Example

<!DOCTYPE html>

<html>

<head>

<title>ExecuteSql Method</title>

charset="utf-8" />

// Wait for Cordova to load

document.addEventListener("deviceready", onDeviceReady, false);

// Cordova is ready

function onDeviceReady() {

var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

db.transaction(populateDB, errorCB, successCB);

}

// Populate the database

function populateDB(tx) {

tx.executeSql('DROP TABLE IF EXISTS DEMO');

tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

}

// Transaction error callback

function errorCB(err) {

alert("Error processing SQL: " + err);

}

// Transaction success callback

function successCB() {

alert("success!");

}

</script>

</head>

<body>

<h1>

Example</h1>

<p>

SQLTransaction</p>

</body>

</html>

SQLResultSet: When we work with the executeSql method of the SQLTransaction it will invoke its callback with a SQLResultSet object. This object has the three properties, the first one is the insertId that will used to return the row number of the successful SQL insert statement; if the SQL does not insert any row then the insertId is not set. The second property of SQLResultSet is rowsAffected; it is always 0 for the SQL select statement, for insert and update it will return the number of rows that have been modified. The third and the last property of SQLResultSet is the rows that represents the rows returned. If no row is selected then the object will be empty. The supported plateforms are Android, BlackBerry WebWorks (OS 6.0 and higher), iPhone and webOS.

Example

<!DOCTYPE html>

<html>

<head>

<title>ExecuteSql Method</title>

charset="utf-8" />

// Wait for Cordova to load

document.addEventListener("deviceready", onDeviceReady, false);

// Populate the database

function populateDB(tx) {

tx.executeSql('DROP TABLE IF EXISTS DEMO');

tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

}

// Query the database

function queryDB(tx) {

tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);

}

// Query the success callback

function querySuccess(tx, results) {

console.log("Returned rows = " + results.rows.length);

// this will be true since it was a select statement and so rowsAffected was 0

if (!results.rowsAffected) {

console.log('No rows affected!');

return false;

}

// for an insert statement, this property will return the ID of the last inserted row

console.log("Last inserted row ID = " + results.insertId);

}

// Transaction error callback

function errorCB(err) {

console.log("Error processing SQL: " + err.code);

}

// Transaction success callback

function successCB() {

var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

db.transaction(queryDB, errorCB);

}

// Cordova is ready

function onDeviceReady() {

var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

db.transaction(populateDB, errorCB, successCB);

}

</script>

</head>

<body>

<h1>

Example</h1>

<p>

Database</p>

</body>

</html>

SQLResultSetList: It is one of the properties of the SQLResultSet that contains the rows returned from a SQL query. The only one property of the SQLResultSetList is length that represents the number of rows returned by the SQL query, and the method is the item that returns the rows at the specified index represented by a JavaScript object. The supported plateforms are Android, BlackBerry WebWorks (OS 6.0 and higher), iPhone and webOS.

Example

<!DOCTYPE html>

<html>

<head>

<title>ExecuteSql Method</title>

charset="utf-8" />

// Wait for Cordova to load

document.addEventListener("deviceready", onDeviceReady, false);

// Populate the database

function populateDB(tx) {

tx.executeSql('DROP TABLE IF EXISTS DEMO');

tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');

tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');

}

// Query the database

function queryDB(tx) {

tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);

}

// Query the success callback

function querySuccess(tx, results) {

var len = results.rows.length;

console.log("DEMO table: " + len + " rows found.");

for (var i = 0; i < len; i++) {

console.log("Row = " + i + " ID = " + results.rows.item(i).id + " Data = " + results.rows.item(i).data);

}

// Transaction error callback

function errorCB(err) {

console.log("Error processing SQL: " + err.code);

}

// Transaction success callback

function successCB() {

var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

db.transaction(queryDB, errorCB);

}

// Cordova is ready

function onDeviceReady() {

var db = window.openDatabase("Database", "1.0", "Cordova Demo", 200000);

db.transaction(populateDB, errorCB, successCB);

}

</script>

</head>

<body>

<h1>Example</h1>

<p>Database</p>

</body>

</html>

SQLError: When an error occurs the SQLError object is thrown. The properties of the SQLError is code and message. The code is one of the predefined error codes and the message is the description of the error.

Summary : In this article I explained the various objects of SQL in Apache Cordova/PhoneGap.