WebSQL Transaction Won't Run In JS Callback Functions

Question

I am using PhoneGap and jQuery Mobile. I am trying to get some JSON data from a remote location and then populate a local WebSQL database with it. Here is my JS function:

function getLocations() {

    var tx = window.openDatabase('csdistroloc', '1.0', 'Distro DB', 1000000);
    tx.transaction(function(tx) {
        tx.executeSql('DROP TABLE IF EXISTS locations'); //this line works!
        tx.executeSql('CREATE TABLE IF NOT EXISTS locations (id, name, address, postalcode, phone, category)'); //this line works!

        $.ajax({
          url: "http://mydomain.com/api.php",
          dataType: 'json',
          data: { action: "getlocations" },
          success: function(data) {
            tx.executeSql("INSERT INTO locations (id, name, address, postalcode, phone, category) VALUES (2,'cheese','232','seven',5,6)"); //this line produces an error
        }});

    }, dberror, dbsuccess);

}

Running the above function gives me an error "INVALID_STATE_ERR: DOM Exception 11" on the line noted above. It does the same thing when I am actually trying to use the returned JSON data to insert data. I have also tried the $.getJSON technique with the exact same result.

Any advice would be appreciated!

Answer 1

We do have a way to lock the transaction, while you do any AJAX or other async operation. Basically before calling AJAX you need to start a dummy db operation and on success of that operation check if AJAX is done or not, and again call the same dummy operation till your AJAX is done. When AJAX is done you can now reuse the transaction object do next set of executeSQLs. This approach is thoroughly explained in this article here. (I hope someone will not delete this answer too, as someone did earlier on a similar question)

Try this approach

Answer 2

Although the accepted answer is correct, I would like to expand upon it because I encountered the same problem and that answer doesn't say why it doesn't work as the OP had it.

When you create a transaction in Web SQL, the transaction processing remains alive only so long as there are any statements queued up in the transaction. As soon as the pipeline of statements in the transaction dries up, the engine closes (commits) the transaction. The idea is that when the function(tx) { ... } callback runs,

1. It executes all of the statements it need to. executeSql is asynchronous, so it returns immediately even though the statement has not yet been executed.
2. It returns control back to the Web SQL engine.

At this point the engine notices that there are statements queued up and runs them to completion before closing the transaction. In your case, what happens is this:

1. You call executeSql twice to queue up two statements.
2. You request something through ajax.
3. You return

The engine runs the two statements that it has queued up. The ajax request is also running asynchronously but it must access the network which is slow so it likely has not completed yet. At this point, the statement queue is empty and the Web SQL engine decides that it's time to commit and close the transaction! It has no way of knowing that there is going to be another statement coming later! By the time the ajax call returns and it attempts to execute the INSERT INTO locations, it's too late, the transaction is already closed.

The solution suggested by the accepted answer works: don't use the same transaction inside the ajax callback but create a new one. Unfortunately, it has the pitfall you would expect from using 2 transactions instead of 1: the operation is no longer atomic. That may or may not be important for your application.

If atomicity of the transaction is important for you, your only 2 recourses are:

• Do everything (all 3 statements) in one transaction inside the ajax callback.

  This is what I recommend. I think it's very likely that waiting until after the ajax request completes before creating the table is compatible with your application requirements.

• Perform the ajax request synchronously as explained here.

  I don't recommend that. Asynchronous programming in JavaScript is a good thing.

By the way, I encountered the problem in the context of Promises, in code that looked something like this:

// XXX don't do this, it doesn't work!
db.transaction(function(tx) {
    new Promise(function(resolve, reject) {
        tx.executeSql(
            "SELECT some stuff FROM table ....", [],
            function(tx, result) {
                // extract the data that are needed for
                // the next step
                var answer = result.rows.item( .... ).some_column;
                resolve(answer);
            }
        )
    }).then(function(answer) {
        tx.executeSql(
            "UPDATE something else",
            // The answer from the previous query is a parameter to this one
            [ ... , answer, .... ]
        )
    });
});

The problem is that, with promises, the chained .then() clause is not run immediately upon resolution of the original promise. It is only queued for later execution, much like the ajax request. The only difference is that, unlike the slow ajax request, the .then() clause runs almost immediately. But "almost" is not good enough: it may or may not run soon enough to slip in the next SQL statement into the queue before the transaction gets closed; accordingly, the code may or may not produce the invalid state error depending on timing and/or browser implementation.

Too bad: Promise would have been useful to use inside SQL transactions. The above pseudo-example can easily be rewritten without promises, but some use cases can greatly take advantage of chains of many .then()s as well as things like Promise.all that can make sure that an entire collection of SQL statements run in any order but all complete prior to some other statement.

Answer 3

I would first suggest not naming your database 'tx' but rather db or database. This could be a variable naming problem since both the function parameter and your database variables are called "tx"

EDIT: I had this same problem and solved it by making the query within the callback it's own transaction. Like so:

success: function(data) {
tx.transaction(function(transaction){
    transaction.executeSql("INSERT INTO locations (id, name, address, postalcode, phone, category) 
VALUES (2,'cheese','232','seven',5,6)"); //now more DOM exception!
  }
}}

I think the problem is by the time the callback is fired the outer transaction has completed because webSQL's transactions are not synchronous.