0

I have a problem with a node.js application that I have written, the SQL statement is only working the first time it is executed and the function after the statement never gets fired.

There are no errors when I start the application and it runs even when the query isn't executed.

This function is only working for the first time it is executed:

var sqlEntfernen = async function(id) {
  return new Promise(function(resolve) {
    var statement3 = 'DELETE FROM `table` WHERE `table`.`column` = ' + id;
    console.log('1'); //this gets fired every time the function is executed
    connection.query(statement3, async function(error3, results3, fields3) {
      console.log(results3.affectedRows + ""); //this never gets fired
      if (error3) {
        resolve(0);
      } else {
        if (results3.affectedRows > 0) {
          resolve(1);
        } else {
          resolve(0);
        }
      }
    });
  })
}

Here is the code that is executed in a for loop:

sqlEntfernen(userid).then(function(statusCode) {
     if (statusCode == 0) {
         console.log("\x1b[41m%s\x1b[0m", 'Fehler beim Löschen aus der Datenbank!'); //never gets fired
     } else if (statusCode == 1) {
         console.log("\x1b[45m%s\x1b[0m", 'Der Benutzer mit der Benutzer_ID ' + id + ' wurde aus der Inventur entfernt!'); //never gets fired
     }
});

I have no Idea why this query is only working the first time.

Hope for your help.

Thanks in advance, Patrick

EDIT: 

var mysql = require('mysql');
var connection = mysql.createConnection({
  user: 'username',
  password: 'password',
  host: '127.0.0.1',
  database: 'database'
});
Object.size = function(obj) {
  var size = 0,
    key;
  for (key in obj) {
    if (obj.hasOwnProperty(key)) size++;
  }
  return size;
};


var sqlEntfernen = async function(id) {
  return new Promise(async function(resolve) {
    var statement3 = 'DELETE FROM `benutzer` WHERE `benutzer`.`Benutzer_ID` = ' + id;
    console.log(statement3); //this gets fired every time the function is excecuted
    connection.query(statement3, async function(error3, results3, fields3) {
      console.log(results3.affectedRows + "");
      if (error3) {
        resolve(0);
      } else {
        if (results3.affectedRows > 0) {
          resolve(1);
        } else {
          resolve(0);
        }
      }
    });
  })

}


var benutzerLoeschen = async function(mandant) {
  return new Promise(async function(resolve) {
    var benutzerArray;
    var benutzerArrayinventur;
    var statement = 'SELECT `benutzer`.`Benutzer_ID` FROM `benutzer`';
    connection.query(statement, async function(error, results, fields) {
      if (error) {
        console.log("\x1b[41m%s\x1b[0m", 'Fehler beim auslesen der Datenbank "benutzer"!');
      } else {
        benutzerArray = results;
        console.log("\x1b[42m%s\x1b[0m", 'Die Datenbank "benutzer" wurde erfolgreich ausgelesen!');
      }


    });
    setTimeout(function() {
      var statement2 = 'SELECT `objekte`.`Benutzer_ID` FROM `objekte`';
      connection.query(statement2, async function(error2, results2, fields2) {
        if (error2) {
          console.log("\x1b[41m%s\x1b[0m", 'Fehler beim auslesen der Datenbank "objekte"!');
        } else {
          benutzerArrayInventur = results2;
          console.log("\x1b[42m%s\x1b[0m", 'Die Datenbank "objekte" wurde erfolgreich ausgelesen!');
        }


      });

      console.log("\x1b[44m%s\x1b[0m", "Startvorgang...");
    }, 100);
    var status = false;
    setTimeout(function() {
      for (let i = 0; i < Object.size(benutzerArray); i++) {
        for (let i2 = 0; i2 < Object.size(benutzerArrayInventur); i2++) {
          if (benutzerArray[i].Benutzer_ID == benutzerArrayInventur[i2].Benutzer_ID) {
            status = true;
            i2 = Object.size(benutzerArrayInventur) - 1;
          }

          if (i2 == (Object.size(benutzerArrayInventur) - 1)) {
            if (status) {
              console.log("\x1b[42m%s\x1b[0m", 'Der Benutzer mit der Benutzer_ID ' + benutzerArray[i].Benutzer_ID + ' wird noch in der Inventur verwendet!');
              status = false;
            } else {
              console.log("\x1b[43m%s\x1b[0m", 'Der Benutzer mit der Benutzer_ID ' + benutzerArray[i].Benutzer_ID + ' wird nichtmehr in der Inventur verwendet!');
              sqlEntfernen(benutzerArray[i].Benutzer_ID).then(function(statusCode) {
                if (statusCode == 0) {
                  console.log("\x1b[41m%s\x1b[0m", 'Fehler beim löschen aus der Datenbank!');
                } else if (statusCode == 1) {
                  console.log("\x1b[45m%s\x1b[0m", 'Der Benutzer mit der Benutzer_ID ' + id + ' wurde aus der Inventur entfernt!');
                }
              });
            }
            if (i == (Object.size(benutzerArray) - 1)) {
              setTimeout(function() {
                resolve('Bereinigung abgeschlossen!');
              }, 100);
            }
          }
        }
      }
    }, 10000);
  });

}


benutzerLoeschen().then(function(message) {
  console.log("\x1b[44m%s\x1b[0m", message);
  setTimeout(function() {
    process.exit();
  }, 10000);
});

EDIT2: NodeJs log

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Patrick Langkau
  • 47
  • 11
  • Specifically how is it "not working"? When you debug (even by just adding console log statements to see what's happening at runtime), how specifically does it fail? Where is this loop that you mention? How have you confirmed that the loop isn't just iterating only once? If the loop is iterating more than once, what specifically happens on the second iteration? – David Jun 18 '20 at 11:28
  • I added my complete code to the question. When i look into my sql database, only the first statement is executed, On the second and any further loops only the console.log(1) is fired but no sql statement. – Patrick Langkau Jun 18 '20 at 11:50
  • The literal value “1” doesn’t provide much debugging info. Can you console log the actual SQL statement? What is that statement? Is the value what you expect? – David Jun 18 '20 at 11:54
  • When i log the variable statement3 instead of the number 1 it is a correct mysql statement, when i fire it with mysql it works. I added a screenshot of the console. – Patrick Langkau Jun 18 '20 at 12:03

2 Answers2

0

I thought mysql expects a conventional function as a callback and with async function... you technically provide a Promise. I would try to remove async from call back:

connection.query(statement3, function(error3, results3, fields3) {
slkorolev
  • 5,883
  • 1
  • 29
  • 32
0

I'm not sure why, but I found out that the sql statements are executedafter my code has finished. The statusCode is also returned at he end of the code. So its working even i don't understand why the statement is executed at the end.

Patrick Langkau
  • 47
  • 11