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

• http://developer.apple.com/documentation/iPhone/Conceptual/SafariJSDatabaseGuide/index.html - Safari Database Programming Guide