javascript mysql insert multiple values

Question

I am trying to insert the request.body I receive which is struct the following into database

request.body = [{
        "createdAt": "2019-03-11T08:03:11.438",
        "deviceType": "type1",
        "deviceSerial": "123",
        "metricName": "metric1",
        "metricValue": "29"
    },
    {
        "createdAt": "2019-03-11T08:03:11.442",
        "deviceType": "type2",
        "deviceSerial": "234",
        "metricName": "metric2",
        "metricValue": "29"
    },
    {
        "createdAt": "2019-03-11T08:03:11.442",
        "deviceType": "type3",
        "deviceSerial": "345",
        "metricName": "metric3",
        "metricValue": "165"
    }
]

Trying to set the request.body into database:

var mysql = require('node-mysql');
var conn = mysql.createConnection({
    ...
});

var sql = "INSERT INTO Test (created_at, device_type, devices_serial, metric_name, metric_value) VALUES ?";

var values = request.body // <- Here it the problem, I guess

conn.query(sql, [values], function(err) {
    if (err) throw err;
    conn.end();
});

I am trying to understand how to construct the var values so it will fit to the following format:

var values = [
    ['2019-03-11T08:03:11.438', 'type1', '123', 'metric1', '29'],
    ['2019-03-11T08:03:11.438', 'type2', '234', 'metric2', '29'],
    ['2019-03-11T08:03:11.438', 'type3', '345', 'metric3', '165'],
];

What's the problem? Do you get any error? Is request defined at this point? — barbsan
– barbsan, Commented Mar 11, 2019 at 8:22
This solution works but probably not the best. generate sql like this 'Insert into...... values (?,?,?...?), (?,?,?...?)...' . Each () contains a record in database. And then spread params sequently. — hashtabe_0
– hashtabe_0, Commented Mar 11, 2019 at 8:38

Aman B · Accepted Answer · 2019-03-11 08:35:10Z

1

You can get the object values as Array

var values = request.body.map(r=>  Object.values(r));

answered Mar 11, 2019 at 8:35

Aman B

2,4081 gold badge20 silver badges28 bronze badges

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

Comments

AmenzO · Accepted Answer · 2019-03-11 08:50:13Z

1

EDIT : Try this

    var mysql = require('node-mysql');
    var conn = mysql.createConnection({
        ...
    });

    var sql = "INSERT INTO Test (created_at, device_type, devices_serial, metric_name, metric_value) VALUES ?";

    var values = request.body.map(Object.values);

    conn.query(sql, [values], function (err) {
        if (err) throw err;
        conn.end();
    });

edited Mar 11, 2019 at 8:50

answered Mar 11, 2019 at 8:34

AmenzO

4097 silver badges20 bronze badges

Comments

Terry Lennox · Accepted Answer · 2019-03-11 08:49:18Z

Something like this should work:

var conn = mysql.createConnection({
    ..
});

let request = {};
request.body = [{
    "createdAt": "2019-03-11T08:03:11.438",
    "deviceType": "type1",
    "deviceSerial": "123",
    "metricName": "metric1",
    "metricValue": "29"
  },
  {
    "createdAt": "2019-03-11T08:03:11.442",
    "deviceType": "type2",
    "deviceSerial": "234",
    "metricName": "metric2",
    "metricValue": "29"
  },
  {
    "createdAt": "2019-03-11T08:03:11.442",
    "deviceType": "type3",
    "deviceSerial": "345",
    "metricName": "metric3",
    "metricValue": "165"
  }
];

// Convert to array of arrays. 
const values = request.body.map((obj) => Object.values(obj));

const sql = "INSERT INTO Test (created_at, device_type, devices_serial, metric_name, metric_value) VALUES ?";

conn.query(sql, [values], function(err) {
    if (err) throw err;
    conn.end();
});

Collectives™ on Stack Overflow

javascript mysql insert multiple values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related