Reputation: 379
Node & MySQL - connection.query inside connection.query - Object property not accessible
I got a pretty awkward problem.
I create a pool, connect to the database, create a connection and query, get the results, do a bunch of stuff, then I have to create another connection and query, but actually it has to be dynamically so I loop over my Array teacherHours containing the data.
Then more Code is happening, and I have to create an extra loop, because certain elements of my teacherHours Array have to try multiple times to get the correct response from the upcoming query.
So another loop follows, which is supposed to loop as long as availableHours > 0. Now, here is where it all goes left.
A buch of code happens inside the second loop, I prepare my second query, call connection.query() and inside of the callback function I prepare my third query (after doing some other stuff) and this is actually where Node kicks me out.
It gives me TypeError: Cannot read property 'tid' of undefined. tid needs to be accessed for my third query, so I try to access it just like I did before but Node doesn't allow it.
I know that the queries return useful data (rows) so it can't be a problem of querying but receiving no data. Actually I console.log("the rowRIDS"+rowRIDS); the result of the second query and I see that it returns 2 rows and just after that it gives me the error.
What is also strange to me, all the console.logs inside my my two loops are being logged, and my console.log of the second query (containing the 2 rows) are being logged after the loops ran through, since the queries are nested shouldn't the returned 2 rows and the error appear within the first iteration of the loop, since the code should access the second query at that point.
BTW, I've tried to set a hardcoded number instead of the tid just to get the next property datum to be an error. I kind of got a feeling as if the variable teacherHours is out of scope, but it is supposed to be a global variable.
To get a better feeling of what I'm talking about I copied the code and uncommented all the javascript code, where I populate and calculate stuff. Any help would be really great, its been almost 7 hours of try & error without any luck. Thank You!
pool.getConnection(function(err, connection){
if (err) throw err;
connection.query('SELECT * FROM teachers_teaching_tbl WHERE fwdid = 1 ', function(err, rows, fields) {
if (err) {
console.error('error querying: ' + err.stack);
return;
}
rowArray=rows;
console.log(rowArray);
//
// HERE HAPPENS
// A LOOOOT OF STUFF
//
// AND teacherHours IS BEING POPULATED
//
// THEN COMES A FOR LOOP
for(var i=0; i<teacherHours.length;i++){
//
// MORE STUFF
//
//AND ANOTHER LOOP
while(availableHours>0){//do{ ORIGINALLY I TRIED WITH A DO WHILE LOOP
//
// AGAIN A BUNCH OF STUFF
//
// NOW I'M PREPARING MY NEXT QUERY
//
var myQueryGetFreeRoom=" SELECT rms.rid FROM rooms_tbl as rms WHERE NOT EXISTS ( ";
myQueryGetFreeRoom+=" SELECT NULL FROM classes_tbl as cls ";
myQueryGetFreeRoom+=" WHERE ( (cls.bis > '"+bisMinus1+"' AND cls.bis <= '"+realBis+"' ) OR ( cls.von > '"+bisMinus1+"' AND cls.von < '"+realBis+"' ) ) AND (cls.datum = '"+teacherHours[i].datum.getFullYear()+"-"+(teacherHours[i].datum.getMonth()+1)+"-"+teacherHours[i].datum.getDate()+"') AND (cls.rid=rms.rid) ) ";
//
//
connection.query(myQueryGetFreeRoom, function(err, rowRIDS, fields) {
if (err) {
console.error('error querying: ' + err.stack);
return;
}
roomIDs=rowRIDS;
console.log("the rowRIDS"+rowRIDS);
//
// MORE STUFF
// HAPPENING
//
if(roomIDs.length>0){
//
// PREPARING QUERY NO.3 - WHICH IS WHERE MY ERROR POINTS - TO THE USE OF tid PROPERTY
//
var myQueryBookClass = " INSERT INTO classes_tbl ( rid , tid , belegtAnz, datum, von , bis ) ";
myQueryBookClass+=" VALUES ( "+Math.floor(Math.random() * roomIDs.length)+", "+teacherHours[i].tid+" , 0, '"+teacherHours[i].datum.getFullYear()+"-"+(teacherHours[i].datum.getMonth()+1)+"-"+teacherHours[i].datum.getDate()+"' , '"+bisMinus1+"' , '"+realBis+"' ) ";
console.log("myQueryBookClass: "+myQueryBookClass);
availableHours = 0;
//
// HERE WAS SUPPOSED TO FOLLOW QUERY 3 - myQueryBookClass
//
// BUT SINCE I DONT EVEN GET INSIDE HERE IT IS IN COMMENTS
//
/*connection.query(myQueryBookClass, function(err, insertRows, fields){
if(err){
console.error('error querying: '+err.stack);
return;
}
console.log("Inserted Rows: "+ insertRows);
}); */
} else {
availableHours= availableHours - 1;
//
// STUFF HAPPENING
//
}
});
availableHours= availableHours - 1;
}//while(availableHours>0);
//
}
connection.release(function(err){
if (err){
console.error('error disconnecting: ' + err.stack);
return;
}
});
});
});
Upvotes: 1
Views: 3518
Answers (1)
Reputation: 9918
I think you are coming from a non-async language like Python, Java, etc. which is why Node, i.e. JavaScript, seems to screw things up for you, but actually it isn't.
The problem you have in your code is that you execute async functions like query synchronously all at the same time in the same while loop. You need to use a module like async which helps to run and collect results asynchronously.
Here is the updated code.
var async = require('async'),
connection;
async.waterfall([
function (cb) {
pool.getConnection(cb);
},
function (conn, cb) {
connection = conn;
connection.query('SELECT * FROM teachers_teaching_tbl WHERE fwdid = 1', cb);
},
function (rows, fields, cb) {
rowArray = rows;
console.log(rowArray);
// HERE HAPPENS
// A LOOOOT OF STUFF
//
// AND teacherHours IS BEING POPULATED
//
// THEN COMES A FOR LOOP
async.eachSeries(teacherHours, function (teacherHour, done) {
// MORE STUFF
//
//AND ANOTHER LOOP
async.whilst(function () {
return availableHours > 0;
}, function (cb) {
// AGAIN A BUNCH OF STUFF
//
// NOW I'M PREPARING MY NEXT QUERY
//
var myQueryGetFreeRoom =
"SELECT rms.rid FROM rooms_tbl AS rms WHERE NOT EXISTS ("
+ "SELECT NULL FROM classes_tbl AS cls"
+ " WHERE ("
+ "(cls.bis > '" + bisMinus1 + "' AND cls.bis <= '" + realBis + "')"
+ " OR (cls.von > '" + bisMinus1 + "' AND cls.von < '" + realBis + "')"
+ ") AND ("
+ "cls.datum = '" + teacherHour.datum.getFullYear() + "-" + (teacherHour.datum.getMonth() + 1) + "-" + teacherHour.datum.getDate() + "'"
+ ") AND cls.rid = rms.rid";
async.waterfall([
function (cb) {
connection.query(myQueryGetFreeRoom, cb);
},
function(rowRIDS, fields, cb) {
roomIDs = rowRIDS;
console.log("the rowRIDS" + rowRIDS);
//
// MORE STUFF
// HAPPENING
//
if (roomIDs.length > 0) {
//
// PREPARING QUERY NO.3 - WHICH IS WHERE MY ERROR POINTS - TO THE USE OF tid PROPERTY
//
var myQueryBookClass = "INSERT INTO classes_tbl (rid, tid, belegtAnz, datum, von, bis) VALUES ("
+ Math.floor(Math.random() * roomIDs.length)
+ ", " + teacherHour.tid
+ ", 0, '" + teacherHour.datum.getFullYear() + "-" + (teacherHour.datum.getMonth() + 1) + "-" + teacherHour.datum.getDate() + "', '" + bisMinus1 + "', '" + realBis + "')";
console.log("myQueryBookClass: " + myQueryBookClass);
availableHours = 0;
//
// HERE WAS SUPPOSED TO FOLLOW QUERY 3 - myQueryBookClass
//
// BUT SINCE I DONT EVEN GET INSIDE HERE IT IS IN COMMENTS
//
connection.query(myQueryBookClass, function (err, insertRows, fields) {
if (err) {
console.error('error querying: '+err.stack);
return;
}
console.log("Inserted Rows: "+ insertRows);
// Here do whatever you need to do, then call the callback;
cb();
});
} else {
--availableHours;
//
// STUFF HAPPENING
//
cb();
}
}
], function (err) {
if (!err) {
// Notice that you are decrementing the `availableHours` twice here and above.
// Make sure this is what you want.
--availableHours;
}
cb(err);
});
}, done);
}, function (err) {
connection.release(function (err) {
if (err) {
console.error('error disconnecting: ' + err.stack);
return;
}
});
});
}
], function (err) {
conn && pool.release(conn);
err && throw err;
});
Next time please format your code properly for better readability which will help yourself to get answers faster, and split your question text into paragraphs for the same purpose.
Explanation
There are four nested async flows:
async.waterfall
-> async.eachSeries
-> async.whilst
-> async.waterfall
Basically, the
async.waterfalllibrary allows you to execute a list of functions in series.- Every next function will be executed only after the previous function has returned the response.
- To indicate that a function is completed and the results are available, it has to call the callback, in our case it is
cb(you can call it whatever you like, eg.callback). The rule is to call it, otherwise, the next function will never be executed because the previous one doesn't seem to have finished its work. Once the previous function has completed, it calls the provided
cbwith the following signature:cb(err, connection);If there was an error while requesting for a connection, the entire
async.waterfallwill interrupt and the final callback function will be executed.- If there was no error, the connection will be provided as a second argument.
asyncmodule passes all arguments of the previous function to the next function as the first, second, etc. arguments which is why the second function receives theconnas the first argument. Every next function will receive the callback
cbas the last argument, which you must eventually call when the job is done.Thus, in the first
async.waterfallflow:- It requests a new database connection.
- Once the connection is available, the next function is executed which sends a query to the database.
- Waits for the query results, then once the results are available, it is ready to run the next function which iterates over each row.
async.eachSeriesallows to iterate over a gives array of values sequentially.In the second
async.eachSeriesflow:- It iterates over each element in the
teacherHoursarray sequentially. - Once each element is processed (however you want), you must call the
donecallback. Again, you could have called this ascblike in the previousasync.waterfallorcallback.doneis just for clarity that the process is done.
- It iterates over each element in the
Then we have the
async.whilstwhich provides the same logic as the normalwhile () {}statement but handles the loop asynchronously.In this third
async.whilstflow:- Calls the first function. Its return value indicates whether it has to continue the loop, i.e. call the second asynchronous function.
- If the return value is truthful (
availableHours > 0), then the second function is called. - When the async function is done, it must call the provided callback
cbto indicate that it is over. Thenasyncmodule will call the first function to check if it has to continue the loop.
In this asynchronous function inside
async.whilstwe have anotherasync.waterfallbecause you need to send queries to the database for eachteacherHour.In this last fourth
async.watercallflow:- It sends the
SELECTquery to the database. - Waits for the response. Once the
rowRIDSare available, it calls the second function in thewaterfall. - If there are
rowRIDS(roomIDs.length > 0), it sends theINSERTquery to the database. - Once done, it calls the callback
cb. - If there were no
rowRIDs, it calls the callbackcb, too, to indicate that the job is done.
- It sends the
It is a great thing that JavaScript is asynchronous. It might be difficult at the beginning when you convert from other synchronous languages, but once you get the idea, it will be hard to thing synchronously. It becomes so intuitive that you will start thinking why other languages don't work asynchronous.
I hope I could explain the above code thoroughly. Enjoy JavaScript! It's awesome!
Upvotes: 3
Related Questions
- Does JavaScript guarantee object property order?
- How do I remove a property from a JavaScript object?
- How to sort an object array by date property?
- Accessing an object property with a dynamically-computed name
- How do I check if an object has a specific property in JavaScript?
- Sorting object property by values
- MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client
- Detecting an undefined object property