CODEWITHSUNDEEP
mysqlnode.js
Arnas Marnas
Arnas Marnas

Reputation: 43

NodeJS mysql if null or empty

I have code, which should execute if the table is not in mysql or "NULL" or empty.

mysqlConnection.query('SELECT `something` FROM `here` WHERE `dog` = \'' +info+ '\'', function(err, row, fields) {
  if(err) {
    console.log('Error1');
    return;
  }
  else if (!row.length) {                                                   
    console.log('Error2');
    return;
  }
  else if (row[0].something == 'NULL' || row[0].something == '') {
    console.log('Error3');
    return;
  }
  console.log('Works');
});

So the thing is, if "something" is not in mysql, console shows Error2, but if "something" is in mysql, but if its NULL, console shows Works, so whats the problem? Im checking if something is NULL, but it wont show Error3. If table is empty, it shows Error3. Thanks for help.

Upvotes: 1

Views: 20969

Answers (3)

DG27
DG27

Reputation: 77

I am aware that I am 5 years and 9 months late, but for those of you struggling with this, here's a solution. The table's value when empty is not NULL. I was having a similar problem in which I wanted to reset AUTO_INCREMENT to 1 when the table is empty. To detect when it's empty, we have to see if it has any element with the index 0. If it has an element, it would return something like: RowDataPacket { // data }. If it doesn't, it would return undefined. See where I'm going with this? Just add a conditional to see if the result[0] is undefined or not. Want some code to better understand it? Sure! Here it is:

  db.query("SELECT * FROM tablename", (err, result) => {
    if (err) throw err;
    else {
      // If the first element does not exist
      if (result[0] == undefined) {
        db.query("yourquery", (err) => {
          if (err) throw err;
        });
      } else {
        res.send(result);
      }
    }
  });

Upvotes: 1

JLCDev
JLCDev

Reputation: 629

If you think in a scenario when you receive an Array<any> when you run a SQL like select name from employee there are three concerns you should have:

  1. If your statement did return something
  2. If the property you are looking for exist
  3. If the content of the property is null and you are expecting a null

As these concerns will occur hundreds of time, I use the following approach (in TypeScript):

let ret: Array<any> = connection.query('select name from employee',...);

for (let r of ret) {
   name = getValueColumn(r,'name','This will be thrown if content is null');
};

export function getValueColumn(obj: any, fieldName: string, messageIfNull: string = null): any {
    fieldName = fieldName.toLocaleLowerCase();
    if (!obj) {
        throw new CustomError(errorCodes.connection.rowNull, 'Linha nula e sem campos');
    } else if (!obj.hasOwnProperty(fieldName)) {
        throw new CustomError(errorCodes.connection.fieldDoesNotExist, 'Campo não existe -> ' + fieldName);
    } else {
        if (!obj[fieldName]) {
            if (messageIfNull) {
                throw new CustomError(errorCodes.connection.fieldWithNullValue, messageIfNull + '\n' + fieldName +
                ' com valores nulos ou campo invalido\n' + obj);
            };
            return null;
        }; 
        return obj[fieldName];
    };
};

If you were to check the results with just if (!ret) {...}, it would be always false as an empty array is not null. So you would have to check if(!ret[0]) {..} So all three concerns are handled and you don't need to be worried every time you want to parse the query.

Upvotes: 0

Timothy Strimple
Timothy Strimple

Reputation: 23070

I would try something like this:

mysqlConnection.query('SELECT `something` FROM `here` WHERE `dog` = ?', [info] function(err, row, fields) {
  if(err) {
    return console.log('Error1');
  } else if (!row.length) {                                                   
    return console.log('Error2');
  } else if (!row[0].something) {
    return console.log('Error3');
  }

  console.log('Works');
});

It's using a "falsy" check for row[0].something which will return false if the value is undefined, null or an empty string. It also fixes the injection attack vector that t.niese mentioned.

Upvotes: 6

Related Questions