Node & MySQL - connection.query inside connection.query - Object property not accessible

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

1. Basically, the async.waterfall library 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 cb with the following signature:

     cb(err, connection);
     

   • If there was an error while requesting for a connection, the entire async.waterfall will interrupt and the final callback function will be executed.

   • If there was no error, the connection will be provided as a second argument. async module passes all arguments of the previous function to the next function as the first, second, etc. arguments which is why the second function receives the conn as the first argument.

   • Every next function will receive the callback cb as the last argument, which you must eventually call when the job is done.

   • Thus, in the first async.waterfall flow:

     1. It requests a new database connection.
     2. Once the connection is available, the next function is executed which sends a query to the database.
     3. Waits for the query results, then once the results are available, it is ready to run the next function which iterates over each row.

2. async.eachSeries allows to iterate over a gives array of values sequentially.

   • In the second async.eachSeries flow:

     1. It iterates over each element in the teacherHours array sequentially.
     2. Once each element is processed (however you want), you must call the done callback. Again, you could have called this as cb like in the previous async.waterfall or callback. done is just for clarity that the process is done.

3. Then we have the async.whilst which provides the same logic as the normal while () {} statement but handles the loop asynchronously.

   • In this third async.whilst flow:

     1. Calls the first function. Its return value indicates whether it has to continue the loop, i.e. call the second asynchronous function.
     2. If the return value is truthful (availableHours > 0), then the second function is called.
     3. When the async function is done, it must call the provided callback cb to indicate that it is over. Then async module will call the first function to check if it has to continue the loop.

4. In this asynchronous function inside async.whilst we have another async.waterfall because you need to send queries to the database for each teacherHour.

   • In this last fourth async.watercall flow:

     1. It sends the SELECT query to the database.
     2. Waits for the response. Once the rowRIDS are available, it calls the second function in the waterfall.
     3. If there are rowRIDS (roomIDs.length > 0), it sends the INSERT query to the database.
     4. Once done, it calls the callback cb.
     5. If there were no rowRIDs, it calls the callback cb, too, to indicate that the job is done.

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!