Looping through json API and inserting values into MySQL Node JS

Question

I'm trying to save specific data from a WEB API to my MySql Database using Node JS, but I'm having trouble. Here is my API.
I need to get the following values from each device.

ID
Name
Type
Enabled
Value

So far I've been trying to use the following code, but it seems to only work for the last ID? I'm counting if the Device ID is already in my Database, if not it'll add the device along with all data. Otherwise, it'll output the ID along with the Value (and update the corresponding data within the database). My goal is to request the API every few seconds, run through each device id and update my database. This is the only solution to what I'm trying to achieve and so far it's just giving me a headache. Massive thanks to anyone who knows how to achieve this or what I'm doing wrong.

fetch('http://127.0.0.1:3000/test', settings)
    .then(res => res.json())
    .then((json) => {
        for (var i = 0; i < json.length; i++) {
            json.id = json[i].id;
            json.value = json[i].properties.value;
            json.name = json[i].name;
            json.type = json[i].type;
            json.enabled = json[i].enabled;
            connection.query('SELECT COUNT(*) AS exist FROM devices WHERE id = ? AND state = ?;', [json.id, "enabled"], function(error, rows, fields) {
                if (error) {
                    console.log(error);
                }
                if (rows[0].exist == 0) {
                    connection.query('INSERT INTO devices(id, name, value, type, state) VALUES( ? , ? , ? , ? );', [json.id, json.name, json.value, json.type, json.enabled], function(error, rows, fields) {
                        if (error) {
                            console.log(error);
                        }
                    });
                } else if (rows[0].exist == 1) {
                    console.log(json.id + ";" + json.value);
                }
            });
        };
    });

Use let i = 0; instead var i = 0;

Eldar
– Eldar

2020-06-30 05:27:24 +00:00
Commented Jun 30, 2020 at 5:27 — Eldar
– Eldar, Commented Jun 30, 2020 at 5:27

Terry Lennox · Accepted Answer · 2020-06-30 06:21:19Z

I think the problem here is that you're modifying the array of devices within the loop, this is causing some slightly odd behaviour.

I'd suggest populating a separate device variable then using this in the insert query, like so:

fetch('http://127.0.0.1:3000/test', settings)
    .then(res => res.json())
    .then((json) => {
        for (var i = 0; i < json.length; i++) {
            console.log(`inserting device: ${i+1} of ${json.length}:`, json[i]);
            const device = { ...json[i], value: json[i].properties.value };
            connection.query('SELECT COUNT(*) AS exist FROM devices WHERE id = ? AND state = ?;', [device.id, "enabled"], function(error, rows, fields) {
                if (error) {
                    console.log(error);
                }
                if (rows[0].exist == 0) {
                    connection.query('INSERT INTO devices(id, name, value, type, state) VALUES( ? , ? , ? , ?, ? );', [device.id, device.name, device.value, device.type, device.enabled], function(error, rows, fields) {
                        if (error) {
                            console.log(error);
                        }
                    });
                } else if (rows[0].exist == 1) {
                    console.log(device.id + ";" + device.value);
                }
            });
        };
    });

I also had to modify the insert SQL query to:

INSERT INTO devices(id, name, value, type, state) VALUES( ? , ? , ? , ?, ? );

Collectives™ on Stack Overflow

Looping through json API and inserting values into MySQL Node JS

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