Skip to content

March 12, 2012

PhoneGap’s Local App Storage

by Colin

With Storage you can store application data similar to mySQL database tables.

In this tutorial you will learn how to create a table and insert data into it. For this tutorial you should have some basic mySQL knowledge.

This tutorial was created on a machine running Ubuntu 11.10, using Aptana Studio 3 as the development IDE and tested on a HTC Desire device running Android 2.2.

End result:



Download the sources, unzip and let’s begin.

First of all I would like to mention that Nitobi was recently acquired by Adobe and PhoneGap is now part of The Apache Software Foundation. This means that the PhoneGap project will remain open source so this is great news for us, web developers. At the moment there is no news about the transition to the Apache Foundation from Nitobi, the creatos of PhoneGap and neither from Adobe. Anyway this doesn’t affect our work, we can still use PhoneGap freely but I just wanted to let you know about what’s going on from what I know so far.

Also with this transition there are some minor changes that need to be done to our project in order to setup PhoneGap corectly.

First you need to edit the App.java class and remove import com.phoengap.*; and add import org.apache.cordova.DroidGap; as in the image below:

Next you need to replace in the AndroidManifest.xml file the name of the activity tag instead of android:name="com.phonegap.*" we will have android:name="org.apache.cordova.DroidGap" check with the image below:

The HTML:

[html]
<!DOCTYPE html>
<html>
<head>
<title>Database</title>

<!– jQuery Mobile Setup –>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="libs/jquery.mobile-1.0.1/jquery.mobile-1.0.1.min.css" />
<script type="text/javascript" charset="utf-8" src="libs/jquery-1.7.1.min.js"></script>
<script type="text/javascript" charset="utf-8" src="libs/jquery.mobile-1.0.1/jquery.mobile-1.0.1.min.js"></script>
<!– PhoneGap Setup –>
<script type="text/javascript" charset="utf-8" src="libs/cordova-1.5.0.js"></script>

<link rel="stylesheet" href="css/style.css" />
<script type="text/javascript" charset="utf-8" src="js/script.js"></script>
</head>
<body>
<div data-role="page" id="home">
<div data-role="header" data-position="fixed">
<h1>DigitalNoiz</h1>
</div>
<div data-role="content">
<p>Database Table Example</p>
<div id="form">
<label for="id">ID:</label>
<input type="number" name="ID" id="ID" value="" />
<label for="name">Name:</label>
<input type="text" name="name" id="name" value="" />
<fieldset data-role="controlgroup">
<legend>Gender:</legend>
<input type="radio" name="radio-choice-1" id="radio-choice-1" value="female"/>
<label for="radio-choice-1">Female</label>
<input type="radio" name="radio-choice-1" id="radio-choice-2" value="male"/>
<label for="radio-choice-2">Male</label>
</fieldset>
<label for="age">Age:</label>
<input type="number" name="age" id="age" value="" />
<a href="#" id="submit" data-role="button" data-theme="b">Submit</a>
</div>
<div id="display">
<ul data-role="listview" data-theme="d">
<li><p class="id">ID</p></li>
<li><p class="name">Name</p></li>
<li><p class="gender">Gender</p></li>
<li><p class="age">Age</p></li>
</ul>
</table>
</div>
</div>
</div>
</body>
</html>
[/html]

Like all of our projects we need to set up our jQuery Mobile and PhoneGap. Recently phonegap reached version 1.5.0 and as you might have noticed I used cordova-1.5.0.js .
After that we set up our page structure, the header with a title and the content with some input forms.
I use the input forms to populate our database. We will have a table containing an id, a name, a gender and an age.

The JavaScript:

[js]
window.addEventListener(‘load’, function(){
// Wait for PhoneGap to load
document.addEventListener("deviceready", onDeviceReady, false);
}, false);

// PhoneGap is ready
function onDeviceReady() {
var db = window.openDatabase("Database", "1.0", "DigitalNoiz", 200000);
var submitBtn = $("#submit");

submitBtn.click(function(){
db.transaction(populateDB, errorCB, successCB);
});
}

// Populate the database
function populateDB(u) {
var setName = $("#name");
var setID = $("#ID");
var setAge = $("#age");
var setGender = $("input:checked");

u.executeSql(‘DROP TABLE IF EXISTS USER’);
u.executeSql(‘CREATE TABLE IF NOT EXISTS USER (id unique, name, gender, age)’);
u.executeSql(‘INSERT INTO USER (id, name, gender, age) VALUES ("’ + setID.val() + ‘", "’ + setName.val() + ‘", "’ + setGender.val() + ‘", "’ + setAge.val() + ‘")’);
}

// Query the database
function queryDB(u) {
u.executeSql(‘SELECT * FROM USER’, [], querySuccess, errorCB);
}

// Query the success callback
function querySuccess(u, results) {
var len = results.rows.length;
//console.log("USER table: " + len + " rows found.");
for (var i=0; i<len; i++){
var getID = results.rows.item(i).id;
var getName = results.rows.item(i).name;
var getGender = results.rows.item(i).gender;
var getAge = results.rows.item(i).age;

var id = $(".id");
var name = $(".name");
var gender = $(".gender");
var age = $(".age");

id.replaceWith("<p>" + getID + "</p>");
name.replaceWith("<p>" + getName + "</p>");
gender.replaceWith("<p>" + getGender + "</p>");
age.replaceWith("<p>" + getAge + "</p>");

//console.log("Row = " + i + " ID = " + results.rows.item(i).id + " Name = " + results.rows.item(i).name + " Gender = " + results.rows.item(i).gender + " Age = " + results.rows.item(i).age);
}
}

// 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", "DigitalNoiz", 200000);
db.transaction(queryDB, errorCB);
}
[/js]

First of all in the onDeviceReady() function I create a database using the window.openDatabase(name, version, display_name, size); method and I want to add some fields into my database.

When the Submit button is pressed I want to manipulate the database in order to insert data into my fields, this is done by db.transaction(populateDB, errorCB, successCB);. The transaction method allows us to execute SQL statements to the database.

We can do database transactions by calling the executeSql method as we have in the populateDB() function.

In our example we create a table called USER which has an id set to unique like we used to do in mySQL databases, it also has a name, gender and an age.

I retrieved the data set in our input forms and displayed them in a list view. In order to do this in the queryDB() function I made a select statement query to the database and retrieved all the data it contains and displayed it with the querySuccess callback function.

[js]
// Query the database
function queryDB(u) {
u.executeSql(‘SELECT * FROM USER’, [], querySuccess, errorCB);
}
[/js]

In the querySuccess function I used .replaceWith() method from jQuery to repopulate the list view with the data from our database.

That’s about it for now. Good luck with your development!

Read more from Mobile

Leave a Reply

required
required

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments