why do I keep getting "undefined" with MySQL function?

Question

When using one of my functions:

    async function fetchServerChance(serverID) {
    var returnValue;
    con.query("SELECT chance FROM serversConfigs WHERE serverID = '"+serverID+"' LIMIT 1", function (err, rows) {
     if (err) throw err;
     returnValue = (rows[0].chance);
    });
    return returnValue;
}

console.log(await fetchServerChance(message.guild.id));

It only returns "undefined", somebody please explain what in god's name I am suppose to do. I've tried doing tons of things like return await returnValue; and making some of the other lines have await in them.

Solve all your problems, including ones you don't yet know you have, by reading this: https://Bobby-tables.com — Caius Jard, Feb 13 '21 at 19:53
Do not use var. if query is async too, then wait for it to finish before trying to return values it gives — Caius Jard, Feb 13 '21 at 19:59

Vitalii · Accepted Answer · 2021-02-13T23:45:26.133

Your current code works like this

async function fetchServerChance(serverID) {
    var returnValue; // 1
    con.query("SELECT chance FROM serversConfigs WHERE serverID = '"+serverID+"' LIMIT 1", function (err, rows) {
     if (err) throw err;
     returnValue = (rows[0].chance); // 4
    }); // 2
    return returnValue; // 3
}

1 - returnValue declared at the top of function and it's value is undefined

2 - con.query() is calling MySQL (but returns right-away and will call provided function when results will be ready)

3 - function returns undefined which is current value of returnValue

4 - (some time later) returnValue gets updated to result of a query (but nothing gets to use that value)

You can't change the order of above execution. con.query() is asynchronous but does not return a Promise to you (so you can't stop execution there and await for the result). The function needs to return something while it does not have the result yet.

You can wrap con.query() into a Promise and return it. This will bridge the gap between MySQL library API (that accepts callback functions) and will allow to use async/await approach.

async function fetchServerChance(serverID) {
    const returnValue = new Promise(function (resolve, reject) {
      con.query(
        // serverID value will be escaped for safety and put into ? placeholder
        // see https://github.com/mysqljs/mysql#escaping-query-values
        "SELECT chance FROM serversConfigs WHERE serverID = ? LIMIT 1", [serverID], 
        function (err, rows) {
            // promise will be rejected if error happens (async function can catch the error with try/catch clause
            if (err) { reject(err) };
            // promise will resolve to value of chance
            resolve(rows[0].chance); 
        }
      );
    });
    return returnValue; // promise of future value
}

P.S.

More information about Promises: Promise, Making asynchronous programming easier with async and await

P.S.

It's a common issue, so there is promise-mysql library which does the above (returns Promises) and allows MySQL queries to be re-written as const rows = await con.query("QUERY")

why do I keep getting "undefined" with MySQL function?

1 Answers1