May 12

PhoneGap-0.9.5 webOS no longer includes the Mojo framework, so you will not be able to use the Depot APIs to create & manipulate local storage. If your webOS app needs a database, you will need to use HTML5′s storage functionality.

If you’re already familiar with HTML5 storage and just want to reference the storage APIs supported in PhoneGap, you can take a look at the PhoneGap Storage API docs.

This post will provide a step by step walkthrough on how to:

  • set up an HTML5 SQLite database
  • insert data into the database
  • retrieve data from the database

First step is to set up your PhoneGap webOS project by following these steps.

Create / Open a database connection

To create a new database or to open a connection to an existing database you need to use the window.openDatabase method.

1
var db = window.openDatabase(name, version, displayName, size);

The parameters used by the openDatabase method:

  • name – name of the database instance
  • version (optional) – version of the database
  • displayName (optional) – database’s display name
  • size (optional) – size of the database in bytes

Modify your index.html file’s source so that it looks like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>PhoneGap WebOS</title>
 
	<script type="text/javascript" src="phonegap.js"></script>   
	<script type="text/javascript">
                var db; 		
 		function onLoad() {
	    	     navigator.device.deviceReady();
		} 
 
		// create the db instance
		function createDB() {  
			// creates or opens a new db connection
			db = window.openDatabase("test", "1.0", "Test DB");
		}		
	</script>
</head>
<body onload="onLoad();">
	<input type="button" onclick="createDB();" value="Test SQLite DB">
</body>
</html>

When “Test SQLite DB” button is pressed it will create a database named “test” (or open a connection to the “test” database if it already exists) by calling the createDB() method which in turn calls the window.openDatabase method. The app will appear to do nothing if you run it and press the button since we have not added any notifications to indicate the result of the database creation.

In the next section, we will insert some test data into the database and display a notification that will indication if the database creation & data insertion was successful.

Inserting data into the database

To perform any CRUD (Create/Read/Update/Delete) operaton on the database you will need to use the database’s transaction method.

1
db.transaction(methodName, errorCallback, successCallback);

The transaction method accepts the following parameters:

  • methodName – name of the method that contains the SQL statements you want to execute
  • errorCallback (optional) – name of the method that gets called if an error occurs when executing the instructions in methodName
  • successCallback (optional) – name of the method that gets called when the instructions in methodName are executed successfully

The transaction object which gets passed into the methodName method as a parameter has an executeSql method which is used to execute SQL statements. In our example below, our methodName is the populateDB method.

1
tx.executeSql(sqlStatement, argumentArray, successCallback, errorCallback);

The executeSql method accepts the following parameters:

  • sqlStatement – SQL statement that needs to be performed on the database
  • argumentArray (optional) – values that can be mapped to SQL arguments represented by ‘?’ in the sqlStatement
  • successCallback (optional) – method to be called if the executeSql method successfully executes
  • errorCallback (optional) – method to be called if the executeSql method isn’t successful

Update your index.html so that the source matches the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>PhoneGap WebOS</title>
 
	<script type="text/javascript" src="phonegap.js"></script>   
	<script type="text/javascript"> 		
 		function onLoad() {
	    	navigator.device.deviceReady();
		} 
 
		// create the db instance
		function createDB() {  
			// creates or opens a new db connection
			var db = window.openDatabase("test", "1.0", "Test DB");
			// call populateDB
			db.transaction(populateDB, errorCB, successCB);			
		}
 
		// inserts test data into db
		function populateDB(tx) {
			// drop the DEMO table if it exists
			tx.executeSql('DROP TABLE IF EXISTS DEMO');  
			// create DEMO table with columns id and data
			tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
			// insert test data into DEMO table
			tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
			tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
		} 
 
		// generic error handler
		function errorCB(tx, err) {
			navigator.notification.alert("error: " + err);
		}
 
		// success handler for db creation & population
		function successCB() {
			navigator.notification.alert('successfully created & populated');  
		}		   			
	</script>
