586 CHAPTER 16 Offline web applications
function itemInserted(transaction, results) {
alert("Id: " + results.insertId);
}var firstName = 'Daniel';
var lastName = 'Defoe';
db.transaction(function(t){
t.executeSql("INSERT INTO authors(firstName, lastName) VALUES(?, ?)"
, [firstName, lastName]
, itemInserted);
});Updating an existing record
In the following example, the lastName of the author, which has an id of 1, is updated. Besides
the SQL syntax differences, it’s very similar to the code used for adding a new record.
var db = openDatabase('Library', '2.0', 'My library', 5 * 1024 * 1024);
var authorId = 1;
var lastName = 'Smith';
db.transaction(function(t){
t.executeSql("UPDATE authors SET lastName =? WHERE id = ?"
, [lastName, authorId]);
});Deleting a record
Removing records is also fairly straightforward. The following example deletes the author
record with an id of 1.
var db = openDatabase('Library', '2.0', 'My library', 5 * 1024 * 1024);
var authorId = 1;
db.transaction(function(t){
t.executeSql("DELETE FROM authors WHERE id = ?", [authorId]);
});Reading values from the database
Now that you know how to add data to the database, you can read and display those records
back to the user. Create a simple SELECT statement to read all values from the authors table.
When executeSql is called this time, a callback method is passed that accepts a transaction
object and a resultset containing the rows returned from the SQL statement.
As the displayResults method iterates through the rows, it formats the person’s name in a
list item and adds it to an unordered list with an id of items. To access the individual column
values within the row, use dot notation, which reads each as a property on the object.
function displayResults(transaction, results) {
for (var i = 0; i < results.rows.length; i++) {
var item = results.rows.item(i);
$('#items').append('<li>' + item.firstName + " " + item.lastName + '</li>');
}
}