< Previous PageNext Page > Hide TOC

Using the JavaScript Database

The JavaScript database, based on SQLite, is a very basic relational database intended to provide local storage for content that is too large to conveniently store in cookies (or is too important to accidentally delete when the user clears out his or her cookies).

In addition, by providing a relational database model, the JavaScript database makes it easy to work with complex, interconnected data in a webpage. You might use it as an alternative to storing user data on the server, or you might use it as a high-speed local cache of information the user has recently queried from a server-side database.

The sections in this chapter guide you through the basic steps of creating a JavaScript-based application that takes advantage of the JavaScript database.

In this section:

Creating and Opening a Database
Creating Tables
Executing a Query
Per-Query Data and Error Callbacks
Transaction Callbacks

Creating and Opening a Database

Before you can use a database or create tables within the database, you must first open a connection to the database. When you open a database, an empty database is automatically created if the database you request does not exist. Thus, the processes for opening and creating a database are identical.

To open a database, you must obtain a database object with the openDatabase method as follows:

try {

    if (!window.openDatabase) {

        alert('not supported');

    } else {

        var shortName = 'mydatabase';

        var version = '1.0';

        var displayName = 'My Important Database';

        var maxSize = 65536; // in bytes

        var mydb = openDatabase(shortName, version, displayName, maxSize);

        // You should have a database instance in mydb.

    }

} 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;

}

alert("Database is: "+mydb);

The version upgrade feature is not yet supported, so for now you should just set the version number field to 1.0 and ignore it.

The short name is the name for your database as stored on disk. This argument controls which database you are accessing.

The display name field contains a name to be used by the browser if it needs to describe your database in any user interaction, such as asking permission to enlarge the database.

The maximum size field tells the browser the size to which you expect your database to grow. This prevents a runaway web application from using excessive local resources. The browser may set limits on how large a value you can specify for this field, but the details of these limits are not yet fully defined.

Creating Tables

The remainder of this chapter assumes a database that contains a single table with the following schema:

CREATE TABLE people(

    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

    name TEXT NOT NULL DEFAULT "John Doe",

    shirt TEXT NOT NULL DEFAULT "Purple"

);

You can create this table and insert a few initial values with the following functions:

function nullDataHandler(transaction, results) { }

function createTables(db)

{

    db.transaction(

        function (transaction) {

            /* The first query causes the transaction to (intentionally) fail if the table exists. */

            transaction.executeSql('CREATE TABLE people(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL DEFAULT "John Doe", shirt TEXT NOT NULL DEFAULT "Purple");', [], nullDataHandler, errorHandler);

            /* These insertions will be skipped if the table already exists. */

            transaction.executeSql('insert into people (name, shirt) VALUES ("Joe", "Green");', [], nullDataHandler, errorHandler);

            transaction.executeSql('insert into people (name, shirt) VALUES ("Mark", "Blue");', [], nullDataHandler, errorHandler);

            transaction.executeSql('insert into people (name, shirt) VALUES ("Phil", "Orange");', [], nullDataHandler, errorHandler);

            transaction.executeSql('insert into people (name, shirt) VALUES ("jdoe", "Purple");', [], nullDataHandler, errorHandler);

        }

    );

}

The errorHandler function is shown and explained in “Per-Query Data and Error Callbacks.”

Executing a Query

Executing a SQL query is fairly straightforward. All queries must be part of a transaction (though the transaction may contain only a single query if desired).

You could then modify the value as follows:

var name = 'jdoe';

var shirt = 'fuschia';

db.transaction(

    function (transaction) {

        transaction.executeSql("UPDATE people set shirt=? where name=?;",

            [ shirt, name ]); // array of values for the ? placeholders

    }

);

Notice that this transaction provides no data or error handlers. These handlers are entirely optional, and may be omitted if you don’t care about finding out whether an error occurs in a particular statement. (You can still detect a failure of the entire transaction, as described in “Transaction Callbacks.”)

Per-Query Data and Error Callbacks

The examples in the previous section did not return any data. For a query that actually returns data, you have to provide a callback function to handle the data.

The following code prints a list of names where the value of the shirt field is “Green”:

function errorHandler(transaction, error)

{

    // Error is a human-readable string.

    alert('Oops.  Error was '+error.message+' (Code '+error.code+')');

    // Handle errors here

    var we_think_this_error_is_fatal = true;

    if (we_think_this_error_is_fatal) return true;

    return false;

}

function dataHandler(transaction, results)

{

    // Handle the results

    var string = "Green shirt list contains the following people:\n\n";

    for (var i=0; i<results.rows.length; i++) {

        var row = results.rows.item(i);

        string = string + row['name'] + '\n';

    }

    alert(string);

}

db.transaction(

    function (transaction) {

        transaction.executeSql("SELECT * from people where shirt='Green';",

            [], // array of values for the ? placeholders

            dataHandler, errorHandler);

    }

);

The error-handling callback is rather straightforward. If the callback returns true, the entire transaction is rolled back. If the callback returns false, the transaction continues as if nothing had gone wrong.

Thus, if you are executing a query that is optional—if a failure of that particular query should not cause the transaction to fail—you should pass in a callback that returns false. If a failure of the query should cause the entire transaction to fail, you should pass in a callback that returns true.

Of course, you can also pass in a callback that decides whether to return true or false depending on the nature of the error.

If you do not provide an error callback at all, the error is treated as fatal and causes the transaction to roll back.

Transaction Callbacks

In addition to handling errors and data on a per-query basis (as described in “Per-Query Data and Error Callbacks”), you can also check for success or failure of the entire transaction in the same way. For example:

function myTransactionErrorCallback(error)

{

    alert('Oops.  Error was '+error.message+' (Code '+error.code+')');

}

function myTransactionSuccessCallback()

{

    alert('J. Doe's shirt is Mauve.');

}

var name = 'jdoe';

var shirt = 'mauve';

db.transaction(

    function (transaction) {

        transaction.executeSql("UPDATE people set shirt=? where name=?;",

            [ shirt, name ]); // array of values for the ? placeholders

    }, myTransactionErrorCallback, myTransactionSuccessCallback

);

Upon successful completion of the transaction, the success callback is called. If the transaction fails because any portion thereof fails, the error callback is called instead.

< Previous PageNext Page > Hide TOC