Unable to CREATE SQLite tables using JavaScript in for loop

Question

I have a cross platform app developed using AngularJS, JS, Phonegap/Cordova, Monaca and Onsen UI.

I have implemented a SQLite Database in the app to store various data to ensure that the app can be used offline. I have done a few simple tests and this is all working as intended.

I now try to create all the tables I will need in the app (28 in total) in my apps first views onDeviceReady() function. For this I create an array of objects that I will pass one by one to the SQLite CREATE statement as below.

Setting up the table values

// Form values
var formValues = [{
    tablename: "table_1",
    id: "id_1",
    desc: "desc_1",
    dataType: "TEXT"
}, {
    tablename: "table_2",
    id: "id_2",
    desc: "desc_2",
    dataType: "TEXT"
}, {
    ...
    ...
    ...
    ...
}, {
    tablename: "table_28",
    id: "id_28",
    desc: "desc_28",
    dataType: "TEXT"
}];

Creating the tables

function onDeviceReady() {
    for (var i = 0; i < formValues.length; i++) {
        var createFormValues = 'CREATE TABLE IF NOT EXISTS ' + formValues[i].tablename + ' (' + formValues[i].id + ' INTEGER, ' + formValues[i].desc + ' ' + formValues[i].dataType + ')';
        alert("SQL: " + createFormValues +
            "\n\nForm: " + formValues +
            "\nName: " + formValues[i].tablename +
            "\nID: " + formValues[i].id +
            "\nDesc: " + formValues[i].desc +
            "\nType: " + formValues[i].dataType);

        db.transaction(function (tx) { tx.executeSql(createFormValues); });
    }
};

When I run the above code, the alert shows that all the information including the SQL statement is OK. However, when I query each table following the for() loop, it says no tables are created EXCEPT the last table.

I then try the following which DOES create all the tables but is obviously less efficient and manageable.

Calling a function

createFormTables("table_1", "id_1", "desc_1", "TEXT");
createFormTables("table_2", "id_2", "desc_2", "TEXT");
createFormTables("...", "...", "...", "...");
createFormTables("table_28", "id_28", "desc_28", "TEXT");

Executing the function

createFormTables: function (tableName, id, description, dataType) {
    var sql = 'CREATE TABLE IF NOT EXISTS ' + tableName + ' (' + id + ' INTEGER, ' + description + ' ' + dataType + ')';
    alert("Create Form Field SQL: " + sql);
    db.transaction(function (tx) { tx.executeSql(sql); });
},

I know that the SQLite statements are executed asynchronously but I dont understand why that would create the tables in the second example and not the first? How do I create the tables using the first example? I can set a $timeout to execute each INSERT statement but this seems unnecessary and will cause delays.

nyluje · Accepted Answer · 2016-09-16 09:52:57Z

I would use the JavaScript "Object" aproach to avoid conflict when calling function 'createFormTables' with something like:

var QuerySet = function(){
//OPTIONAL: I use JQuery Deferred to catch when an async event has finished
    this.defRes = new $.Deferred();
    this.defResProm = this.defRes.promise();
};

QuerySet.prototype.createFormTables= function(inputData){
      //do your stuff and manage when this.defRes is resolved and results it sends back (if a result is needed)
     this.sqlToExecute = "// YOUR SQL QUERY"; 
     var self=this;
    $.when(
        (new SqlResult(this.sqlToExecute)).execSqlCustomDeferred(), //read below for SqlResult() explanations
        self
    ).done(function(sqlRes,self){
        self.defRes.resolve();
    });

      return this.defResProm;
};

Then in the code:

creatFromTables[i] = (new QuerySet()).createFormTables(inputData);
createFromTables[i].defRes.done(function(res){//do stuff with the result});

Don't need to use $.Deferred() but I think it could be useful if you want to know when all the tables have been created.

And here is sqlResult that is used to call the transaction on the DB itself:

var SqlResult = function(sqlToExecute,bracketValues){
            //console.log("SqlResult("+sqlToExecute+','+bracketValues+') starts');

    this.sqlToExecute = sqlToExecute;
    this.bracketValues =bracketValues;
};

SqlResult.prototype.execSqlCustomDeferred = function(){
        var execSqlCustomDeferredRes = $.Deferred();
        var execSqlCustomDeferredResProm = execSqlCustomDeferredRes.promise();  

        //console.log("SqlResult.execSqlCustomDeferred sqlToExecute is: "+this.sqlToExecute);
        var sqlToExecuteForTx = this.sqlToExecute;
        var bracketValuesForTx = this.bracketValues;

        DbManagement.db.transaction(function(tx){

            console.log("SqlResult.execSqlCustomDeferred in TX sqlToExecute is: "+sqlToExecuteForTx);

            if(bracketValuesForTx!=null){
                console.log("SqlResult.execSqlCustomDeferred bracket value is: "+ArrayManagement.arrayToString(bracketValuesForTx));
            }
            tx.executeSql(sqlToExecuteForTx,bracketValuesForTx,success,error);
            function success(tx,rs){
                //console.log('before execSqlCustomDeferredRes resolve ' + execSqlCustomDeferredRes.state());
                execSqlCustomDeferredRes.resolve(rs);
                //console.log('before execSqlCustomDeferredRes resolve  after ' + execSqlCustomDeferredRes.state());

            }
            function error(tx,error){
                console.log('execSqlCustomDeferred error ' + error.message);
                execSqlCustomDeferredRes.reject(error);
            }

            });

        return execSqlCustomDeferredResProm;
};

Collectives™ on Stack Overflow

Unable to CREATE SQLite tables using JavaScript in for loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related