I'm trying to send null to my MySQL database using Node.JS:
con.query("INSERT INTO Routes (routeTrigger) VALUES ( " + null + " )", {title: 'test'}, function(err, result) {
if (err) throw err;
});
But when looking in the database, the updated value reads 'null'. The database's NULL is supposed to read NULL. If I send capital NULL instead, that doesn't even work (in Node.JS). If I send 'NULL' it sends the string "NULL", and not the database's NULL.
JavaScript's null is not the same as SQL's Null. You want (in your case, using raw SQL strings) a string:
con.query("INSERT INTO Routes (routeTrigger) VALUES ( null )", {title: 'test'}, function(err, result) {
if (err) throw err;
});
But note that that creates a row that's entirely null. Which is possible, but it's unlikely what you want.
What are you actually trying to do?
If you want to assign the NULL value In the SQL tables, you can think differently.
Instead of assigning NULL, you can tweak the field (column) so that the default value (when no value is assigned) is : NULL.
Now, when you insert your new row, just don't assign any value to that field.
Example :
Let's say the table is structured like so :
MyTable
id: auto increment; ...
name: text
url: text; default = NULL
Then you could do:
INSERT INTO Mytable ( name ) VALUES ( "Joe" );
As you omit the url information, it gets set to NULL.
console.log( query ) and see what your query looks like.To pass null value in mysql using nodejs, we need a different kind of implementation of a query.
conn.query({
sql: `INSERT INTO project values(?, ?, ?, ?);`,
values: [projectId, name, startDate, endDate]}, (error, results) => {
if (error) {
console.log("Error executing sql: ", error.message);
return res.send(error);
}
});
});
Official site says: "undefined / null are converted to NULL" and it works.
Use that above syntax to fire a query.
Using the Node mysql package I was able to insert a null value by doing the following:
let myValue = null;
Note the column configuration:
'myValue' varchar(15) DEFAULT NULL
This was used for PostgresQL table migration from JSON using Node.js; may help. Check the values coming from your source, and if it doesn't exist for that column, use "NULL" with double quotes. EG, if it's JSON:
const makeVal = (val) => {
if (val === undefined) {
return "NULL"
} else {
return "'" + JSON.stringify(val).replace(/'/g, "''") + "'"
}
}
See this gist https://gist.github.com/foureyedraven/ca90a7dcbfd0918c153b0c91ec9317c5 for full implementation.
it worked
if (my_var != null) {
my_var = '"' + my_var + '"';
}
else {
my_var = "NULL";
}
connection.query('insert into my_table(my_var) values(' + my_var + ')', (error, results) => {
if (error) {
throw error;
}
connection.end();
});
