PhoneGap + jQuery Mobile access HTML5 Sqlite database problem in Android 2.1

Question

I am trying to access to HTML5 database when I was building an Android App using PhoneGap + jQuery Mobile. It is strange that the following code can get different results. It can get correct result in Galaxy S 2.2 (chris, lulu, chris), but in Cliq TX (Android 2.1) the app get crushed (the last alert is createTable()), and in Emulator 2.3 the app crashed at the first alert. I pulled the database out and found that it is correctly created. I think it is hard to explain those problems, why such simple codes cause so many problems? Dose someone have success on accessing the database?

Thank you in advance.

    function init() {
    alert('init()');
    document.addEventListener("deviceready", onDeviceReady, false);
}

function onDeviceReady() {
    if (!window.openDatabase) {
        alert('Local Databases are not supported.');
    } else {
        db = window
                .openDatabase("YCHW", "1.0", "YCHW", 200000);
    }
    dropTable();
    createTable();
    insertData();
    selectData();
}

function dropTable(){
    alert('dropTable()');
    db.transaction(
        function (transaction) {
            transaction.executeSql("DROP TABLE measurements;", [], nullDataHandler, errorHandler);
        }
    );
    console.log("Table 'measurements' has been dropped.");
    // location.reload();
}

function createTable(){
    alert('createTable()');
    db.transaction(
        function (transaction) {
            transaction.executeSql('CREATE TABLE IF NOT EXISTS measurements(id INTEGER NOT NULL PRIMARY KEY, user TEXT NOT NULL, date TEXT NOT NULL, height INTEGER NOT NULL, weight INTEGER NOT NULL, bmi REAL NOT NULL, abnormal INTEGER NOT NULL);', [], nullDataHandler, errorHandler);
        }
    );
    console.log("Table 'measurements' has been created.");
}

function insertData(){
    alert('insertData()');
    db.transaction(
        function (transaction) {
            var data = ['1','chris','2000-02-22 00:00:00.000','170','60', '20', '0'];
            transaction.executeSql("INSERT INTO measurements(id, user, date, height, weight, bmi, abnormal) VALUES (?, ?, ?, ?, ?, ?, ?)", [data[0], data[1], data[2], data[3], data[4], data[5], data[6]]);
            data = ['2','lulu','2000-02-22 00:00:00.000','170','60', '20.12', '0'];
            transaction.executeSql("INSERT INTO measurements(id, user, date, height, weight, bmi, abnormal) VALUES (?, ?, ?, ?, ?, ?, ?)", [data[0], data[1], data[2], data[3], data[4], data[5], data[6]]);
            data = ['3','chris','2222-02-22 00:00:00.000','170','60', '20.12', '1'];
            transaction.executeSql("INSERT INTO measurements(id, user, date, height, weight, bmi, abnormal) VALUES (?, ?, ?, ?, ?, ?, ?)", [data[0], data[1], data[2], data[3], data[4], data[5], data[6]]);
        }
    );
    console.log("Data has been inserted.");
}

function selectData(){
    alert('selectData()');
    db.transaction(
        function (transaction) {
            transaction.executeSql('SELECT * FROM measurements;', [], dataSelectHandler, errorHandler);
        }
    );
    console.log("Data has been selected.");
}

function dataSelectHandler(transaction, results){
    alert('dataSelectHandler()');
    for (var i=0; i<results.rows.length; i++) {
        var row = results.rows.item(i);
        var measurement = new Object();
        measurement.id   = row['id'];
        measurement.user = row['user'];
        alert(measurement.user);
    }
}

function nullDataHandler(){
    alert('nullDataHandler()');
    console.log("nullDataHandler()");
}

function errorHandler(transaction, error){
    alert('errorHandler()');
    if (error.code==1){
        // DB Table already exists
        alert('DB Table already exists');
    } else {
        // Error is a human-readable string.
        console.log('Oops.  Error was '+error.message+' (Code '+error.code+')');
        alert('Oops.  Error was '+error.message+' (Code '+error.code+')');
    }
    return false;
}

Answer 1

I had a similar issue, and my solution was to put do something like this:

Instead of doing this:

dropTable();
createTable();
insertData();
selectData();

I would chain them, like this:

function (transaction) {
            transaction.executeSql("DROP TABLE measurements;", [], nullDataHandler, errorHandler);
        }, errorHandler, createTable

And so on, because the function(transaction) can also use callback handlers, and you can verify that the previous statement has fully executed. Hope this helps.