How to make a WebSQL query synchronous?

Consider:

var globalvar;

function viewyearmain() {
  db.transaction(function (tx) 
  {
    tx.executeSql('SELECT * FROM BUDGET WHERE holdingtype="month"', [], function (tx, results) 
    {
       var len = results.rows.length;
       msg = len;
       globalvar = msg;
    }, null);

  });

  if (globalvar>0)
  {
    alert("ROWS FOUND");
  }
  else
  {
    alert("ROWS NOT FOUND");
  }
}

The problem is that ROWS NOT FOUND appears because the transaction has not completed by the time the if statement is reached.

Answers


An asynchronous callback is not synchronous, regardless of how much you want it to be.

Just move all the code the depends on the result into the callback:

var globalvar;

function viewyearmain() {
  db.transaction(function (tx) 
  {
    tx.executeSql('SELECT * FROM BUDGET WHERE holdingtype="month"', [], function (tx, results) 
    {
       var len = results.rows.length;
       msg = len;
       globalvar = msg;
       if (globalvar>0)
       {
         alert("ROWS FOUND");
       }
       else
       {
         alert("ROWS NOT FOUND");
       }
    }, null);

  });
}

Alternatively, move it into a second function, and call that from the callback.


To my knowledge, WebSQL does not support synchronous SQL statements. This is normally a good thing, as you do not want the processing of SQL to disrupt or freeze your User Interface. As such, CL.'s answer provides the right asynchronous mechanism for processing results to a query.

If, however, you truly desire synchronous SQL queries, then check out the WebSQL alternative: SequelSphere - An HTML5 / JavaScript SQL Relational Database.

It is 100% JavaScript, so it runs in any browser and on any platform. Plus, it stores its data in either IndexedDB or LocalStorage. It also contains many bells and whistles that WebSQL does not: JSON Integration, Change Trackers, User-Defined SQL Functions, SYNCHRONOUS SQL processing, etc. Since the WebSQL standard has been deprecated, I think this is an excellent alternative.

Full Disclosure: I love WebSQL, but am married to SequelSphere.


It's a bit late now but for what it's worth...you can't make the calls synchronous but you can simplify your code by using a library such as Async. It might seem like overkill but if you need to perform 3 or 4 statements in a row it can make your code a lot easier to read.

async.waterfall([
    function(callback){
        db.transaction(function (tx) {
            tx.executeSql('SELECT * FROM BUDGET WHERE holdingtype="month"', [], 
                function (tx, results) {
                    var len = results.rows.length;
                    callback(null, len)
                }, 
                function(){
                    callback("An error occurred when reading data");
                }
            }); 
       });
    },
    function(len, callback){
        // Now do something with the length.
        console.log("The length is: " + len);
    }
]);

I'm a couple of years late, but considering that the question was never directly answered, I figured I'd throw my two cents in, as well ass add some suggestions!

First, if you're reading this, you probably shouldn't be using WebSQL. It has been deprecated in favor of IndexedDB, which at this point is the only database on the W3C standards track.

If, for whatever reason, you're intent on using WebSQL, and you can live without the benefits that its asynchronous API offers (some of which are mentioned in John Fowler's answer), then you should know that its spec also defines a synchronous API.

So yes, there is a way to execute statements in WebSQL synchronously, provided the browsers you are developing for have implemented the synchronous API.

If you don't mind dealing with an asynchronous interface that is just about as simple as a synchronous one, check out BakedGoods.

With it, executing your query is as simple as:

bakedGoods.getAll({
    filter: "valueObj.holdingType === 'month'",
    storageTypes: ["webSQL"],

    //Contains database configuration data and operation preferences
    options: optionsObj,

    complete: function(byStorageTypeResultDataObj, byStorageTypeErrorObj){}
});

Its simple interface and unmatched storage facility support comes at the cost of lack of support for some storage facility-specific configurations. For instance, it does not support the conduction of storage operations in WebSQL tables with multi-column primary keys.

So if you make heavy use of those types of features, you may want to look elsewhere.

Oh, and for the sake of complete transparency, BakedGoods is maintained by yours truly :) .


Need Your Help

Cakephp, JQuery JCarousel Lite, Error

javascript jquery cakephp jcarousel

I am using the following code to make an unordered list into a carousel with jcarousel lite and jquery:

matplotlib upper limits inverted

python matplotlib

I am using matplotlib to make a scatter plot with upper and lower limits. I noticed that my lower limits are plotted with arrows pointing downwards instead of upwards and vice versa.