sql query inside loop failing

Question

I have this:

var getDetailsForText = "select * from SCHEDULE where DATE BETWEEN '" + startDateRange + "' AND '" + endDateRange + "'"
console.log(getDetailsForText)
//figure out date = ''

ibmdb.open(ibmdbconn, function(err, conn) {
if(err) 
    return console.log(err);
conn.query(getDetailsForText, function(err, rows) {
    if(err)
        console.log(err);

    let startDate = startDateRange.split(' ')
    startDate = startDate[0]
    let startTime = startDate[1]
    let endTime = endDateRange.split(' ')
    endTime = endTime[1]

    for(var i = 0; i < rows.length; i++) 
    {
        let clientPhoneNumber = ''
        let clientName = ''
        let trainerName = ''
        console.log(rows[i])
        var getUserWithEvent = "select * from users where username = '" + rows[i]["CLIENT_USERNAME"] + "'"

        ibmdb.open(ibmdbconn, function(err, conn) {
            if(err) 
                return console.log(err);
            conn.query(getUserWithEvent, function(err, clientdata) {
                if(err)
                    console.log(err);

                console.log("test data!")

                for(var i = 0; i < clientdata.length; i++) 
                {
                    console.log("test data!")
                    console.log(clientdata[i])
                    clientPhoneNumber = clientdata[i]["PHONE_NUMBER"]
                    clientName = clientdata[i]["FIRST_NAME"] + " " + clientdata[i]["LAST_NAME"]
                }

                /*
                    client.messages.create({
                         to: clientPhoneNumber,
                         from: 'mynum',
                         body: 'hello ' + clientName + ', your session begins in 24 hours. To cancel your session, reply CANCEL.'
                     }, function(err, data) {
                         if (err) {
                             console.log("err: " + err)
                         }
                         console.log(data)
                     });
                 */

                conn.close(function() {});
             });
         });
     }
    conn.close(function() {});
  });
});
}, null, true)

What this does is it goes through the SCHEDULE table between the specific range. That part works and actually returns 2 results, which is right. But then, as you can see, I have a loop that loops through those results, which is fine, but then I do another SQL query which takes the CLIENT_USERNAME and queries the table with that. When i do that though, it returns 2 results, but both of them are the LAST result of the two. So it returns 2 results, but they are the identical results, which isn't right and shouldn't be happening. It then screws up my text messages. How can i fix this, what am I doing wrong? I can't figure it out!

Use `let getUserWithEvent` instead of `var getUserWithEvent` — Barmar, Dec 21 '21 at 17:14
It would be best if you changed all your `var` declarations to `let`. — Barmar, Dec 21 '21 at 17:14
You also should use parametrized queries instead of string concatenation in the SQL. — Barmar, Dec 21 '21 at 17:14
@Barmar appreciate the response. But will changing it from var to let do much other then the way it is declared? — roger, Dec 21 '21 at 17:15
Yes, I believe it will. The callbacks are asynchronous, so they're both running after the `for` loop completes, at which time the variable has the value from the last iteration. — Barmar, Dec 21 '21 at 17:16
@Barmar it worked like that, you are correct. Thank you so much. Do you mind posting an answer explaining a little bit on why that would've worked and your recommendations for future visitors? — roger, Dec 21 '21 at 17:21

sql query inside loop failing

0 Answers0