How to make a WebSQL query synchronous?

Question

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.

Kyphosis

Answer 1

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.

Walkthrough

Answer 2

Nowadays, you can take advantage of async / await and promises like this:

async function alertResult() {
  const rows = await viewyearmain();
  if (rows.length) {
    alert("ROWS FOUND");
  } else {
    alert("ROWS NOT FOUND");
  }
}

function viewyearmain() {
  return new Promise(resolve => {
    db.transaction(function(tx) {
      tx.executeSql(
        "SELECT * FROM BUDGET WHERE holdingtype = ?;", ["month"],
        function(tx, result) { resolve(result.rows); },
        null
      );
    });
  });
}

Eclipse

Answer 3

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 :) .

Kura

Answer 4

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.

Chlortetracycline

Thank you for the information... I have already fixed my code according to CL's idea and its working.. but i will going through the post too.. Thank You @John Fowler.. – Kyphosis

@John Fowler I want to know SequelSphere is support offline or not? – Lunt

@Kanna SequelSphere does support offline (using an app cache manifest file). It stores it's data either in IndexedDB or LocalStorage (in that order, based on what the browser supports). – Chlortetracycline

@JohnFowler: looks like the link you mentioned is dead!? – Caecum

Answer 5

Not quite synchronous - but it's a start.

Create a helper function to execute your queries from within a Promise

const executeSql = (query, params) => new Promise ( (resolve, reject) => {
  tx.executeSql(query, params, resolve, reject);
};

Then when running from an async function, you can use the 'await' keyword

const myFunc = async () => {
   let result = await executeSql("SELECT * FROM BUDGET WHERE holdingtype=?", ["month"]);
}

However you MAY have issues with the result of the transaction as the object may de-reference once the Promise fulfils.

const executeSql = (query, params) => new Promise ( (resolve, reject) => {
    tx.executeSql(query, params, 
        (tx, results) => {
            var items = [];
            if(results.rows.length)
            {
                var max = results.rows.length;
                for (var x = 0; x < max; x++) items.push(results.rows.item(x))
            }
            resolve(items)
        }, reject)
    })

Sinuous

Answer 6

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);
    }
]);

Dry