Prepopulated Database using PhoneGap Storage API, Cordova 2.0.0


I slightly adapted the PhoneGap Cordova Storage API to show content on the screen (PG’s version only shows content in the console). Here is that code below. Just copy and paste it into a text file, name it PGStorageAPI.html, and click to display in web browser. You’ll need cordova-2.0.0.js in the same folder.

Please note that this database is created in the HTML on this page. It is not a separate database that can be lost in an upgrade or low-memory situation. Also, it is not a data-entry database where the user enters data. Just like you cannot lose an HTML page in your app, so you can’t lose the pre-populated data on this page. As you can see, this pre-populated database is ideal for highly repetitive page layouts, such as this one where the same layout information is repeated over and over. Entering the information once in the database frees one from hand-coding the HTML for each entry. Image Gallery, anyone?

(When I copied this file into a text file and renamed the ending .html, and included cordova-2.0.0.js, the page was blank. A bit of searching around found several double-quotes turned into question marks, and a mixture of curly and straight quotes and double-quotes. Once I go those fixed, the script worked fine. I guess it was the change from typing it in my Mac, pasting it in here, then copying/pasting from here into a Windows text editor. What a mess. )

<!DOCTYPE html>
<html>
<head>
<title>Prepopulated DB (PG Storage Example)</title>

<script type=”text/javascript” charset=”utf-8″ src=”cordova-2.0.0.js”></script>
<script type=”text/javascript” charset=”utf-8″>

// Wait for Cordova to load

document.addEventListener(“deviceready”, onDeviceReady, false);

// Populate the database

