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.
