NodeJS Json Data not fully inserting into database

Question

Been 2 days trying to find a solution to my problem. I request data(json) from a website. They return fine and json is valid but when i try to insert them to database almost 10% do not get inserted. I dont know what to do, i even tried php with same results. Any help world be appreciated thank you.

This is json ouptut after selecting the data attribute var result = obj.data; pastebin

var request = require("request");
var fs = require('fs');
var sleep = require('system-sleep');

var mysql = require('mysql');
var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'feeds',
    timezone: 'Z'
});

request.post({
    url: "xxx",
    headers: {
        "Content-Type": "application/json"
    },
    body: '{"xx": "true", "xxx": "true"}'
}, function(error, response, body) {

    var obj = JSON.parse(body);
    var result = obj.data;
    console.log(result);
    for (var i = 0; i < result.length; i++) {
        var object = result[i];
        for (property in object) {
            var value = object[property];
            var pid, pname;
            if (property == "id") {
                pid = value;
            }
            if (property == "name") {
                pname = value;
            }
            if (property == "xxxxx") {}
            if (property == "xxxxxxxx") {
                connection.query('INSERT INTO tournaments(id, name) VALUES (' + pid + ', "' + pname + '")');
            }

        }
    }

});

So the comment there, that's where the insert occurs? It seems to me like that would be an async action, and you would need a promise to get the results of that insert. Either way, you should be able to get the result, it's possible there is an error message you aren't getting. — Peege151, Dec 02 '17 at 19:30
Ok, so how do you know if the insertion was successful? I'm not familiar with MySQL, but ideally you can .then that operation and get the result of the insertion. — Peege151, Dec 02 '17 at 19:35

Peege151 · Accepted Answer · 2017-12-02T19:40:56.207

0

Welcome to SO.

There is probably an error that you are missing. You request the insertion but then let it go. connection.query allows a function as its second parameter, and that is a callback. Check out the following line

connection.query( 'SELECT * FROM some_table', ( err, rows ) => {
  // do something with the results here
} );

For you it would look something like:

connection.query('INSERT INTO tournaments(id, name) VALUES (' + pid + ', "' + pname + '")', function(err, rows){
   console.log('Successful write', rows);
   console.error('Error! This did not write because: ', err);
})

You should console.log both err and rows, for each time this is called. Odds are you will now see why some of the results are not getting written into the DB, and they will show up as errors.

edited Dec 02 '17 at 19:40

answered Dec 02 '17 at 19:37

Peege151

1,562
2
21
45

this is what i get Successful write OkPacket { fieldCount: 0, affectedRows: 1, insertId: 86, serverStatus: 2, warningCount: 1, message: '', protocol41: true, changedRows: 0 } Error! This did not write because: null Successful write OkPacket { fieldCount: 0, affectedRows: 1, insertId: 87, serverStatus: 2, warningCount: 1, message: '', protocol41: true, changedRows: 0 } Error! This did not write because: null – Tim Dec 02 '17 at 19:40
So in this case, null is the error, which is good. That means success. Try NOT logging rows, and only logging err. It will be a little cleaner. – Peege151 Dec 02 '17 at 19:43
Error! This did not write because: null Error! This did not write because: null Error! This did not write because: null Error! This did not write because: null Error! This did not write because: null Error! This did not write because: null – Tim Dec 02 '17 at 19:45
I really dont know what to do, even the error returns nothing – Tim Dec 02 '17 at 19:45
The error returns nothing because there is no error! Is everything in the DB? Or are you still missing data? – Peege151 Dec 02 '17 at 19:45
yes still missing some data, for example id 75 (italy), 59 (Germany) etcc – Tim Dec 02 '17 at 19:46
It sounds like all the insertions are working with the DB, so I would then check your conditional statements. You may not be calling the write for italy or germany because the if statement resolves to false, and it skips the write for those – Peege151 Dec 02 '17 at 19:48
checked now it works, don't know why it has not worked before – Tim Dec 02 '17 at 19:51
No problem. So the big themes you learned from this was the callback! When you make an asynchronous operation (something that requires waiting for a response from a server) you need to 'wait' for the response. That is what the callback does! I'd take a look at this https://stackoverflow.com/questions/9596276/how-to-explain-callbacks-in-plain-english-how-are-they-different-from-calling-o – Peege151 Dec 02 '17 at 19:57
i found why it gives null, because the for loop loops through json>data.length which give the string length and not count ex how much rows are – Tim Dec 02 '17 at 19:59

NodeJS Json Data not fully inserting into database

1 Answers1