nodejs mysql insert null datetime field

Question

using mysql-ssh

How to insert DateTime field's value to null in MySQL through nodejs?

Tried following:

connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', null])
connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', ''])
connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', 'null'])
connection.query('INSERT INTO idea_steps (field1, field2) VALUES(?, ?)', ['value1', undefined])

Table Structure:

field1 String
field2 DateTime Default null

Error:

for ''

Incorrect datetime value: \'\' for column \'field2\' at row 730

for undefined

Unknown column \'NaN\' in \'field list\'

for 'null'

Incorrect datetime value: \'null\' for column \'field2\' at row 730'

for null

Unknown column \'NaN\' in \'field list\'

Can anyone help me to insert DateTime field to null? I can't skip the field2 field. Let me know if it is even possible, if not I'll have to figure out something else.

Hi, can you tell the structure of table you are using (column names). — jsgrewal12, Jul 10 '20 at 10:02
What is the error that is getting generated when you try any of these ways? — Dhruv Shah, Jul 10 '20 at 10:03
`''` and `'null'` should definitively **not** insert a null value. — Álvaro González, Jul 10 '20 at 10:08
@ÁlvaroGonzález Just tried because some answers on other questions stated that... but no success — Himanshu Bansal, Jul 10 '20 at 10:09
Is that the actual code, with a literal `null`, or you're reading the value from a variable you believe to be null? — Álvaro González, Jul 10 '20 at 10:11
@ÁlvaroGonzález both actually.. I tried with variable and also tried concatenating inside the string but both fail to update — Himanshu Bansal, Jul 10 '20 at 10:27
Shouldn't the query be a string? For example: `connection.query("INSERT INTO idea_steps (field1, field2) VALUES ?", ['value1', null])` — Dhruv Shah, Jul 10 '20 at 10:28
@DhruvShah Query is string.. I replaced variable with a query that's why I forget to add quotes... but still, if the query was not string ..I won't get the above listed errors.. :/ — Himanshu Bansal, Jul 10 '20 at 10:48

Dhruv Shah · Answer 1 · 2020-07-10T10:38:55.263

0

The syntax of connection.query seems faulty over here.

It should be:

const sqlQuery = "INSERT INTO idea_steps (field1, field2) VALUES ?";
const values =  [ ['field1', 'NULL'] ];

con.query(sqlQuery, [values], function (err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });

edited Jul 10 '20 at 10:38

answered Jul 10 '20 at 10:30

Dhruv Shah

1,611
1
8
22

I have just added a callback function to `conn.query` and provided the`sqlQuery` parameter as a `string`, based on the documentation available for mysql-.ssh `https://www.npmjs.com/package/mysql-ssh` – Dhruv Shah Jul 10 '20 at 10:42
`You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near` Getting this... Not working.... – Himanshu Bansal Jul 10 '20 at 10:51

nodejs mysql insert null datetime field

1 Answers1