Steve Steve - 3 months ago 14
Javascript Question

JavaScript functions accessing SQLite db

I'm using Electron with JavaScript and am attempting to access a SQLite db. The output of the db is a list of countries. The target is a simple ul in a div. The action is called by an onclick event attached to a button on the page.

Here's the issue. Technically speaking, I can access the db. However, in the data layer (data.js) I have two options that only sort of work to populate an array from the db file. The console.log() found in each is simply for debugging.

In option 1, the return element returns the array fully populated with a length of 23 (see Option 1 image below) whereas moving the return element out of the db.all function returns the array empty on top but populated when expanded with a length of 0 (see Option 2 image). I think the first option is how the array should look, but because the array is contained within the db.all function, it doesn't return anything for getLocations() in data.js. Subsequently, the arrays in model.js and view.js (code below) remain undefined.

If I go with option 2, the arrays populate down the line (i.e. data, model, and view), but the length remains 0 for all the arrays because they are "empty on top". As such, iterating through the array for writing to the div fails because the length ultimately is incorrect at 0.

As a side note, if I hard code the countries into the array in data.js, all works fine and the list of countries write to the page. While I can (and need to) get the data from the db file, it seems to cause the issues above. So, it's really all about how data.js is returning the array.

Does anyone have any suggestions on how to properly get the arrays to populate correctly so I can iterate through them and write to the page?

Your help is appreciated!!




Data.js option 1:

function getLocations() {
var sqlite3 = require('sqlite3').verbose();
var file = 'db/locations.sqlite3';
var db = new sqlite3.Database(file);
var larray = [];

db.all('SELECT * FROM Country', function(err, rows) {
rows.forEach(function(row) {
larray.push(row.CountryName);
})
console.log(larray);
return larray;
});
}


Data.js option 2:

function getLocations() {
var sqlite3 = require('sqlite3').verbose();
var file = 'db/locations.sqlite3';
var db = new sqlite3.Database(file);
var larray = [];

db.all('SELECT * FROM Country', function(err, rows) {
rows.forEach(function(row) {
larray.push(row.CountryName);
})
});
console.log(larray);
return larray;
}





Option 1 image:
enter image description here

Option 2 image:
enter image description here

model.js:

function Locations() {

//Pull location values from data
var viewLoc = getLocations();

return viewLoc;
}


view.js:

function viewLocation() {

var viewLoc = Locations();

var container = document.getElementById('wrapper');

for (var i=0; i < viewLoc.length; i++) {
container.insertAdjacentHTML('beforeend', '<li>' + viewLoc[i]);
}
};

Answer

db.all is an asynchronous function. It's callback function is not going to get executed immediately until a response has been received from your database.

What is happening in your code for option 1 is that it will invoke the db.all function and since it is asynchronous, it will move on to the next line - that is your console.log and lastly return larray which would be empty.

To fix your problem; you will need to make getLocations() take in a callback function as a parameter. Then when a response is received from db.all, populate larray and return it through the callback.

Example:

data.js

function getLocations(done) {
    var sqlite3 = require('sqlite3').verbose();
    var file = 'db/locations.sqlite3';
    var db = new sqlite3.Database(file);
    var larray = [];

    db.all('SELECT * FROM Country', function(err, rows) {
        // This code only gets called when the database returns with a response.
        rows.forEach(function(row) {
            larray.push(row.CountryName);
        })
        return done(larray);
    });
}

model.js

function Locations(done) {
    return getLocations(done);
}

view.js

function viewLocation() {
    // Pull location values from data
    var viewLoc =  Locations(function(/*OOPS, THIS WAS MISSING!!=*/results) {
        // Code only gets triggered when getLocation() calls return done(...); 
        var container = document.getElementById('wrapper');

        for (var i=0; i < results.length; i++) {  
            container.insertAdjacentHTML('beforeend', '<li>' + results[i]);
        }
    });
}

BTW looking at your code again. It seems like viewLocation() is expecting something to be returned from Location() but from your code it is not returning anything at the moment but I suspect it could be just you trying to debug things. Anyway, that is out of the problem scope so I won't be too concern here.