Are SQL Statements Executed in Sequential Order via PhoneGap's Web SQL Database?

Question

The PhoneGap Web SQL Database documentation at http://docs.phonegap.com/en/3.1.0/cordova_storage_storage.md.html#SQLTransaction lists the following JavaScript code fragment:

function populateDB(tx) {
    tx.executeSql('DROP TABLE IF EXISTS DEMO');
    tx.executeSql('CREATE TABLE IF NOT EXISTS DEMO (id unique, data)');
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (1, "First row")');
    tx.executeSql('INSERT INTO DEMO (id, data) VALUES (2, "Second row")');
}

Am I guaranteed that the four SQL statements in the above code fragment will execute sequentially (i.e., the DROP TABLE command will definitely execute first, followed by the CREATE TABLE statement second, etc)? There are lots of postings about the asynchronous nature of the PhoneGap Web SQL Database API, but I can't find any postings about the sequential nature of the PhoneGap Web SQL Database API. As you might imagine, it doesn't make any sense for the CREATE TABLE statement to execute if the DROP TABLE statement didn't first finish executing.

Kyaw Tun · Answer 1 · 2013-11-26T10:22:26.940

1

Unfortunately, it is not guaranteed. Websql spec don't say that request must be execute on order it placed, whereas IndexedDB API does. But most implementation respect request ordering, but few don't.

The proper way is listen to request success callback and use tx from the callback to guarantee sequential execution.

edited Nov 26 '13 at 10:22

answered Nov 26 '13 at 07:35

Kyaw Tun

12,447
10
56
83

So, if the first table that I create requires one or more indexes, I need to create the indexes in a success callback? And if I have five tables to create, each of which has a foreign key declared against the most recently created table (that is, table 2 has a foreign key against table 1, table 3 has a foreign key against table 2, etc), I end up creating table 2 in the success callback for table 1, table 3 in the success callback for table 2, and so on? – Mitch Dec 02 '13 at 14:20
I believe you will not face any out-of-order issue.. never heard or see it. – Purus Dec 02 '13 at 14:32
Just search here in SO. New cordova already encounter problem reported – Kyaw Tun Dec 02 '13 at 15:32
Could you please post the SO URL of the new cordova problem? Thank you very much. – Mitch Dec 02 '13 at 18:23
In order for the sql queries to execute sequentially if your using a for loop you need to make a new transaction for each new item – Kardon63 Mar 24 '21 at 12:05

score 0 · Answer 2 · answered Nov 25 '13 at 17:42

0

Yes. The lines are executed in sequential order unless you have have some conditional branching.. like if-else-then.

For the above snippet, it will execute sequentially.. guaranteed.

answered Nov 25 '13 at 17:42

Purus

5,701
9
50
89

Is there any documentation confirming this? – Mitch Dec 02 '13 at 14:21
No documentation.. but as far as I have done, it always respect the ordering. – Purus Dec 02 '13 at 14:24

score 0 · Answer 3 · answered Oct 01 '15 at 23:39

I have had the same issue. I wrote a function to make it easier to avoid callback hell. I am sure there are probably similar things out there but here is mine. I tested it with 100,000 lines, no problem

function runSqlSeries(tx, sqls, parameterss, fnum, callback) {
    if (typeof sqls === 'string') {
        sqls = [sqls];
    }
    var totalNumber = sqls.length;
    var sqlIndex = fnum;
    if (parameterss && sqls.length == 1 && parameterss.length > 1) {
        //ie one sql statement run many times
        totalNumber = parameterss.length;
        sqlIndex = 0;
    }
    if (fnum >= totalNumber) {
        callback(true, "success - ran " + fnum + " sql statements");
        return;
    }
    var successFn = function() {
        astracore.runSqlSeries(tx, sqls, parameterss, fnum + 1, callback);
    }
    var errorFn = function(tx, error) {
        callback(false, "Error running function " + fnum + " " + error.message);
    }
    var parameters = [];
    if (parameterss) {
        parameters = parameterss[fnum];
    }

    tx.executeSql(sqls[sqlIndex], parameters, successFn, errorFn);
};

Are SQL Statements Executed in Sequential Order via PhoneGap's Web SQL Database?

3 Answers3