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):


$('#createEntry form').submit(createEntry);

$('#settings form').submit(saveSettings); $('#settings').bind('pageAnimationStart', loadSettings);

Figure 5-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(Q

function(transaction) { transaction.executeSql(

'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);',

refreshEntries(); jQT.goBack();

}, errorHandler

return false;

O This section contains some variables that we're going to use in the SQL query. As you may recall (from "Saving the Selected Date to Session Storage" on page 75), the date the user taps on the Dates panel is stored in sessionStorage.currentDate. The other two values (calories and food) are pulled out of the data entry form using the same approach that we used earlier with the Settings form.

© This code opens a database transaction and runs an executeSql() call. Here we are passing four parameters to the executeSql() method:

'INSERT INTO entries (date, calories, food) VALUES (?, ?, ?);'

This is the statement that will be executed. The question marks are data placeholders. [date, calories, food]

This is an array of the values being sent to the database. They correspond by position with the data placeholder question marks in the SQL statement.


This anonymous function will execute if the SQL query is successful. errorHandler

This is the name of the function that will execute if the SQL query fails.

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

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. Table 5-1, at the end of this chapter, shows the (current) possible error codes according to the W3C Web SQL Database working draft specification.

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.

® - 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 " v i 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 (?, ?, ?);',

refreshEntries(); jQT.goBack();

}, errorHandler

}, transactionErrorHandler, transactionSuccessHandler

return false;

0 0

Post a comment