Having trouble with creating SQLite tables using JavaScript within a for loop

Question

Having trouble with creating SQLite tables using JavaScript within a for loop

I have developed a multi-platform app using AngularJS, JavaScript, Phonegap/Cordova, Monaca, and Onsen UI.

In order to enable offline usage of the app, I have integrated an SQLite Database to store various data. After conducting some basic tests, I confirmed that this functionality is working correctly.

My current challenge involves creating all 28 required tables within my app's initial view function onDeviceReady(). To achieve this, I am iterating through an array of objects and executing the SQLite CREATE statement for each table.

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

After running the code above, I found that only the last table was created when querying the tables post loop execution.

To address this issue, I resorted to calling individual functions for each table creation, which proved to be less efficient but successful in generating all tables.

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

The asynchronous nature of SQLite statements seems to be causing the discrepancy between the two approaches. I am unsure why the first method fails to create all tables while the second one succeeds. How can I ensure that the tables are created using the initial approach without resorting to delays?

javascript angularjs sqlite cordova

Answer 1

Answer №1

When calling the function 'createFormTables', I prefer using the JavaScript "Object" approach to prevent conflicts. Here's an example:

var QuerySet = function(){
    this.defRes = new $.Deferred();
    this.defResProm = this.defRes.promise();
};

QuerySet.prototype.createFormTables= function(inputData){
      // Handle your tasks here and resolve this.defRes once finished
     this.sqlToExecute = "// YOUR SQL QUERY"; 
     var self=this;
    $.when(
        (new SqlResult(this.sqlToExecute)).execSqlCustomDeferred(),
        self
    ).done(function(sqlRes,self){
        self.defRes.resolve();
    });

      return this.defResProm;
};

In your code, you can do:

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

Using $.Deferred() may not be necessary, but it can be useful if you need to track when all tables are created.

The following is sqlResult, used for DB transactions:

var SqlResult = function(sqlToExecute, bracketValues){
    this.sqlToExecute = sqlToExecute;
    this.bracketValues = bracketValues;
};

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

    var sqlToExecuteForTx = this.sqlToExecute;
    var bracketValuesForTx = this.bracketValues;

    DbManagement.db.transaction(function(tx){
        console.log("Executing in transaction: "+sqlToExecuteForTx);

        if(bracketValuesForTx != null){
            console.log("Bracket values: "+ArrayManagement.arrayToString(bracketValuesForTx));
        }
        
        tx.executeSql(sqlToExecuteForTx,bracketValuesForTx,success,error);
        
        function success(tx,rs){
            execSqlCustomDeferredRes.resolve(rs);
        }

        function error(tx,error){
            console.log('Error during execution: '+error.message);
            execSqlCustomDeferredRes.reject(error);
        }

    });

    return execSqlCustomDeferredResProm;
};

Answer 2

When calling the function 'createFormTables', I prefer using the JavaScript "Object" approach to prevent conflicts. Here's an example:

var QuerySet = function(){
    this.defRes = new $.Deferred();
    this.defResProm = this.defRes.promise();
};

QuerySet.prototype.createFormTables= function(inputData){
      // Handle your tasks here and resolve this.defRes once finished
     this.sqlToExecute = "// YOUR SQL QUERY"; 
     var self=this;
    $.when(
        (new SqlResult(this.sqlToExecute)).execSqlCustomDeferred(),
        self
    ).done(function(sqlRes,self){
        self.defRes.resolve();
    });

      return this.defResProm;
};

In your code, you can do:

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

Using $.Deferred() may not be necessary, but it can be useful if you need to track when all tables are created.

The following is sqlResult, used for DB transactions:

var SqlResult = function(sqlToExecute, bracketValues){
    this.sqlToExecute = sqlToExecute;
    this.bracketValues = bracketValues;
};

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

    var sqlToExecuteForTx = this.sqlToExecute;
    var bracketValuesForTx = this.bracketValues;

    DbManagement.db.transaction(function(tx){
        console.log("Executing in transaction: "+sqlToExecuteForTx);

        if(bracketValuesForTx != null){
            console.log("Bracket values: "+ArrayManagement.arrayToString(bracketValuesForTx));
        }
        
        tx.executeSql(sqlToExecuteForTx,bracketValuesForTx,success,error);
        
        function success(tx,rs){
            execSqlCustomDeferredRes.resolve(rs);
        }

        function error(tx,error){
            console.log('Error during execution: '+error.message);
            execSqlCustomDeferredRes.reject(error);
        }

    });

    return execSqlCustomDeferredResProm;
};

Having trouble with creating SQLite tables using JavaScript within a for loop

Answer №1

Similar questions

Replace specific text with a span element around it?

I want to use the enter key for posting, but also have the ability to use the shift and enter key to create a

When entering a sub-URL into the browser address bar, the routes do not display as expected

Swap out the %20, which signifies a space in the iron router context

javascript issue with fetching data from URL using the GET method

Modify meta titles according to specific url #id

Javascript and Codeigniter interaction: Using conditionals with Ajax

How can custom JavaScript objects have tags set upon click?

Switching up the content of an HTML page with JavaScript or JQuery: what you need

JavaScript is reliant on the presence of the alert() function to properly function

Issue encountered while attempting to write KML objects to Google Earth API: NPObject error

Are there specific mathematical algorithms that lend themselves well to creating responsive HTML designs?

What is the best way to activate a JQ function with my submit button?

Display information based on the radio button chosen

Contrasting onevent with addEventListener

suggestions for customizing Angular Material

The output of new Date() varies between app.js and ejs

Displaying information on a chartJS by connecting to an API using axios in VueJS

Which file from Next.js should I statically serve using Node?

Check to see if the event handler is triggered and the promises are executed in sequence (syncronously)