I am working with Node.js and connecting it to MySQL.

My problem is that, a SELECT query is not returning the result. I've used an INSERT query and it works perfectly!

For the SELECT query problem.

I want to have a separate file with functions that perform queries to the MySQL database. That file is database.js, which contains the following code:

exports.connectionParams = {
    host: 'localhost',
    user: 'root',
    password: '*****',
    database: 'savitri_dev'
}

exports.getSchoolIdByName = (con, schoolName, callback) => {
    let sql = `SELECT id FROM School WHERE designation = '${schoolName}';`;
    console.log(sql);
    con.query(sql, (queryErr, result) => {
        if (queryErr) throw queryErr;
        callback(result[0].id);
        //return result[0].id;
    });
};

Now, the file where I call the query is the new_client.js (it is a route file):

var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var db = require('../bin/database');

router.get('/', (req, res, next) => {
    //res.send('respond with a resource');
    res.render('new_client', { 
        title: 'Clients - Add New Client',
        angular_app_name: 'clientsModule',
        angular_controller_name: 'AddClientController'
    });
});

router.put('/', (req, res, next) => {
    const dbConnection = mysql.createConnection(db.connectionParams);
    let data = req.body;
    let studentsDataList = [];
    console.log(data.studentsData);
    [...data.studentsData].forEach((s) => {
        const schoolId = db.getSchoolIdByName(dbConnection, s.school, function(result) {
            console.log(result);
            console.log((result != null || result != undefined));
            return parseInt(result);
        });

        console.log(schoolId);
        
        studentsDataList.push([
            s.name,
            s.year,
            schoolId,
            s.course,
            s.mainVoucherCode,
            s.megaIdentifier
        ]);

        
    });
    console.log(studentsDataList);
});

module.exports = router;

I don't understand what the issue is. This codes runs without errors having as an output:

[
  {
    name: 'M David',
    year: '3',
    school: 'Escola EB1,2,3/JI Quinta Nova da Telha',
    course: '',
    mainVoucherCode: '',
    megaIdentifier: ''
  }
]
SELECT id FROM School WHERE designation = 'Escola EB1,2,3/JI Quinta Nova da Telha';
undefined
[ [ 'M David', '3', undefined, '', '', '' ] ]
null
false

As you can see, what I want to do is, for each element in the list, I want to get from the database the id and add it to the new list.

I've already tried a lot of different thinks.

I've tried with async|await functions with a Promise: database.js

(...)
exports.getSchoolIdByName_new = (con, schoolName) => {
    return new Promise((resolve, reject) => {
        con.query(`SELECT id FROM School WHERE designation = '${schoolName}';`, (queryErr, result) => {
            return queryErr ? reject(err) : resolve(result[0].id);
        });
    });
};
(...)

new_client.js

(...)
[...data.studentsData].forEach((s) => {
        (async () => {
            dbConnection.connect();
            const schoolId = await db.getSchoolIdByName(dbConnection, s.school);
            console.log(schoolId);
            dbConnection.end();
        });

        console.log(schoolId);
        
        studentsDataList.push([
            s.name,
            s.year,
            schoolId,
            s.course,
            s.mainVoucherCode,
            s.megaIdentifier
        ]);

        
    });
    console.log(studentsDataList);
(...)

output

[
  {
    name: 'M David',
    year: '9',
    school: 'Escola EB2,3 D. João I',
    course: '',
    mainVoucherCode: '',
    megaIdentifier: ''
  }
]
PUT /clients-add-client 500 172.545 ms - 11532

I've also trying not using the query from another file - i.e., using it inside the forEach in new_client.js: new_client.js

(...)
[...data.studentsData].forEach((s) => {
        dbConnection.query(`SELECT id FROM School WHERE designation = '${s.school}';`, (queryErr, result) => {
            if (queryErr) throw queryErr;
            const schoolId = result[0].id;
        });

        console.log(schoolId);
        
        studentsDataList.push([
            s.name,
            s.year,
            schoolId,
            s.course,
            s.mainVoucherCode,
            s.megaIdentifier
        ]);

        
    });
    console.log(studentsDataList);
(...)

That gave me the same 500 output: PUT /clients-add-client 500 172.545 ms - 11532

I don't know what to try next for this to work.

CodePudding user response：

I haven't used my sequel in a project yet, can you explain

1. why have you used null as the first parameter in callback of getSchoolIdByName

2. And in new_client.js when you used db.getSchoolIdByName(), in the 3rd parameter(the result callback) you have used only one parameter which is null as i mentioned in the 1st point

CodePudding user response：

If your using await in a loop use it like this:

dbConnection.connect();
await Promise.all([...data.studentsData].map(async(s) => {            
    const schoolId = await db.getSchoolIdByName(dbConnection, s.school);
    console.log(schoolId);  
        
    studentsDataList.push([
       s.name,
       s.year,
       schoolId,
       s.course,
       s.mainVoucherCode,
       s.megaIdentifier
    ]);
    return s
}));

dbConnection.end();
console.log(studentsDataList);