Building Android Apps: Web SQL Database (part 1) - Creating a Database

1/2/2011 3:21:44 PM
Of all the exciting features of HTML5, the one that rocks my world the most is the Web SQL Database. The Web SQL Database spec gives developers a simple but powerful JavaScript database API to store persistent data in a local SQLite database.
Figure 1. Before the refreshEntries() function, the title just says “Date”...

Figure 2. ...and after the refreshEntries() function, the title reflects the selected date


Technically, the Web SQL Database spec is not part of HTML5. It was broken out of the original HTML5 spec into its own spec, but in casual conversation it’s often still referred to as an “HTML5 feature.”

Developers can use standard SQL statements to create tables and to insert, update, select, and delete rows. The JavaScript database API even supports transactions. We’re talking about SQL here, so there is an inherent complexity. Regardless, this is a game-changing feature, so time spent getting your head around it will be well rewarded.

1. Creating a Database

Now that our Date panel knows which date the user has selected, we have all the information we need to allow the user to create entries. Before we can write the createEntry() function, we need to set up a database table to store the submitted data (this is a one-time operation). We’ll add some lines to kilo.js to do so:

var db;
$('#settings form').submit(saveSettings);
$('#settings').bind('pageAnimationStart', loadSettings);
$('#dates li a').click(function(){
var dayOffset =;
var date = new Date();
date.setDate(date.getDate() - dayOffset);
sessionStorage.currentDate = date.getMonth() + 1 + '/' +
date.getDate() + '/' +
var shortName = 'Kilo';
var version = '1.0';
var displayName = 'Kilo';
var maxSize = 65536;
db = openDatabase(shortName, version, displayName, maxSize);
function(transaction) {
' date DATE NOT NULL, food TEXT NOT NULL, ' +
' calories INTEGER NOT NULL );'

If you were to launch the app as is, it would create a database named Kilo on the Android phone.

In the desktop version of Chrome, you can actually view and interact with your client-side databases by navigating to ViewDeveloperDeveloper Tools, and clicking the Storage tab.

The Developer Tools included in desktop Chrome are extremely helpful when debugging. By default, it appears as a pane of your current browser window. If you click the undock icon (hover over the icons at the bottom left to see what they do), it will appear in a separate window, as shown in Figure 3. The interface even allows you to send arbitrary SQL queries to the database by clicking on the database name (see Figure 4).

Figure 3. The Storage tab in Chrome’s Developer Tools with some test records displayed

  •  Building Android Apps: Web Storage
  •  Building Android Apps : Simple Bells and Whistles
  •  Building Android Apps : Traffic Cop
  •  iPhone Application Development : Exploring Interface Builder - Connecting to Code
  •  iPhone Application Development : Customizing Interface Appearance
  •  iPhone Application Development : Creating User Interfaces
  •  iPhone Application Development : Understanding Interface Builder
  •  Android Security Tools
  •  Android Security : Binder Interfaces
  •  Android Security : Files and Preferences
  •  Android Security : ContentProviders
  •  Android Security : Services
  •  Android Security : Broadcasts
  •  Android Security : Activities
  •  Android Security : Creating New Manifest Permissions
  •  Android Permissions Review
  •  Android’s Security Model
  •  Android’s Securable IPC Mechanisms
  •  CSS for Mobile Browsers : CSS Techniques
  •  CSS for Mobile Browsers : Selectors
    Top 10
    Fujifilm Fujinon XF 14mm f/2.8R Lens Review
    Nikon Coolpix L320 - A Compact Camera (Part 2)
    Nikon Coolpix L320 - A Compact Camera (Part 1)
    NZXT Kraken X60 - The Best Liquid Cooling System (Part 2)
    NZXT Kraken X60 - The Best Liquid Cooling System (Part 1)
    Late 2012 Razer Blade - One Of The Luxurious Laptops (Part 4)
    Late 2012 Razer Blade - One Of The Luxurious Laptops (Part 3)
    Late 2012 Razer Blade - One Of The Luxurious Laptops (Part 2)
    Late 2012 Razer Blade - One Of The Luxurious Laptops (Part 1)
    The Apple iPad (Fourth Generation) - The Bigger Brother Is Back
    Most View
    How To Make A Massive Synth Bass Sound (Part 5) : Commercial house bass with impOSCar
    Guide To Upgrades With The Greatest Effects (Part 1)
    Top 10 Televisions – Q1 2013
    Master The New Calendar
    17 Killer Mac Apps Under $20 (Part 1) : Smartday, Eisenpower
    Google vs Apple vs Microsoft (Part 5)
    Nokia Lumia 822 Windows Phone 8 Smartphone (Part 3)
    Red Redray Player - Dedicated 4K Player
    Lenovo Ideapad Yoga 11 Hybrid Laptop
    Gentlemen, Lend Me Your Ears!