RhoElements - Building Your First App - Part 4 WebSQL Setup

In the of the , 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 two tables with the following fields in each table:

Table Name	Fields
eventSessions	sessionID: ID Number of the session, Unique title: Session title room: Room Name speaker: Name of presentor displayTime: Time of Session to be displayed attendees: Field to keeo track of number of attendees for quick display
sessionAttendees	sessionID: ID Number of the session attending scanData: Data of barcode scanned (this will be complete QR Code that has the vCard data string

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:

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 two tables 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

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.

We will also create the eventSessions table using a similar set of statements. In this section, we will be calling a function we will create that is called: dbInsertEventData(). This function will load the event data that will be stored in a separate file into the eventSessions table. We do this on the success callback of the create table transaction, because we just want to do it the first time the table is created. The idea is that the developer conference sessions information will be stored in a text file and distributed with the application (making the app portable to other conferences). When the application first loads it takes the details in the text file and inserts thev individual session information into the eventSessions table. Then when attendees are scanned into the sessionAttendees table, the final database file will have all the detail that is needed to display data or run reports on the conference breakout sessions.

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', 5000000);           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);                     //Create the eventSessions table                     tx.executeSql("CREATE TABLE eventSessions (sessionId TEXT,title TEXT,room TEXT, speaker TEXT,displayTime TEXT,attendees INTEGER)", [],                     function(tx)                     {                               dbInsertEventData();                     },                     onError);           }); }

Now let';s save this db.js file and create another javascript file called data.js. In it we will define a simple variable called eventData that will be an array of objects that define the breakout session detail.

The variable will be defined like:

var eventData =[

                    { sessionId: "", title:"", room:"", speaker:"", displayTime:"",sortTime:"",attendees:"0"},

  { sessionId: "", title:"", room:"", speaker:"", displayTime:"",sortTime:"",attendees:"0"},

  { sessionId: "", title:"", room:"", speaker:"", displayTime:"",sortTime:"",attendees:"0"},

...

};

Where of course we will have actual data inside each of the quotes above for the specific field information.We will use this variable to insert records into the eventSessions table.

Put this file aside for now and let's get back to the db.js file since we still need to create the function dbInsertEventData(). For this function we will basically just loop through the eventData array and call another function called dbInsertEventSession().

function dbInsertEventData() {           //loop through eventData array           for (i=0;i                     {                               eventSession = eventData[i]                               dbInsertEventSession(eventSession);                     } }

For the function dbInsertEventSession(), we will call a similar db.transaction statement but with a different SQlite statement. This time we will use an insert statement. It follows the same format for the transaction, but the SQL statement is somewhat different. In this case we are passing parameters into the SQL statement. You indicate the parameter in the SQL statement with a "?" then you put the corresponding value or javascript variable in the argument array. Make sure you have the same number of question marks as you do parameters in the parameter array. Which is why it is a good reason to include the errorCallback function to catch this.

function dbInsertEventSession( e) {           db.transaction(                     function(tx)                     {                               tx.executeSql("INSERT INTO eventSessions (sessionId,title,room,speaker,displayTime,attendees) VALUES (?,?,?,?,?,?)", [ e.sessionId,e.title,e.room,e.speaker,e.displayTime,0],                               function(tx, result)                               {                               },                               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:

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 any database client that can read SQLite databases (Here is a free one).

Now the basic database functions are created for setting up the tables and initial data. In the we will walk through how to insert the scanned in bar code data into the second table to track attendee information.