nested sqlite transaction in phonegap

Question

My primary aim is to persist the following structure in a db in sqlite for android/IOS. The corresponding table will consist of foreign keys which is being taken care by the pragma.

var tempData = [{
    name : "foo",
    values : [{
            child : "foofoo",
            value : [1, 2, 3]
        }, {
            child : "foofaa",
            value : [5, 6, 7]
        }
    ]
}, {
    name : "bar",
    values : [{
            child : "barbar",
            values : [11, 22, 33]
        }, {
            child : "barbala",
            values : [44, 55, 66]
        }
    ]
}, {
    name : "baz",
    values : [{
            child : "bazbaz",
            values : [444, 333, 222]
        }, {
            child : "bazbaazar",
            values : [999, 888, 777]
        }
    ]
}];

I have written the below snippet for persisting the above structure. Is this the conventional method for getting over with nested transactions ? Or i have to follow some standards ?

The below code is working fine, except that the order of execution is not guaranteed. I am seeing some random behaviour in the output. How can i guarantee synchronous behavior for nested transactions. I have scraped many sites but could not find a candid solution for this. Please help

db.transaction(setupTable, dbErrorHandler, getEntries);
function setupTable(tx) {
doLog("before execute sql...");
tx.executeSql('CREATE TABLE IF NOT EXISTS mainTest(mainKey INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT  NOT NULL)');
tx.executeSql('CREATE TABLE IF NOT EXISTS child(PKEY INTEGER PRIMARY KEY AUTOINCREMENT,parentKey INTEGER,children TEXT  NOT NULL,FOREIGN KEY(parentKey) REFERENCES mainTest(mainKey))');
tx.executeSql('CREATE TABLE IF NOT EXISTS secondChild(SCKEY INTEGER PRIMARY KEY AUTOINCREMENT,spkey INTEGER,sales INTEGER,FOREIGN KEY(spkey) REFERENCES child(PKEY))');
doLog("after execute sql...");}
function getEntries(tx) {

//doLog("get entries");
/*dbShell.transaction(function(tx) {
tx.executeSql("select id, title, body, updated from notes order by updated desc",[],renderEntries,dbErrorHandler);
}, dbErrorHandler);*/

doLog("get entries");
db.transaction(function (tx) {
    _.each(tempData, function (item) {
        name = item.name;
        tx.executeSql('INSERT INTO mainTest (name) VALUES("' + name + '")', [], function (tx, result) {
            doLog("in child insert" + item.values);
            doLog("in child insert" + JSON.stringify(tempData));
            _.each(item.values, function (item) {
                doLog("in " + item.child);
                tx.executeSql('INSERT INTO child (parentKey,children) VALUES((select mainKey from mainTest where name = "' + name + '"),"' + item.child + '")', [], function (tx, result) {
                    _.each(item.values, function (itemNew) {
                        tx.executeSql('INSERT INTO secondChild (spkey,sales) VALUES((select PKEY from child where children = "' + item.child + '"),"' + itemNew + '")', [], function (tx, result) {}, dbErrorHandler);
                    });
                }, dbErrorHandler);
                doLog("after secondChild Insertion");
            });
            doLog("after child insertion");
        }, dbErrorHandler);
        doLog("after main insertion");
    });
}, dbErrorHandler);}

Answer 1

You have to use success callback function with tx.executeSql. The advantage also is, that if there is an error in your first SQL Statement the second will not be executed. So you do not get inconsistency in your database. This helped me:

        db.transaction(function(tx){
            tx.executeSql('DELETE FROM object WHERE object_id IN(1,2)');
            }, errorCB, function(){
            db.transaction(function(tx){
                tx.executeSql('INSERT INTO object '+values_object);
                }, errorCB, successCB
            );
            }
        );

The clue is to execute the second transaction as success callback of the first transaction. So the statements are executed in defined order.

Answer 2

I think I understand your point.

Let me try to help:

I was working with similar code, in order to try to guarantee specific order of transactions, but in my personal opinion (due to a recent experience) you have to look for a workaround to nested transactions... My issue was related to the time I had to take my phonegap app to WinPhone 8 where capabilities of database are not native available for cordova and the first solution was to include/develop a plugin. The one that I found was not able to deal with nested transactions and you can imagine how hard was to rewrite/redesign the program :/ Thinking of the async model of javascript I found one answer: I've created custom events to determine whether a transaction is finished and inside the listener of that event, run the next transaction.

//event var
var EVENT_TRANSACTION_ENDED_ = document.createEvent('Event');
//init the event
EVENT_TRANSACTION_ENDED_.initEvent('EVENT_TRANSACTION_ENDED_', true, true);
//listener to the event
document.addEventListener('EVENT_TRANSACTION_ENDED_', onEventFunctionHandler, false);

//first transaction
db.transaction(function(tx){
    //sample query
    tx.executeSql('SELECT * FROM TABLE', [], function(tx){
        //notify first transaction ended
        console.log('First transaction');
        setTimeout('document.dispatchEvent(EVENT_TRANSACTION_ENDED_);', 100);
    }, dbErrorHandler);
}, dbErrorHandler);

//
function onEventFunctionHandler(e){
    //second transaction
    db.transaction(function(tx){
        //sample query
        tx.executeSql('SELECT * FROM ANOTHER_TABLE', [], function(tx){
            //notify SECOND transaction ended
            console.log('SECOND transaction');
        }, dbErrorHandler);
    }, dbErrorHandler);
}

That worked for me, hope this helps.