webOS and the HTML 5 Database API

webOS and the HTML 5 Database API - Revisited

Monday, 01 June 2009

Way back before most people had heard of the Pre or webOS, we published an article that covered HTML 5 local database storage capabilities that would be included with webOS.

Palm has now published some more specific information about how HTML5 database storage will be implemented in webOS, and it's quite similar to what we had originally anticipated. HTML 5 is so new that the database API is still in draft spec and subject to change. It seems that Palm is intent on conforming to the HTML 5 Database API spec as closely as possible and recommends checking it for the latest database capabilities of webOS:

http://dev.w3.org/html5/webstorage/#databases

We previously covered the use of Mojo cookies and Depot for local data storage. The HTML 5 Database API should be used when your application's needs exceed the capabilities of these simpler functions.

The HTML 5 local storage spec includes support for two objects: Database and Storage. The latter evolved from Firefox's DOM Storage, and is not supported in webOS. webOS uses the Database object.

The openDatabase() method creates a new database or open an existing database, returning the database object opened or created.

db = openDatabase(name:"myDB", version:"1", displayName:"My DB", estimatedSize"10000");

The arguments are each a property/value pair:

Property	Required	Description
Name	Required	Database name
version	Optional	Target version, or undefined if any version is acceptable
displayName	Optional	Application defined, not used by webOS
estimatedSize	Optional	Informs webOS of intended size to prompt for any system constraints at creation rather than during use

Palm notes that the database version was intended to represent the schema version, facilitating smooth migration of the database through schema changes. If the application specifies a database name and version number, both need to match for the database open to succeed. If the database doesn't exist, it will be created.

After the database is opened, you can execute transactions against it, using transaction() for read/write or readTransaciton() for read-only transactions.

The methods specify one to three callbacks:

Transaction callback
Error callback
Success callback

The transaction callback includes the transactions steps you want to execute using an executeSQL() method, which accespts an SQL query string as an argument along with success and error callbacks.

Here is an example in which the transaction method is called with a literal function that includes two executeSQL() methods. The second method specifies a success callback, this.successHandler(), and an error callback, this.errorHandler():

   MyAssistant.prototype.activate = function()    { 
   . 
   . 
   . 
   db.transaction( (function (transaction) { 
   transaction.executeSql('A BUNCH OF SQL', []); 
   transaction.executeSql('MORE SQL', [], this.successHandler.bind(this), 
   this.errorHandler.bind(this)); 
   }).bind(this); 
   . 
   . 
   . 
   MyAssistant.prototype.successHandler = function(transaction, SQLResultSet) { 
   // Post processing with results, which includes 
   }; 
   MyAssistant.prototype.errorHandler = function(transaction, error)  { 
   Mojo.Log.Error('An error occurred',error.message); 
   // Handle errors here 
   };

The success handler is passed the transaction object plus an SQLResultSet object as an
argument. The attributes of the results object are:

Attributes	Description
insertID	Row ID of the row that was inserted into the database, or the last o multiple rows, if any rows were inserted
RowsAffected	Number of rows affected by the SQL statement
SQLResultSetRowList	Rows returned from query, if any

At time of writing, the current database size limit is 5MB, which is based on the HTML 5 Draft Recommendation.

That's all Palm has said so far on HTML5 Database capabilities, which really only covers the basics of the API. Refer to our previous HTML5 database tutorial or the draft spec for more detail.

Next, we'll cover AJAX data transfer in webOS applications.

This is one of many (not quite) daily development-related webOS articles. Grab the RSS feed to stay in the know!

Much of the information in this article was presented in Chapter 6 of Palm webOS by Mitch Allen.

4 Comments

Feed

andrefy makes this comment

Saturday, 29 August 2009
Hi, thanks, that is a really useful article. I was wonder if you know how the scope in the successHandler works.

I want to populate an array using the SQLResultSet, I use the next code:

First populate and assistant property
```
MyAssistant.prototype.successHandler =
        function(transaction, SQLResultSet) { 
                this.data = SQLResulSet;         
   };
```
But the problem is that I can access the value of this.data outside of the successHandler

I am bind (this) at the active function at same way that in your example.

Populate a List Widget
I try the next code to populate at the successHandler
[code=html]
MyAssistant.prototype.successHandler =
function(transaction, SQLResultSet) {
/*populate list widget*/
this.timesList = [];

for(i=0; ind and I don't see what else I can do.

Basically a want to store the SQLResult at some variable that I could use later,

Thanks,

andrefy makes this comment

Saturday, 29 August 2009

It seems that I don't paste well the second code

MyAssistant.prototype.successHandler =
        function(transaction, SQLResultSet) { 
                this.timesList = [];    
                
                for(i=0; iis.attributes = {
              itemTemplate: 'list/listitem',
              listTemplate: 'list/listcontainer',
              addItemLabel: $L('Add ...'),
              swipeToDelete: true,
              reorderable: true,
              emptyTemplate:'list/emptylist'
         },
         this.timesList = {
             listTitle: $L('List '),
             items : this.timeList
         });

 };

Amit makes this comment

Thursday, 24 September 2009

I am unable to use readTransaciton on the database, it gives an error.

Object # has no method 'readTransaciton', line undefined,

if i use use the transaction it works fine, but in few casees i am only reading a row from database , so i want to use readTransaction.
Paul makes this comment

Friday, 17 September 2010

If you need access to your async results outside of the callback, you have to overcome two issues: (1) where to store the results (hopefully not the global namespace), (2) timing/race conditions created by the aysnc nature of the database.

I solved both problems with my library, proto-q: http://code.google.com/p/proto-q/

In my previous attempts, I used the same Active Object pattern but I saved the results to a standard application namespace variable.

Basically, every callback was passed a variable name so the results would be added to MyApp[name] = {rows:{...}}. On a small app, this is easy to maintain. By contrast, my new library saves the results with the object that originated the call. This is easier for me to keep straight on larger projects.