Part 2 - AppForum Dev Lab - WebSQL

So far we have a basic HTML file (our app) that has placeholders for content and simple javascript functions for enabling the scanner and handling the decode event. In this part of the series, we will discuss WebSQL storage and incorporate that into our application. Before we start modifying our HTML, let's discuss how the database will be laid out.

We will be creating just one table with the following fields:

To help separate the code, let's create a new javascript file: db.js. In this file we will create javascript functions to take care all of the database work.

The first function we will be to initialize the database, let's call the function dbInit(). In this function we will first need to open the database:

db = openDatabase('EventTracker', '1.0', 'Attendee Tracker', 4 * 1024 * 1024);

The openDatabase method has the following arguments in their respective order:

• Name of the database you want to create and open. If the database has already been created, the openDatabase method just open the database. In this case we are calling the database EventTracker
• Version of the database to open. The version number is required to open the database and this number has to match the database version that the client use.
• Textual description of the database
• Size of the database to create. This is the estimated size of the database. It does not automatically consume this size when the file is created.

When this line of code is run, a SQLite database file will be created on the device. The Config.XML defines where the file will be located and what the quota of the databases can be. If you look at the config.xml file again you will see a section. In it you will see the storage path and maximum quota. Make sure your openDatabase statement does not exceed the quote.

Now that we have a reference to the database that we created, we can start executing transactions to  create the table that we will need. All SQL statements need to happen inside a transaction. A transaction is considered a set operations that acts like a single operation. If for some reason, it fails during the transaction the database will rollback to the previous state before the transaction. Essentially you call the transaction method of the database object. The transaction object takes three arguments. The first one is the transaction callback. The second is the error callback and the last one is the success callback. The last two options ate optional and for the sake of this example, I am leaving those out:

In the transaction callback I have an attribute, tx, which is a SQLTransaction object. I will now use the executeSql method of this object to create the table sessionAttendees:

The executeSql method is the following: executeSql(sqlStatement, arguments, callback, errorCallback)

• sqlStatement: is the SQL statement to perform on the database. This argument is mandatory, all the following is optional.
• arguments: The sqlStatement argument can contain SQL arguments represented by question marks '?'. These question marks are then mapped to values contained in the arguments argument which is an array of values.
• callback: success callback function
• errorCallback: error callback function

db.transaction(function(tx)
          {
                    // This is where the SQl statements go
                    //  Create the table to hold the data for the session attendees
                    // this table will have a record for each attendee of each session
                    tx.executeSql("CREATE TABLE sessionAttendees (sessionId TEXT,scanData TEXT)", [],
                    function(tx)
                    {
                              //do nothing on success
                    },
                    onError);
          });

In this case my sqlStatement is "CREATE TABLE sessionAttendees (sessionId TEXT,scanData TEXT)". This follows standard SQLite syntax. For this example the table name is sessionAttendees and the field names are sessionID and scanData. The value "TEXT" next to the field name indicates the type of field it is. For a complete list of this syntax please refer to http://www.sqlite.org. I am not passing any SQL parameters or doing anything upon success of creating the table and I am calling a generic javascript function, onError, that I will create to log errors to the RhoElements log file.

function onError(tx, error) 
{
          generic.Log(error.message,1); //log error to the RhoElements log file
}

Your complete dbInit() function should now look like:

function dbInit()
{
          //Create the Database called Events with a estimated size of around 5MB
          db = openDatabase('EventTracker', '1.0', 'Attendee Tracker', 4 * 1024 * 1024);
          db.transaction(function(tx)
          {
                    // This is where the SQl statements go
                    //  Create the table to hold the data for the session attendees
                    // this table will have a record for each attendee of each session
                    tx.executeSql("CREATE TABLE sessionAttendees (sessionId TEXT,scanData TEXT)", [],
                    function(tx)
                    {
                              //do nothing on success
                    },
                    onError);
          }); 
}

Before we test that we have the basics of the WebSQL setup let's add an onLoad function to the Body tag of the index.html file and call the dbInit() function.

Let's also make sure to link the javascript files we just created by adding script tags to the section

Copy all three files to the device and launch RhoElements:

After you launch RhoElements, you should see the same placeholder text come up. It looks the same, what happened? Well the only thing we did was to create the database file. We have not done anything else. Let's verify that the file was actually created. Navigate to the install path of RhoElements. On my device it was \Program Files\RhoElements in that folder you should now see a folder called file__0 and in that folder you will see a file that has an extension of .db. That is your database that was just created.

Copy it over to your computer and you will be able to open it with the SQLite client that was on the USB stick. When opening the file you should see that there was a table that was created with the field names that we specified.