HTML5 Web Database and Mobile Web Applications

The Web SQL Storage (based on SQLite) API in HTML 5 is ideal for Mobile Web applications that require a complex data storage support and that work mostly offline. The Web storage remains locally on the device, and it is persisted even after the application or browser is closed. The implementation of the data access is all done using JavaScript and the SQL script format is compliant with the SQLite specifications.

To create or open a database, the following script can be used:

var data=null; //connection handler

if (typeof (window.openDatabase) != 'undefined' && window.openDatabase) {
    var maxSize = 5 * 1024 * 1024; // in bytes = 5MB
    //name,version,displayName,maxsize to identify the app database
    data = window.openDatabase('appName', '1.0', 'appName_db', maxSize);
    if (data) {//transaction is open
       data.transaction(function (tx) {  $(tables).each(function () { tx.executeSql(this); });  });

The script above checks the openDatabase property from the windows object. If the value is true, there is Web Storage support, and we can move forward with the API calls to open the database. The openDatabase call opens the connection to the database. If the database does not currently exists, the database gets automatically created, but with no tables. The parameters to open the database uniquely identify the database and its size. Once we have a valid handle to the database, we can use a transaction object to run a SQL statement and build our tables using the executeSql method. This method is also used to execute other SQL statements on the database. The SQl statement to build the tables checks to see if the table does not exists before creating.

CRUD Methods:

To insert, update, and delete a record, we just need to use the following scripts:

Execute: function (sql, params, dataHandler, errHandler) {
   if (data) {//transaction is open
      data.transaction(function (tx) {tx.executeSql(sql, params, dataHandler, errHandler);});
ErrorHandler: function (tx, err) {
    alert('DbError: ' + err.message + ' (Code ' + err.code + ')');
   return false;
DataHandler: function (tx, data) {
  if (typeof (data) != 'undefined') {
       if (data.rows.length == 0) {
            //TODO CHECK FOR rowsAffected,insertedId FOR updates,deletes, inserts.
      else if (data.rows.length > 0) { //selects
          for (i = 0; i < data.rows.length; i++) {
              var row = data.rows.item(i);
             var data = [row['id'] ,row['notes'], row['date']] //sets value in array

The execute function executes a SQL statement by calling the transaction executeSql method. This method takes the SQL statement, a parameters array and handlers for successful or failed responses. The DataHandler manages the return recordset (data parameter) or rows affected from an update, delete and insert statement. The data.rows collection is populated when a select statement is executed. For an insert on a table with an identity column, the data.insertedId property is set to the new identity id. The DataHandler method is where the recordset can be iterated to populate the UI with the data.

The CRUD statements look as follows:

sqlIns: 'INSERT INTO mynotes(notes,date) values(?,?)',
sqlUpd: 'UPDATE mynotes SET notes=?,date=? WHERE id=?',
sqlDel: 'DELETE mynotes WHERE id=?',
sqlSel: 'SELECT * FROM mynotes WHERE id=?'

You should note the question marks. These are the parameter variables that are matched to the values in the params array of the Execute function. For example, to insert a new record, use the following:

var params = [‘my notes here’,’02/20/2011’];
Execute(sqlIns, params,DataHandler,ErrorHandler);

The browsers that support this feature have an option to allow you to see the resources which include web databases. For instance. Google Chrome (menu Tools->Developer Tools) provides the ability to see tables and data in the database. It also provides an interface with you can use to execute queries on your database.

With the ability to open a database and execute SQL statements against the database, simple data providers could be implemented to provide data support for your mobile applications. This can be handy for disconnected mode support in mobile applications.

I hope this is helpful.



Resolve MSB4064: The "Retries" parameter is not supported by the "Copy" task – Team Build

When building Visual Studio 2010 projects on TFS 2008, you may get this error:

MSBuild\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets(132,11): error MSB4064: The "Retries" parameter is not supported by the "Copy" task. Verify the parameter exists on the task, and it is a settable public instance property.

MSBuild\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets(130,5): error MSB4063: The "Copy" task could not be initialized with its input parameters.

The item to notice is that Visual Studio 2010 projects by default use the MSBuild V10.0 targets, and the target definitions are updated for TFS 2010 build projects not TFS 2008. A simple way to address this is to add a condition on the project file to use the Visual Studio 2008 build targets instead when the build is not done with Visual Studio. This is done by editing the Visual Studio project file and updating the following entry:

<Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" />

To the following:

<Import Condition="'$(IsDesktopBuild)' == 'true'" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v10.0\WebApplications\Microsoft.WebApplication.targets" />

<Import Condition="'$(IsDesktopBuild)' == 'false'" Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\WebApplications\Microsoft.WebApplication.targets" />

The IsDesktopBuild variable is true when the build is done on Visual Studio. When is false, it is a build done over the command line. For this case, we tell the project to use the v9.0 targets instead.
After making this change, you may now be getting this error:

error MSB4131: The "Reason" parameter is not supported by the "GetBuildProperties" task. Verify the parameter exists on the task, and it is a gettable public instance property.

You can add a property to the SolutionToBuild node as follows:( modify the MSBuild project file):

<SolutionToBuild Include=...>
I hope it helps



Serialize JSON Object to String

A simple way to serialize a JSON object is to iterate through the object’s property and create a JSON formatted string. All the properties and functions in a JSON object can be read just like an associative array or a name/value pair. This allows us to list all the keys and query the object for the values using the keys.  Look at the following script:
var myJSON = {
    FirstName: '',
    LastName: '',
    Email: '',
    load: function () {
        //implementation here
    serialize: function () {
        var json = "";
        for (var key in this) {
            if (typeof this[key] != 'function') {
                json += (json != "" ? "," : "") + key + ":'" + this[key] + "'";
        json = '[{' + json + '}]';
        return json;

The serialize function uses a for loop to get all the keys in the object. It checks if the type is not a function because, for this case, we just want to extract the user data values and ignore the functions. You could also extract the contents of the function by just removing the if condition. To build the formatted string, the function concatenates a string with the key/value pair until it reads all the keys. The following sample script sets the property values and serializes the data to a string:

//client code to set properties and serialize data
if (typeof (myJSON) != 'undefined') {
    myJSON.FirstName = "myfirstname";
    myJSON.LastName = "mylastname";
    myJSON.Email = "myemail";
    var data = myJSON.serialize();

The data variable contains a string with this format:
I hope this helps.