JavaScript functions accessing SQLite db

Question

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:

Option 2 image:

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

Samuel Toh · Accepted Answer · 2016-08-26 04:01:14Z

2

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.

edited Aug 26, 2016 at 4:01

answered Aug 25, 2016 at 23:17

Samuel Toh

19.5k3 gold badges29 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Steven Kanberg Over a year ago

Thanks, Samuel. It's progress, but I still have an error. When I implement the above, I get "TypeError: Cannot read property 'length' of undefined" from model.js. It would suggest to me that the length is still coming thru as 0, yes? As for viewLocation(), I pasted the wrong code for Location(). The original message has been corrected to what I actually have. For testing I used the code you provided in Locations() and called that from the page button. But, thoughts on the error I just mentioned and how I might implement overall with the corrected code above would be appreciated.

Samuel Toh Over a year ago

@Steve - that is my mistake. The callback function we passed into getLocations was missing a parameter. We did the right thing by calling back with the value but we forgot to retrieve it on the other end. I have updated the solution. Have a try

Steven Kanberg Over a year ago

Ok! So, it works fantastically. Thank you so much! If I may ask, however, how would I implement this with the code separated out between model.js and view.js as I edited further above? I certainly could combine the two into one as has already been successfully done by you, but I'm trying to maintain the MVC model. I appreciate your help!!

Samuel Toh Over a year ago

@Steve Updated the solution. I'm not quite whether they works for you or not. Will appreciate if you can mark this as answered if you find it helpful. Thanks

Steven Kanberg Over a year ago

Fantastic! Absolutely fantastic. Thank you. I struggled with this for quite awhile. I am in your debt.

Collectives™ on Stack Overflow

JavaScript functions accessing SQLite db

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related