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
– Ben Nyberg, Commented Dec 2, 2016 at 16:25
yes, want to insert json to two columns of the table. first column name data1 and second column name data2. — user2848031
– user2848031, Commented Dec 2, 2016 at 16:35

Nacht Blaad · Accepted Answer · 2016-12-12 16:24:13Z

1

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");}

answered Dec 12, 2016 at 16:24

Nacht Blaad

4252 silver badges7 bronze badges

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

Comments

elenst · Accepted Answer · 2016-12-02 16:23:01Z

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 2, 2016 at 16:23

elenst

4,0051 gold badge19 silver badges23 bronze badges

7 Comments

user2848031 Over a year ago

Thank you. Good catch! Now i'm getting { [Error: Column count doesn't match value count at row 1] code: 1136 }

elenst Over a year ago

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.

user2848031 Over a year ago

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.

elenst Over a year ago

Your code doesn't show how your json is initialized, so it's hard to say anything. Are you sure they aren't NULLs? 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.

user2848031 Over a year ago

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.

|

Walle Cyril · Accepted Answer · 2016-12-05 17:44:37Z

0

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

answered Dec 5, 2016 at 17:44

Walle Cyril

3,2674 gold badges28 silver badges60 bronze badges

Collectives™ on Stack Overflow

How to insert json data into MariaDB using Nodejs?

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related