custom mysql function in nodejs returns undefined

Question

so I have a multi function in a file in NodeJS, and it returns undefined, so here is my code: `

var MySql = require('mysql');

var mysql = MySql.createConnection({
    host: process.env.DB_IP,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: "gamezone_3tar"
});

mysql.connect(function(err){
    if (err) throw err;
    console.log('MySql Connection Successfully!');
});

module.exports = {
    checkGuild: function(gid){
        var sql = "SELECT * FROM GUILDS WHERE `gid` = '"+gid+"'";
        mysql.query(sql, function (err, result) {
            if (err) throw err;
            return true;
        });
    },
    addGuild: function(gid, gname, gowner){
        var sql = "INSERT INTO GUILDS(`gid`, `gname`, `gOwner`) VALUES ('"+gid+"', '"+gname+"', '"+gowner+"')";
        mysql.query(sql, function (err, result) {
            if (err) throw err;
            return true;
        });
    },
    checkVip: function(type, id){
        if(type == 'guild')
            var sql = 'SELECT vip FROM GUILDS WHERE `gid` = ?';
        else if(type == 'user')
            var sql = 'SELECT vip FROM USERS WHERE `uid` = ?';
        
        mysql.query(sql, [id], function(err, result){
            if (err) throw err;
            var tempdata = JSON.parse(result);
            if(tempdata.vip == 'false') 
                return false;
            else if(tempdata.vip == 'true') 
                return true;
        });
    },
    addVip: function(type, id){
        if(type == 'guild')
            var sql = "UPDATE GUILDS SET vip = 'true' WHERE `gid` = '"+id+"'";
        else if(type == 'user')
            var sql = "UPDATE USERS SET vip = 'true' WHERE `uid` = '"+id+"'";
        
        mysql.query(sql, function(err, result){
            if (err) throw err;
            return true;
        });
    },
    removeVip: function(type, id){
        if(type == 'guild')
            var sql = "UPDATE GUILDS SET vip = 'false' WHERE `gid` = '"+id+"'";
        else if(type == 'user')
            var sql = "UPDATE USERS SET vip = 'false' WHERE `uid` = '"+id+"'";
        
        mysql.query(sql, function(err, result){
            if (err) throw err;
            return true;
        });
    },
    removeGuild: function(gid){
        var sql = "DELETE FROM GUILDS WHERE `gid` = '"+gid+"'";
        mysql.query(sql, function(err, result){
            if (err) throw err;
            return true;
        });
    }
};

and here is the discord bot command i use to trigger this functions:

var db = require('../../etc/database.js');

module.exports = {
  name: "addvip",
  description: "change guild to vip",
  execute(message) {
    const msgID = message.author.id;
    if (!admins.includes(msgID)) return;
    if(db.checkGuild(message.guild.id) == true)
    {
      if(db.checkVip('guild', message.guild.id) == true)
      {
        console.log('already vip!');
      }
      else if(db.checkVip('guild', message.guild.id) == false)
      {

        if(db.addVip('guild', message.guild.id) == true)
        {
          console.log('Guild is now vip!');
        }else{
          console.log('error in addvip');
        }

      }else{
        console.log('error in checkVip');
      }
    }
    else if(!db.checkGuild(message.guild.id) == true)
    {
      if(db.addGuild(message.guild.id, message.guild.name, message.guild.owner) == true)
      {
        if(db.addVip('guild', message.guild.id) == true)
        {
          console.log('added to vip!');
        }else console.log('error in adding [check]');
      }else console.log(db.addGuild(message.guild.id, message.guild.name, message.guild.owner));
    }else console.log('wtf!');
  }
};

so any idea how to fix this, please? I'm seriously working on this code like 1 month after solving SQL errors and ...

and BTW, it successfully connects to MySQL server!

Daemon Beast · Accepted Answer · 2020-08-09 10:46:02Z

1

It seems to be due to the fact that your SQL code uses callbacks, so no data is immediately returned and therefore, the return value is undefined. To fix this, you need to make the SQL functions return a Promise and change the return statement to resolve(), like this:

checkGuild: function(gid){
    return new Promise(resolve => {
        var sql = "SELECT * FROM GUILDS WHERE `gid` = '"+gid+"'";
        mysql.query(sql, function (err, result) {
            if (err) throw err;
            resolve(true);
        });
    });
},

Then, you need to use await wherever the function is called in order to wait for the Promise to complete. Since, you are calling the functions in the addvip command file, you need to make the execute function asynchronous, so that you can use await:

module.exports = {
  name: "addvip",
  description: "change guild to vip",
  async execute(message) {
    const msgID = message.author.id;
    if (!admins.includes(msgID)) return;
    if(await db.checkGuild(message.guild.id) == true)

answered Aug 9, 2020 at 10:46

Daemon Beast

2,9193 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Ehsan Fox Over a year ago

so i got this error: ` /app/node_modules/mysql/lib/protocol/Parser.js:437 throw err; // Rethrow non-MySQL errors SyntaxError: Unexpected token o in JSON at position 1`

Daemon Beast Over a year ago

@EhsanFox The error looks like there is a problem with your SQL queries or the file you are accessing. Did you remember to apply the examples in my answer to all parts of your code?

Ehsan Fox Over a year ago

i applied all of them, so can u tell mewhat is wrong with my sql query? here is the code:

mysql.query(sql, [id], function(err, result){ 				if (err) throw err; 				var tempdata = JSON.parse(result); 				if(tempdata.vip == 'false')  					resolve(false); 				else if(tempdata.vip == 'true')  					resolve(true); 			});

Daemon Beast Over a year ago

@EhsanFox You didn't make it return a Promise like in my answer.

Ehsan Fox Over a year ago

i did as you said, the undefined think is gone, and instead of that, im getting the error that i sent

|

Collectives™ on Stack Overflow

custom mysql function in nodejs returns undefined

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related