INSERT INTO fails with node-mysql - variable is always NULL

Question

I am using MySql with node.js

I have this query and it not works, temp1 in MySQL is alwayls NULL:

var queryTemp1 = { tid: '1', temp: temp1, timestamp: myDate };
con.query('INSERT INTO temps SET ?', queryTemp1, function(err,res){
  if(err) throw err;

  console.log('Last insert ID:', res.insertId);

});

temp1 is float I think- - i can print it and everything is OK:

var temp1 = parseFloat(stdout);
var temp1 = temp1/1000
var temp1 = (temp1.toFixed(2));

My table has columns:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| tid       | smallint(6) | YES  |     | NULL    |       |
| timestamp | datetime    | YES  |     | NULL    |       |
| temp      | float(4,2)  | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

What am I doing wrong?

then you'd need `set temp=?`. you can't just set a value. values are already "set". — Marc B, Oct 21 '15 at 15:52

Paul Stanley · Answer 1 · 2015-10-20T20:50:22.983

0

tid is currently a string and its looking for a tinyint, try taking the single quotes away and see if that fires.

Also make sure your datetime is a datetime not actually a timestamp, ie something like 2014-01-01 00:00:00 and not 1231232131

Edit: your code here could be scrubbing the value,

var temp1 = parseFloat(stdout);
var temp1 = temp1/1000
var temp1 = (temp1.toFixed(2));

if you need to update temp1, take the var out.

var temp1 = parseFloat(stdout);
temp1 = temp1/1000
temp1 = (temp1.toFixed(2));

edited Oct 20 '15 at 20:50

answered Oct 20 '15 at 20:13

Paul Stanley

4,018
6
35
56

1

I have tried without quotes and there is no changes. Timestamp is really datetime - it works good, and stores date in MySQL – piotr Oct 20 '15 at 20:23
Can you check if tid is not actually the primary key as well please. – Paul Stanley Oct 20 '15 at 20:31
1

There is no primary key in this table. – piotr Oct 20 '15 at 20:44
I changed that, but dont help – piotr Oct 20 '15 at 20:55
Can you give an example `temp1` echo/console.log() for us please? – Paul Stanley Oct 20 '15 at 20:55
7.81 - its actual temperature outside. – piotr Oct 20 '15 at 21:02

score 0 · Answer 2 · answered Oct 20 '15 at 20:17

0

That INSERT statement doesn't look right...

Try this:

// BIND PARAMS
var queryTemp1 = { tid: '1'
                 , temp: temp1
                 , timestamp: myDate 
                 };

// VARIABLE FOR SQL
var sql = "INSERT INTO temps(tid, temp, timestamp)\
           VALUES (:tid, :temp, :timestamp)";

// DATABASE
con.query( sql
         , queryTemp1
         , function(err, res) {
             if(err) throw err;
             console.log('Last insert ID:', res.insertId);
          });

answered Oct 20 '15 at 20:17

ash

1,224
3
26
46

