Introduction
If you are using PhoneGap to create your iPhone application, you may be interested to know that you can create SQL Databases for use by your application and access them from your application's Javascript. This is all possible because of HTML5 support built into Safari, the iPhone's native browser. This guide aims to show how to add database support to your iPhone application through simple examples
Step 1 - Initialising the database
When your iPhone application runs for the first time, there will be no database and no tables. The first task is to create the database and the tables within it:
var mydb=false;
// initialise the database
initDB = function() {
try {
if (!window.openDatabase) {
alert('not supported');
} else {
var shortName = 'phonegap';
var version = '1.0';
var displayName = 'PhoneGap Test Database';
var maxSize = 65536; // in bytes
mydb = openDatabase(shortName, version, displayName, maxSize);
}
} catch(e) {
// Error handling code goes here.
if (e == INVALID_STATE_ERR) {
// Version number mismatch.
alert("Invalid database version.");
} else {
alert("Unknown error "+e+".");
}
return;
}
}
The initDB function first checks whether databases are supported or not. If they are, then a database called "phonegap" is created/opened. The version number of the database allows future versions of your application to upgrade existing tables (by adding tables, columns etc) without loss of data. The global variable "mydb" is used as the database handle.
Step 2 - Filling the database
The data in your database is entirely up to you and learning SQL is beyond the scope of this tutorial. For the purposes of this example, I shall assume we are storing the names of our favourite celebrities:
// db error handler - prevents the rest of the transaction going ahead on failure
errorHandler = function (transaction, error) {
// returns true to rollback the transaction
return true;
}
// null db data handler
nullDataHandler = function (transaction, results) { }
// create tables for the database
createTables = function() {
try {
mydb.transaction(
function(transaction) {
transaction.executeSql('CREATE TABLE celebs(id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL DEFAULT "");', [], nullDataHandler, errorHandler);
transaction.executeSql('insert into celebs (id,name) VALUES (1,"Kylie Minogue");', [], nullDataHandler, errorHandler);
transaction.executeSql('insert into celebs (id,name) VALUES (2,"Keira Knightley");', [], nullDataHandler, errorHandler);
});
} catch(e) {
/// alert(e.message);
return;
}
}
The "createTables" function creates a single table called "celebs" which has two rows:
- id - the primary key of the table
- name - the name of the celebrity
For good measure, it also primes the table with two rows. Each SQL statement is executed as part of a "transaction"; if a single SQL statement in the transaction fails, then none of the transaction is committed to the database. The first time your application is run, it will create the table and create the two rows in it. On subsequent runs, the "CREATE" statement fails because the table already exists, so the following "INSERT" statements are never attempted.
Step 3 - Retrieving data from the database
To load our data from the database we can use the "loadCelebs" function, which performs a "SELECT" statement.
// callback function to retrieve the data from the prefs table
celebsDataHandler=function(transaction, results) {
// Handle the results
var html = "<ul>";
for (var i=0; i<results.rows.length; i++) {
var row = results.rows.item(i);
html += '<li>'+row['name']+'</li>\n';
}
html +='</ul>';
alert(html);
}
// load the currently selected icons
loadCelebs = function() {
try {
mydb.transaction(
function(transaction) {
transaction.executeSql('SELECT * FROM celebs ORDER BY name',[], celebsDataHandler, errorHandler);
});
} catch(e) {
alert(e.message);
}
}
The resultant data is sent to "celebsDataHandler", where the data can be manipulated as desired.
Links
