WebSQL

The methods

• Database openDatabase(in DOMString name, in DOMString version, in DOMString displayName, in unsigned long estimatedSize, in optional DatabaseCallback creationCallback)
• void transaction(in SQLTransactionCallback callback, in optional SQLTransactionErrorCallback errorCallback, in optional SQLVoidCallback successCallback)
• void executeSql(in DOMString sqlStatement, in optional ObjectArray arguments, in optional SQLStatementCallback callback, in optional SQLStatementErrorCallback errorCallback)
• void changeVersion(in DOMString oldVersion, in DOMString newVersion, in optional SQLTransactionCallback callback, in optional SQLTransactionErrorCallback errorCallback, in optional SQLVoidCallback successCallback)
  

The callbacks

• DatabaseCallback: void handleEvent(in Database database)
• SQLTransactionCallback: void handleEvent(in SQLTransaction transaction)
  
• SQLTransactionErrorCallback: void handleEvent(in SQLError error)
  
• SQLVoidCallback: void handleEvent()
  
• SQLStatementCallback: void handleEvent(in SQLTransaction transaction, in SQLResultSet resultSet)
  
• SQLStatementErrorCallback: boolean handleEvent(in SQLTransaction transaction, in SQLError error)

The data structures

• Database: has a version property, transaction(), changeVersion()
  
• SQLTransaction: executeSql(),
  
• SQLError: code, message
  
• SQLResultSet: insertId, rowsAffected, SQLResultSetRowList rows
  
• SQLResultSetRowList: length, item(index)

Basic usage

This API is asynchronous, so makes heavily use of callbacks.

Error handling

Error handling can be done at 2 levels: transaction and query execution.

When an error callback is given to the executeSql method and it returns false, the transaction is NOT rolled back. If you return true, or don't give an error callback, the transaction is rolled back. When the executeSql error handler returns false, the transaction error handler will not fire.

You can also define an error handler for the transaction as a whole, so you can know when something went wrong.

Opening / creating a database

Opening and creating a database happens inside openDatabase. If the second version argument is NOT and empty string, the database requested MUST be the same version, otherwise it throws an exception. An empty string as version will give you any version. If the database does not yet exists, it will be created.

var db = null;

function getDatabase(successCallback, errorCallback) {
  db = window.openDatabase("testdb", "", "Just a test database", 2 * 1024 * 1024);
  if (db.version != "1") {
    alert("Creating new version of database");
    db.changeVersion(db.version, "1",
      function(tx) {
        tx.executeSql("DROP TABLE IF EXISTS companies");
        tx.executeSql("DROP TABLE IF EXISTS members");
        tx.executeSql("CREATE TABLE IF NOT EXISTS companies
          (id INTEGER PRIMARY KEY, name TEXT, last_update TIMESTAMP DEFAULT NULL)");
        tx.executeSql("CREATE TABLE IF NOT EXISTS members
          (id INTEGER PRIMARY KEY, name TEXT, company_id INTEGER)");
      },
      function(err) {
        errorCallback(err);
      },
      function() {
        successCallback(db);
      }
    );
  } else {
    successCallback(db);
  }

  /*
  Another option to create / open database is by making use of the creationCallback argument:
  db = window.openDatabase("testdb", "", "Just a test database", 2 * 1024 * 1024,
    function(db) {
      We are inside the creationCallback, so the database does not yest exist, so here
      we can add tables to it.
      db.changeVersion()...
    }
  );
  */
}

function openDatabaseSuccess(db) {
  alert("Database " + db.version + " created!");
}

function openDatabaseError(err) {
  alert("Error opening database: " + err.message);
}

getDatabase(openDatabaseSuccess, openDatabaseError);

Executing queries

Every query is executed inside a transaction. A transaction can have 1 or more queries. The transaction is rolled back when something goes wrong. So if you have 10 queries and the 7th gives an error, the whole transaction is rolled back. So if you're doing some other processing inside a transaction (for example looping through some objects) and you want to "cancel" the transaction, just throw an exception.

function setData() {
  db.transaction(function(tx) {
    tx.executeSql("delete from members");
    tx.executeSql("delete from companies");
    var companies = [
      {"id" : 1, "name" : "Jamin"},
      {"id" : 2, "name" : "Shell"},
      {"id" : 4, "name" : "Philips"}
    ];
    for (var i = 0; i < companies.length; i++) {
      var company = companies[i];
      tx.executeSql("INSERT INTO companies (id, name) VALUES (?, ?)",
        [company.id, company.name],
        function(tx, result) {
          console.log(company.id + ", " + company.name + ", " + result.insertId);
        }
      );
    }
    var members = [
      {"name" : "Jan", "company_id" : 1},
      {"name" : "Carl", "company_id" : 1},
      {"name" : "Marit", "company_id" : 2},
      {"name" : "Kermit", "company_id" : 4}
    ];
    for (var i = 0; i < members.length; i++) {
      var member = members[i];
      tx.executeSql("INSERT INTO members (name, company_id) VALUES (?, ?)",
        [member.name, member.company_id],
        function(tx, result) {
          console.log(member.name);
        }
      );
    }
  }, function(err) {
    alert("Transaction rollbacked: " + err.code + " (" + err.message + ")");
  }, function() {
    alert("Transaction committed!");
  });
}

Using the resultset with SELECT queries:

function getMember(id, callback) {
  db.transaction(function(tx) {
    tx.executeSql("SELECT members.*, companies.name as company_name
      FROM members, companies WHERE members.id = ? and members.company_id = companies.id",
      [id],
      function(tx, result) {
        var member = null;
        if (result.rows.length != 0) {
          var row = result.rows.item(0);
          member = new Member(row.id, row.name, row.company_id, row.company_name);
        }
        callback(member);
      }
    );
  });
}