How to insert json data into MariaDB using Nodejs?

Question

I'm inserting JSON data into MariaDB using NodeJs. Getting below error while inserting data. Please advise what cause to get error. Actually Column data1 no empty or null values.Why am i getting below error ?

{ [Error: Column 'data1' cannot be null] code: 1048 }

Table Structure

CREATE TABLE `from_excel` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `data1` VARCHAR(50) NULL DEFAULT NULL,
    `data2` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`ID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

Code which i'm using to insert data.

var Client = require('mariasql');
var c = new Client({
  host     : 'localhost',
  user     : 'xxxx',
  password : 'xxxx',
  db : 'Metrics'
});




const workbook = xlsx.readFile(__dirname + '/test.xlsx');
const worksheet = workbook.Sheets[workbook.SheetNames[0]];
var json=xlsx.utils.sheet_to_json(worksheet);
console.log(json.length);
      for(var i=0;i<json.length;i++)
    {
        var post  = {data1: json[i].data1, data2: json[i].data2};

        var sql = c.query('INSERT INTO elements_from_excel (data1,data2) VALUES (?,?)', post, function(err, result) {
            console.log(sql);
        if(err){console.log(err);}
            else {console.log("success");}

     });

    }
    c.end();

Do you want the JSON inserted as a string? Or are `data1` and `data2` columns that you want the values inserted into? — Ben Nyberg, Dec 02 '16 at 16:25
yes, want to insert json to two columns of the table. first column name data1 and second column name data2. — user2848031, Dec 02 '16 at 16:35

score 1 · Answer 1 · answered Dec 12 '16 at 16:24

What could be happening is that the resulting insert statement being run is as follows:

INSERT into from_excel (data1, data2) VALUES (`data1` = \'data1value\', `data2` = \'value\', ?)

Try replacing the query string with the following instead:

var post  = {data1: json[i].data1, data2: json[i].data2};

var sql = c.query('INSERT INTO from_excel SET ?', post, function(err, result) {
            console.log(sql);
        if(err){console.log(err);}
            else {console.log("success");}

score 0 · Answer 2 · answered Dec 02 '16 at 16:23

0

It should be INSERT INTO from_excel VALUES (?), although it's quite possible that you'll encounter other errors when you fix this one.

answered Dec 02 '16 at 16:23

elenst

3,839
1
15
22

Thank you. Good catch! Now i'm getting { [Error: Column count doesn't match value count at row 1] code: 1136 } – user2848031 Dec 02 '16 at 16:35
That's because you are inserting one value, but apparently have more than one column in the table. Then it should be `INSERT INTO from_excel (column_name) VALUES (?)`. Or, if it's two columns, then `INSERT INTO from _excel (data1, data2) VALUES (?, ?)` and bind accordingly. – elenst Dec 02 '16 at 16:37
Thanks. Now inserting NULL values only. So i made not null in the table structure and its giving an error data1 cannot be null] code: 1048.But no null value in data1 column. Please advise. – user2848031 Dec 02 '16 at 17:04
Your code doesn't show how your `json` is initialized, so it's hard to say anything. Are you sure they aren't `NULL`s? For starters, paste your updated code, and also add debug printing before running the query, with the values that you bind to the query. In the initial error we could see that the server was receiving explicit `NULL` in the query, it's not a default that it uses, so the problem must be in the code. – elenst Dec 02 '16 at 17:33
I have updated the code. I am reading the value from excel file and converting into json and inserting into DB.Actually i can insert directly without converting json. But i have one scenario to do same.I have only one row in the excel, though i'm getting same error. – user2848031 Dec 02 '16 at 17:58
Okay, so, `sheet_to_json` returns an array of JSON objects, and `json[i]` is a JSON object, is it right? But then, aren't you supposed to parse it with `JSON.parse` or something, before referring to the elements `data1` and `data2`? I think it used to be so, maybe things have changed. – elenst Dec 02 '16 at 18:37
There is no such thing as JSON object. You should say Js Object or Object or JSON string – Walle Cyril Dec 05 '16 at 17:42

score 0 · Answer 3 · answered Dec 05 '16 at 17:44

0

Make sure the function you are calling receive the exact type of data they expect.

answered Dec 05 '16 at 17:44

Walle Cyril

3,087
4
23
55

How to insert json data into MariaDB using Nodejs?

3 Answers3