How to pass a column NULL value from angular to node using mysql

Question

Sometimes, I need to pass a null value from my angular frontend app to my backend(node+mysql). My column table content can be NULL.

I have tried to use: NULL, 'NULL' or empty('') but in all cases I have an exception on server side because mysql throw an invalid column value. The error is:

code: 'ER_TRUNCATED_WRONG_VALUE', errno: 1292,
 sqlMessage: 'Incorrect datetime value: \'\' for column \'DATE_CHECK\' at row 1',
 sqlState: '22007',

How could I do that?

//angular front end controller

var saveItem=async function(row){
        var item={}
        try{
          item.ID=row.ID;
          item.DATE_CHECK=row.DATE_CHECK?moment(row.DATE_CHECK).format('YYYY-MM-DD'):'';
          await  $http.post('/agenda_staff/' + item.ID, item)
        } catch(erro){
           console.log(erro)
        }
}

//node server

router.post('/:id',async (req,res,next)=>{  
    var input = JSON.parse(JSON.stringify(req.body));
    var id = req.params.id;
    try {
       const connection = await pool.getConnection();
       try {
            var data = {
              ID: input.ID
            , DATE_CHECK: input.DATE_CHECK
            };

            var query=await connection.query("UPDATE AGENDA_STAFF set ? WHERE id = ? ",[data,id])
            var ret={success:true, message:'Registro foi atualizado'}
            return res.send(ret);
        } finally {
            pool.releaseConnection(connection);
        }
    } catch (err) {
        if (err instanceof Errors.NotFound)
            return res.status(HttpStatus.NOT_FOUND).send({ success:false, message: err.message, results:err.stack }); // 404
        console.log(err);
        return  res.status(HttpStatus.INTERNAL_SERVER_ERROR).send({ success:false,message: err.message, results:err.stack }); // 500
  }
});

//table def

CREATE TABLE AGENDA_STAFF (
    ID BIGINT AUTO_INCREMENT  NOT NULL
    , DATE_CHECK              TIMESTAMP 
    ,CONSTRAINT PK_STAFF
       PRIMARY KEY ( ID )
)ENGINE=INNODB;

ufxmeng · Accepted Answer · 2017-11-26 09:33:01Z

1

As the docs said: undefined / null are converted to NULL, not something like string 'null' or empty string ''. so change the code inside the node server.

var data = {
    ID: input.ID,
    DATE_CHECK: input.DATE_CHECK === '' ? null : input.DATE_CHECK
};

answered Nov 26, 2017 at 9:33

ufxmeng

2,6001 gold badge15 silver badges14 bronze badges

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

Collectives™ on Stack Overflow

How to pass a column NULL value from angular to node using mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related