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

Escaping back slashes in Node.js

The webpage loaded through ajax is not rendering correctly

Assigning attributes to inner components in VueJS based on prop values

locate the following div using an accordion view

The functionality of Bootstrap popover is not functioning properly

Avoid using fs.read without returning a value to console.log

Show the React component once the typewriter effect animation is complete

Tips on working with an array received from a PHP script through AJAX

Creating a linear video playback system

HTML not being able to execute Javascript when using an SSL connection

Necessitating derived classes to implement methods without making them publicly accessible

A step-by-step guide on extracting nested ASP.NET DataGrid values with JavaScript

locomotory mesh decentralized sorting

Guide to Re-rendering a component inside the +layout.svelte

The AJAX status is now 0, with a ready state of 4

Is there a way to transfer the jQuery code I've developed on jsfiddle to my website?

Leveraging the combination of <Form>, jQuery, Sequelize, and SQL for authentication and navigation tasks

Exploring the process of retrieving outcomes from Node.js within a Knockout ObservableArray

Ensure that all MySQL queries within a for loop have completed before executing the rest of a function in NodeJS

What is the process for executing mocha tests within a web browser?