Issue with inserting data into MariaDB using NodeJS

Question

const mariadb = require('mariadb/callback');
const connection = mariadb.createConnection({
  host     : 'localhost',
  user     : 'tudublin',
  password : 'Tudublin@2020',
  database : 'IOT',
  timezone: 'Europe/Dublin',
});
 
connection.connect(err => {
    if (err) {
      console.log("not connected due to error: " + err);
    } else {
      console.log("connected ! connection id is " + connection.threadId);
    }
  });

connection.query("INSERT INTO observations VALUES (?, ?, ?, ?)", [2, 20, "oC", Date.now()], (err, result) => {
      if (err) throw err;
      console.log(result);
      //log : { affectedRows: 1, insertId: 1, warningStatus: 0 }
    }
  );

MariaDB [IOT]> describe observations;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| data_id   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| sensor_id | int(10) unsigned | NO   | MUL | NULL    |                |
| temp      | int(11)          | YES  |     | NULL    |                |
| temp_unit | varchar(30)      | YES  |     | NULL    |                |
| dt_added  | datetime         | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
5 rows in set (0.003 sec)

Hi,

I am trying to insert data into my observations table. But it keeps displaying the error:

code: 'ER_WRONG_VALUE_COUNT_ON_ROW'

I created the table using the below command

CREATE TABLE observations (data_id INT unsigned not null auto_increment primary key,sensor_id INT unsigned not null, temp int, temp_unit VARCHAR(30), dt_added DATETIME);

&

ALTER TABLE observations ADD FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id);

Which I thought the way it's set up would be fine as I am only entering in 4 values and the other one being the data_id which is set as a primary key and auto_increments. Would anyone have any idea on why it shows the above error?

Thanks!

t.niese · Accepted Answer · 2020-12-08 22:12:20Z

1

In INSERT INTO observations VALUES (?, ?, ?, ?) you omit the column names associated with the values, due to that you need to provide values for all columns, also the primary key (even so it has auto increment).

You generally should not rely only on the column order when inserting values but use the corresponding column names. This will prevent you from future problems in case the column order changes for some reason and allows you to omit the data_id column:

INSERT INTO `observations`(`sensor_id`, `temp`, `temp_unit`, `dt_added`) VALUES (?,?,?,?)

answered Dec 8, 2020 at 22:12

t.niese

41k9 gold badges78 silver badges111 bronze badges

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

Collectives™ on Stack Overflow

Issue with inserting data into MariaDB using NodeJS

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