Building Android Apps: Web SQL Database (part 2) - Inserting Rows

2. Inserting Rows

Now that we have a database set up to receive some entries, we can set about building the createEntry() function. First, you have to override the submit event of the #createEntry form. You can do so by binding the createEntry() function to the submit event in the document ready function in kilo.js (here I just show the first few lines with the added line of code in bold):

$(document).ready(function(){
    $('#createEntry form').submit(createEntry);
    $('#settings form').submit(saveSettings);
    $('#settings').bind('pageAnimationStart', loadSettings);
    ...

Figure 4. The Storage tab in Chrome’s Developer Tools allows you to execute arbitrary SQL statements against your database

Now when a user submits the #createEntry form, the createEntry() function is called. Next, add the following to kilo.js to create the record in the database:

function createEntry() {
    var date = sessionStorage.currentDate;
    var calories = $('#calories').val();
    var food = $('#food').val();
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);', 
                [date, calories, food], 
                function(){
                    refreshEntries();
                    jQT.goBack();
                }, 
                errorHandler
            );
        }
    );
    return false;
}

					  

---

Note:

Quotes (' or ") around the ? placeholders are not necessary—escaping and quoting of data is handled automatically.

---

2.1. Error handling

Assuming the insert is successful, the anonymous function passed as the third parameter will be executed. It calls the refreshEntries() function (at the moment, this function only updates the title of the Date panel, but soon it will make entries you create appear in the list there) and it simulates a tap on the Cancel button to dismiss the New Entry panel and return to the Date panel. As we saw earlier with the Settings panel, the Cancel button does not cancel the submit action—it’s really just a Back button labeled “Cancel” that isn’t shaped like a left arrow.

If the insert is not successful, the errorHandler() function will run. Add the following to the kilo.js file:

function errorHandler(transaction, error) {
    alert('Oops. Error was '+error.message+' (Code '+error.code+')');
    return true;
}

The error handler is passed two parameters: the transaction object and the error object. Here, we’re using the error object to alert the user to the message and error code that were thrown.

Error handlers must return true or false. When an error handler returns true (i.e., “Yes, this is a fatal error”), execution is halted and the entire transaction is rolled back. When an error handler returns false (i.e., “No, this is not a fatal error”), execution will continue.

In some cases, you might want to branch based on the type of error to decide whether you should return true or false. 

You may have noticed that the error handler function accepts a transaction object in addition to the error object. It’s conceivable that in some cases you might want to execute a SQL statement inside the error handler, perhaps to log the error or record some metadata for debugging or crash-reporting purposes. The transaction object parameter allows you to make more executeSql() calls from inside the error handler, like so (this is just an example; it will not run unless you’ve created the errors table that it refers to):

function errorHandler(transaction, error) {
  alert('Oops. Error was '+error.message+' (Code '+error.code+')');
  transaction.executeSql('INSERT INTO errors (code, message) VALUES (?, ?);', 
                         [error.code, error.message]);
  return false;
}

					  

Please take special note of the fact that we have to return false from the error handler if we want the executeSql() statement to run. If we return true (or nothing at all), the entire transaction—including this SQL statement—will be rolled back, thereby preventing the desired result.

---

Note:

Although I won’t be doing so in my examples, you should know that you can also specify success and error handlers on the transaction method itself. This gives you a convenient location to execute code after a long series of executeSql() statements have completed.

Oddly, the parameter order for the transaction method’s callbacks is defined to be error, then success (the reverse of the order for executeSql()). Here’s a version of the createEntry() function with transaction callbacks added toward the end (don’t add these to kilo.js, because we haven’t defined either of these methods):

function createEntry() {
    var date = sessionStorage.currentDate;
    var calories = $('#calories').val();
    var food = $('#food').val();
    db.transaction(
        function(transaction) {
            transaction.executeSql(
                'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);', 
                [date, calories, food], 
                function(){
                    refreshEntries();
                    jQT.goBack();
                }, 
                errorHandler
            );
        }, 
        transactionErrorHandler, 
        transactionSuccessHandler
    );
    return false;
}