Why I'm getting no results from sqlite3 (under nodeJs)?

Question

I just studied nodeJs & Sqlite3.

I'm trying to build simple nodeJs server with simple DB. I'm adding users into the DB (I'm getting no error), but when trying to print the values from the table, I'm getting empty results.

I cant understand what am I doing wrong ?

// Import Required Module
var express = require('express')
var sqlite3 = require('sqlite3').verbose();
var promise = require('promise');
var app = express()
var bodyParser = require('body-parser')


// Create instances
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({    
  extended: true
})); 

// Get
app.get('/', function (req, res) {
  res.sendFile('main.html', {root: __dirname })
})

// Init
app.get('/init', function (req, res) {
  initDataBase(req, res)
})


// Post - Login
app.post("/login", function(req, res) {

    var userName = req.body.userName;
    var pass = req.body.pass;
    console.log('userName: ' + userName);
    console.log('pass: ' + pass);

    if(req.body.userName && req.body.pass) 
    {
        if  ((userName == 'a') && (pass == 'b')) 
        {
            console.log('YES')
            res.send('YES')
        }
    }  
    else
    {
        console.log('NO')
        res.send('NO')  
    }

});

/*
    /addUserScreen
*/
app.get("/addUserScreen", function(req, res) {
    console.log('Sending: addUserScreen.html')
    res.sendFile('addUserScreen.html', {root: __dirname })
});

/*
    View
*/
app.get("/view", function(req, res) {

    var db = new sqlite3.Database('usersDataBase');
    db.all("SELECT * FROM usersTable", function(err,rows){

        if (err)
        {
            console.log('Error');
        }
        else
        {
            console.log('Ok');
            rows.forEach(function (row) {
                console.log('---');
                console.log('User: ' + row.userName + ": " + row.password);         
            });

        }
    });
    db.close(); 

    // get to main screen
    console.log('Sending main.html')
    res.sendFile('main.html', {root: __dirname })
});

function onSelectFromUsersTableSuccess(row) {
     console.log(row.userName + ": " + row.password);
}

function onSelectFromUsersTableError(err) {
   console.log('error: ' + err)
}

// Post - Add User
app.post("/addUser", function(req, res) {

    var userName = req.body.userName;
    var passw = req.body.passw;

    if(req.body.userName && req.body.passw) 
    {
         console.log('Adding userName: ' + userName + ' password: ' + passw);   
         var db = new sqlite3.Database('usersDataBase');         
         var stmt = db.prepare("INSERT INTO usersTable VALUES (null, ?, ?)", userName, passw, onAddNewUsernameFinished);         
         stmt.finalize();    
         db.close();
    }  
    console.log('Sending main.html')
    res.sendFile('main.html', {root: __dirname })
});

function onAddNewUsernameFinished(err) {

    if (null == err)
    {   
        console.log('Finished adding new user name')
    }
    else
    {
        console.log('[onAddNewUsernameFinished] error: ' + err)
    }
}

/*

*/
var initDataBase = function (req, res) {
  console.log('Init DB')

  // create DB
  var db = new sqlite3.Database('usersDataBase');

  // create table
  db.run("CREATE TABLE IF NOT EXISTS usersTable (id INTEGER PRIMARY KEY AUTOINCREMENT , userName TEXT, password TEXT)", onCreateDatabaseError);  
  db.close();
  console.log('DB Created Finished')
}

function onCreateDatabaseError(err) {
    if (null != err)
    {
        console.log('[onCreateDatabaseError] error: ' + err)
    }
}


// Listner
app.listen(3000, function () {
  console.log('Example app listening on port 3000!')
})

Answer 1

You actually haven't added users in your database.

In order to execute a prepared query statement, you need to use the .run method.

Your code should look like this :

var stmt = db.prepare("INSERT INTO usersTable VALUES (null, ?, ?)");          
stmt.run( userName, passw, onAddNewUsernameFinished );
stmt.finalize();

You can read more in the statement documentation of sqlite3