I want to insert all values, and resolution provided loks like wants to insert only temp. – piotr Oct 20 '15 at 20:22
Looking at the MySQL Docs - it does look like you can use `INSERT SET` http://dev.mysql.com/doc/refman/5.6/en/insert.html . I've never used it! In any case - I'm not sure it's appropriate for binding variables. See if my answer helps you, and feel free to give me a shout if you need more help with this. – ash Oct 20 '15 at 20:27
Result is: var sql = "INSERT INTO temps(tid, temp, timestamp) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ SyntaxError: Unexpected token ILLEGAL – piotr Oct 20 '15 at 20:36
There will likely be one or more spaces after the backslash - just check, or whack it all on one line instead - e.g. `var sql = "INSERT INTO temps(tid, temp, timestamp) VALUES (:tid, :temp, :timestamp)";` – ash Oct 20 '15 at 20:38
I found that, and there is still error: throw err; ^ Error: ER_PARSE_ERROR: 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 ':tid, :temp, :timestamp)' at line 1 – piotr Oct 20 '15 at 20:40
Which mysql package are you using, and how does it expect to receive bind params? e.g. does it expect an object (like you have provided), or an array? I did wonder when I saw the placeholder `?` - looks like it wants an array (in which case change `queryTemp1` to `queryTemp1 = ['1', temp1, myDate];` and update the SQL accordingly (with `?` instead of `:tid, :temp, :timestamp`) - let me know in any case and I will have a look. – ash Oct 20 '15 at 20:45
There is still the same error. There is what im using: var mysql = require("mysql"); – piotr Oct 20 '15 at 20:49
Looking at the docs... https://www.npmjs.com/package/mysql#escaping-query-values - looks like you're following the docs as you should - maybe @Octopi is on the right track. – ash Oct 20 '15 at 20:53
Now i have Error: ER_WRONG_VALUE_COUNT_ON_ROW: Column count doesn't match value count at row 1 I tried solution provided by @Octopi, but without success. – piotr Oct 20 '15 at 20:54
That suggests that your bind params aren't all set - e.g. - you're asking to insert into 3 columns, but only giving it 1 or 2 bind params. console.log `queryTemp1` and see what it's populated with. – ash Oct 20 '15 at 20:56
I dont know where to consoloe.log becouse of that error. I found right place :) There is really not definied temp: Output: [ 1, undefined ] - i used 2 params – piotr Oct 20 '15 at 21:12
Console.log it right underneath where you set queryTemp1 - good practice would be to check all the variables are set before it goes anywhere near the database - have you managed to sort it now then and got it working? – ash Oct 20 '15 at 21:18
I just uploaded entire code, can you have a look at that? – piotr Oct 20 '15 at 21:21

score 0 · Answer 3 · answered Oct 20 '15 at 21:20

Here is entire code:

    var mysql = require("mysql");
    var child_process = require('child_process');
    var temp1;
    // First you need to create a connection to the db
    var con = mysql.createConnection({
      host: "localhost",
      user: "user",
      password: "password",
      database: "database"
    });

    con.connect(function(err){
      if(err){
        console.log('Error connecting to Db');
        return;
      }
      console.log('Connection established');
    });

    console.log(temp1);

    child_process.exec("sudo -u www-data grep -a 't=' /var/www/temp/w1_slave | cut -f2 -d=", function (err, stdout, stderr){
        if (err) {
            console.log("child processes failed with error code: " +
                err.code);
        }
        console.log(stdout);
        temp1 = parseFloat(stdout);
        temp1 = temp1/1000
        temp1 = (temp1.toFixed(2));
    });



    var moment = require('moment');
    var now = moment();
    myDate =  moment(now.format('YYYY/MM/DD HH:mm:ss')).format("YYYY-MM-DD HH:mm:ss");

    var queryTemp1 = [1, temp1];

    // VARIABLE FOR SQL
    var sql = "INSERT INTO temps(tid, temp) VALUES (?)";

    // DATABASE
    con.query(sql
             , queryTemp1
             , function(err, res) {
                 if(err){console.log(queryTemp1); throw err;}
                 console.log('Last insert ID:', res.insertId);
              });
    console.log(queryTemp1);

Replace VALUES with SET - if you want to use VALUES, then you need to stipulate what they are (as shown in my answer) — ash, Oct 20 '15 at 23:52

score 0 · Answer 4 · answered Jan 08 '21 at 22:22

A working example of my use case that inserts a row and returns the insertId:

Function definition:

const sleep = (waitTimeInMs) => new Promise(resolve => setTimeout(resolve, waitTimeInMs));
const insertLog = async (db, requestUrl, requestType, isRequestSuccessful) => {
    let insertId = null;

    const stmt = 'insert into logs(request_url, request_type, request_status) values (?, ?, ?)';
    const insertData = [requestUrl, requestType, isRequestSuccessful ? 'successful' : 'failed'];
    db.query(stmt, insertData, (err, result, fields) => {
        if (err) {
            throw err;
        }

        insertId = result.insertId;
    });

    while(insertId === null) {
        await sleep(100);
    }

    return insertId;
};

Usage:

const logId = await insertLog(mysqlConnection, browseUrl, 'browse', false);

I'm using mysql package v2.18.1 and node v15.4.0.

Should be easy to adjust to your use case, for example

const insertId = await insert(mysqlConnection, data);

INSERT INTO fails with node-mysql - variable is always NULL

4 Answers4