</head>
<body onload="onLoad();">
	<input type="button" onclick="createDB();" value="Test SQLite DB">
</body>
</html>

If you run your app now & press on the “Test SQLite DB” button, you will get an alert that indicates that the database was successfully created and that data was successfully inserted into your database.

Database creation & insertion success

Now that we’re able to insert data into the database, we’ll want to be able to retrieve the data from the database.

Retrieving data from the database

These last updates will add a method to retrieve all of the test data we have entered into the database. There will be notifications that display the total number of rows added to the database and the contents of each row.

We will be adding a method called ‘queryDB’ that will retrieve all of the data from the database and a method named ‘querySuccess’ which will display the notifications when if the data retrieval is successful.

Update your index.html so that the source matches the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
<html>
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
	<title>PhoneGap WebOS</title>
 
	<script type="text/javascript" src="phonegap.js"></script>   
	<script type="text/javascript"> 		
 		function onLoad() {
	    	navigator.device.deviceReady();
		} 
 
		// create the db instance
		function createDB() {  
			// creates or opens a new db connection
			var db = window.openDatabase("test", "1.0", "Test DB");
			// call populateDB
			db.transaction(populateDB, errorCB, successCB);			
		}
 
		// inserts test data into db
		function populateDB(tx) {
			// drop the DEMO table if it exists
			tx.executeSql('DROP TABLE IF EXISTS DEMO');  
			// create DEMO table with columns id and data
			tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
			// insert test data into DEMO table
			tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
			tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
		} 
 
		// generic error handler
		function errorCB(tx, err) {
			navigator.notification.alert("error: " + err);
		}
 
		// success handler for db creation & population
		function successCB() {
			navigator.notification.alert('successfully created & populated'); 
			// call queryDB
			db.transaction(queryDB, errorCB); 
		}
 
		// query db for all values in the DEMO table
		function queryDB(tx) {
			tx.executeSql('SELECT * FROM DEMO', [], querySuccess, errorCB);
		}
 
		// display results of a success db query
		function querySuccess(tx, results) {     
			var len = results.rows.length;
			// display alert with number of rows inserted into the db
			navigator.notification.alert('rows inserted: ' + len);
 
			// display each item in the recordset in its own alert
			if (len > 0) {
				for (var i=0;i<len;i++) {
					navigator.notification.alert('id: ' + results.rows.item(i).id + ' data: ' + results.rows.item(i).data);
				}
			}
		}		   			
	</script>
</head>
<body onload="onLoad();">
	<input type="button" onclick="createDB();" value="Test SQLite DB">
</body>
</html>

The source code for this demo can be found here.

7 Responses to “Using SQLite with PhoneGap-0.9.5 webOS”

  1. digitalelph Says:

    All of the tutorials are great, thanks for your work! There’s one error in your last code snippet that prevents it from running correctly — var db needs to be defined outside of CreateDB() to successfully query the database. You have it written correctly in the source code, just not in the code snippet.

  2. Herm Wong Says:

    Thanks for pointing out the error in the code snippet, the error has been fixed.

  3. prabhakar Says:

    your tutorials are good, Great job.I want to retrieve only one row ,
    ie, ‘select id from DEMO where id=”20″ ‘ like that ….
    Which cursor i have to use to display data.

  4. Herm Wong Says:

    prabhakar, to retrieve a specific row in the database you would need to modify the queryDB method. It would probably need to look like this:

    function queryDB(tx) {
    tx.executeSql(‘SELECT * FROM DEMO WHERE id = ?’, [idToFind], querySuccess, errorCB);
    }

    The idToFind would be an external variable that you would have to set before the queryDB method is called.

  5. Luke Says:

    I would like to point out that I had to NOT set the db size when creating the db variable. If I did something like 5*1024*1024 it would never get created.

    This was in the simulator though.

  6. Herm Wong Says:

    Hi Luke, the size parameter is optional – according to the W3C’s HTML5 storage specifications the max size of the local storage is 5MB.

  7. ratheesh Says:

    thnks its a good tutorial

Leave a Reply