function populateDB(tx) {
tx.executeSql(‘DROP TABLE IF EXISTS CARS’);
tx.executeSql(‘CREATE TABLE IF NOT EXISTS CARS (id unique, bodyType TEXT NOT NULL, category TEXT NOT NULL, name TEXT NULL, photo TEXT NULL, resource TEXT NULL, caption TEXT NULL)’);
tx.executeSql(‘INSERT INTO CARS (id, bodyType, category, name, photo, resource, caption) VALUES (1, “Short Course”, “SC8.2e”, “SC8.2e Ready-To-Run”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC8.2e/SC8.2e_RTR_2560x2048_sm.jpg&#8221;, “http://www.teamassociated.com/cars_and_trucks/SC8.2e/RTR/&#8221;,”Modeled after the short-course race trucks that compete in the Lucas Oil Off Road Racing Series, the SC8.2e RTR takes the next evolutionary step in Team Associated\’s 1:8 scale short-course line by adding the new performance suspension developed on our R.O.A.R. National Championship-winning RC8.2 buggy.”)’);
tx.executeSql(‘INSERT INTO CARS (id, bodyType, category, name, photo, resource, caption) VALUES (2, “Short Course”, “SC10GT”, “SC10GT Ready-To-Run”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC10GT/SC10GT_RTR_2560x2048_sm.jpg&#8221;, “http://www.teamassociated.com/cars_and_trucks/SC10GT/RTR/&#8221;,”For many people in the RC world, nothing beats a 2-stroke nitro-breathing engine. From the sound, to the smoke, to the brutal power, nitro delivers an experience in a RC truck like nothing else can. Now you can experience that awesome nitro power in the short-course class with the SC10GT!”)’);
tx.executeSql(‘INSERT INTO CARS (id, bodyType, category, name, photo, resource, caption) VALUES (3, “Short Course”, “SC10 4×4″, “SC10 4×4 RTR Combo”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC10_4x4/SC10_4x4_Kit_2560x2048_sm.jpg&#8221;, “http://www.teamassociated.com/cars_and_trucks/SC10_4x4/RTR_Combo/&#8221;,”The SC10 4×4 Ready-To-Runs are RC replicas of the 800+ horsepower short course trucks driven in the Lucas Oil Off Road Racing Series.”)’);
tx.executeSql(‘INSERT INTO CARS (id, bodyType, category, name, photo, resource, caption) VALUES (4, “Short Course”, “SC10″, “SC10 RS RTR Combo”, “http://www.teamassociated.com/pictures/cars_and_trucks/SC10/SC10_RTR_Procomp_2560x2048_sm.jpg&#8221;, “http://www.teamassociated.com/cars_and_trucks/SC10/RS_Combo/&#8221;,”The SC10RS (Race-Spec) RTR Combo is a ready-to-run replica of the trucks driven in the Lucas Oil Off Road Racing Series.”)’);
tx.executeSql(‘INSERT INTO CARS (id, bodyType, category, name, photo, resource, caption) VALUES (5, “Buggy”, “RC8.2e”, “RC8.2e Factory Team”, “http://www.teamassociated.com/pictures/cars_and_trucks/RC8.2e/RC8.2e_2560x2048_sm.jpg&#8221;, “http://www.teamassociated.com/cars_and_trucks/RC8.2e/Factory_Team/&#8221;,”Team Associated has taken all of the refinements from the RC8.2 and have applied them to our electric-power platform. The RC8.2e has already been proven as a winner after TQ\’ing and winning the 2011 Sidewinder Nitro Explosion in the capable hands of Ryan Cavalieri.”)’);
}
// Query the database.

function queryDB(tx) {
tx.executeSql(“SELECT * FROM CARS”, [], querySuccess, errorCB);
}

// Query the success callback

function querySuccess(tx, results) {
var len = results.rows.length;
console.log(“CARS table: ” + len + ” rows found.”);
for (var i=0; i<len; i++){
document.getElementById(“output”).innerHTML +=

“<div class=’even’><p class=’title’>” + results.rows.item(i).name + “</p>” +
“<img src=’” + results.rows.item(i).photo + “‘ width=’100px’ height=’100px’>” +
“<p class=’caption’>” + results.rows.item(i).caption + “</p>” +
“<p class=’more’><a href=’” + results.rows.item(i).resource + “‘>More</a></p>”;

}

}

// Transaction error callback

function errorCB(err) {
console.log(“Error processing SQL: “+err.code);
}

// Transaction success callback

function successCB() {
var db = window.openDatabase(“Database”, “1.0″, “CARS”, 2000000);
db.transaction(queryDB, errorCB);
}

// Cordova is ready

function onDeviceReady() {
var db = window.openDatabase(“Database”, “1.0″, “CARS”, 200000);
db.transaction(populateDB, errorCB, successCB);
}

</script>
<style type=”text/css”>
body {margin:0; padding:0; background-color: #aaa;}
div#wrapper{width:100%;padding:.2em;}
div.even{max-width:100%;background-color:#fff;font-family:Helvetica, Arial, sans-serif; color: #333; text-align:center;margin:1em;padding:1em;
-webkit-border-radius: 5px;
-moz-border-radius: 5px;
border-radius: 5px;
-webkit-box-shadow: 5px 5px 4px 0px #888; /* Safari 3-4, iOS 4.0.2 – 4.2, Android 2.3+ */
box-shadow: 5px 5px 4px 0px #888; /* Opera 10.5, IE9, Firefox 4+, Chrome 6+, iOS 5 */
border:1px #666 solid;
}
p{text-align:left;}
p.title {font-weight:bold;font-size:1em;text-align:center; color:#000}
p.more {font-size:.8em;text-align:center;font-style:italic;}
a {color:#666}
img {width:150px;-webkit-border-radius: 5px;
-moz-border-radius: 5px;
border-radius: 5px;
-webkit-box-shadow: 0px 0px 6px 6px #ddd; /* Safari 3-4, iOS 4.0.2 – 4.2, Android 2.3+ */
box-shadow: 0px 0px 6px 6px #ddd; /* Opera 10.5, IE9, Firefox 4+, Chrome 6+, iOS 5 */
}
</style>

</head>
<body onload=”onDeviceReady()”>

<div id=”wrapper”><span id=”output”></span></div>

</body>
</html>

Note:

Someone populated a DB outside of PG and inserted it into a PG iOS product. Here are the steps he took: https://groups.google.com/forum/?fromgroups=#!topic/phonegap/ymyF99bDl2w

About these ads

6 thoughts on “Prepopulated Database using PhoneGap Storage API, Cordova 2.0.0

  1. Hi, thanks for your example. it works great. But there is one thing i would like to know: How can i save the database after population?

      • but since web database has been dropped from html5 specs back in 2010, this might perhaps eventually not work anymore maybe. :)

      • The data is stored on an html page, not a user-generated web database, so those concerns do not apply. This is not an HTML5 database or local storage. Just as you can’t lose your html page content in an app, you can’t lose this page. “Saving” the html page “saves” the database. :)

  2. Hi you showed that all javascript and html all in one page, if i am calling that javascript in another js file to my html means how to display the records

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s