Creating The Database And Tables in Interacting  With Web SQL in Moblile Web Development

Creating The Database And Tables in Interacting With Web SQL in Moblile Web Development

To follow along, you need to run some code to create the database for Corks.Follow these steps to create your main database.

1. Open your text editor of choice and start editing in the index.html file.

2. At the bottom of the </body> tag, add the following snippet of code:

<script type=”text/javascript” language=”Javascript”>
var db;
$(document).ready(function(){
});
</script>

This snippet enables you to fire some JavaScript code when the document is fully loaded.

The db variable is to keep track of your database transactions.

3.  Add the code to create the database for Corks with the following snippet:

var db;
$(document).ready(function(){
db = openDatabase(‘myCorks’, ‘1.0’, ‘My Corks Database’, 2 * 1024 * 1024);
});

Now the database will be created under the name myCorks. If you save your index file and open your Web Inspector, you should see a page similar to that shown in Figure 7-1.

Figure 7-1: Web Inspector open to Databases

Now you start building the tables. Follow these steps:

1. Open the transaction method of the db variable and use the executeSQL command:

db.transaction(function (tx) {
tx.executeSql(“CREATE TABLE
IF NOT EXISTS wines (wine_id INTEGER PRIMARY KEY AUTOINCREMENT, wine_name,

wine_description, wine_abv, winery_id, color_id, created_at)”, null, sR, fR);
tx.executeSql(“CREATE TABLE
IF NOT EXISTS winery (winery_id INTEGER PRIMARY KEY AUTOINCREMENT, winery_name,
created_at)”, null, sR, fR);
tx.executeSql(‘CREATE TABLE
IF NOT EXISTS color (color_id PRIMARY KEY AUTOINCREMENT, color_name,
created_at)’,
null, sR, fR);
tx.executeSql(‘CREATE TABLE
IF NOT EXISTS activity (activity_id PRIMARY KEY AUTOINCREMENT, wine_id,
activity_note, activity_quantity, created_at)’, null, sR, fR);
});

Notice that you are setting an sR and fRcallback that will catch errors on creation of the tables. You can define them outside the document.ready()function, as shown here:

function sR(a,b){
// The query was successfully!
}
function fR(a,b){
// Oops! There was an issue. Let’s alert the user.
alert(b.message);
}

In this example, the script will do nothing if the query is successful, but will alert the error (in the form of b.message) if there is a problem on creation.

When creating and testing your databases, you will see multiple databases in the Web Inspector, similar to what you see in Figure 7-2.

Creating The Database And Tables in Interacting  With Web SQL in Moblile Web Development1Figure 7-2: Multiple databases in
the Web Inspector

This is normal behavior, as a new version is created on launch every time. However, if the database table exists, it overwrites the data in the tables. According to the Web SQL specification, there is no way to delete a version of the database. The only way to delete those databases is to clear your web cache and web data from your browser.

Beware! This will clear all active tables as well.

2. Save this index.html file and load it in your browser.Open your last database, myCorks,in the Web Inspector. You should see an image like the one in Figure 7-3:

Creating The Database And Tables in Interacting  With Web SQL in Moblile Web Development2Figure 7-3: The myCorks database in the
Web Inspector

Written by imkits