Insert array of json data in mariadb

General
1

msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?)";
msg.payload = [{"DeviceId": 1,
"Line": 1,
"outPIN": "GPIO1",
"count": 15,
"Time": "2018-09-23 08:14:45"},
{"DeviceId": 1,
"Line": 1,
"outPIN": "GPIO1",
"count": 15,
"Time": "2018-09-23 08:15:13"},
{"DeviceId": 1,
"Line": 1,
"outPIN": "GPIO1",
"count": 15,
"Time": "2018-09-23 10:45:36"},
{"DeviceId": 1,
"Line": 1,
"outPIN": "GPIO1",
"count": 15,
"Time": "2018-09-23 10:48:51"}]
return msg;

Error message : msg : error

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"

2

Is this a duplicate of your previous post?

3

Initially i try with single array set of data. Now i am trying with set of array data.

4

Should be ,);"

5

msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?,);"

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"

6

One comma too many inside the brackets!

7

Still error persist.

8

msg.topic = "Insert into mytable(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?);"

9

msg.topic = "Insert into mytable1(DeviceId,Line,outPIN,count,Time) values (?,?,?,?,?);"

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?)' at line 1"

Just for info on mytable1:
CREATE TABLE mytable1 (
ID INT(11) NOT NULL AUTO_INCREMENT,
DeviceId INT(5) NULL DEFAULT NULL,
Line INT(5) NULL DEFAULT NULL,
outPIN VARCHAR(10) NULL DEFAULT NULL COMMENT 'GPIO',
count INT(5) NULL DEFAULT NULL,
Time TIMESTAMP NOT NULL DEFAULT '',
PRIMARY KEY (ID)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=37
;

10

I would suggest you step back and make sure you can do an insert with actual values before moving onto the ? marks and use the values from the forst element in the array.

11

msg.topic = "Insert into mytable1 (DeviceId,Line,outPIN,count,Time) values ( '1','1', 'GPIO1','15','2018-09-23 08:14:45')";

Its works only on this format

12

Does it work in the form you posted in a different thread, with question marks and a simple array of values?

13

Nope, doesn't work.

14

Look in the mariadb log to see exactly what query it is being given.

15

Take a look at https://www.w3schools.com/nodejs/nodejs_mysql_insert.asp the section " Insert Multiple Records"

In it the data is an array of arrays ie instead of [{...},{...}] they use [[...],[...]] then only use one ? in the insert

1 Like
16

Which node-red node are you using to insert into mariadb? Does it even support passing in a array of data to do multiple inserts? I'm not aware of any node that does support doing that.

17

Can I just check that you mean you cannot insert one row by passing in an array of values in the payload such as [ '1','1', 'GPIO1','15','2018-09-23 08:14:45']. If so then as I suggested look in the mariadb log, but also give us the information @knolleary has asked for.

18

Its doesn't work on question mark and work in this format
as [ '1','1', 'GPIO1','15','2018-09-23 08:14:45']

19

Please try and answer questions asked, it is difficult to help if you don't, you have not answered @knolleary's question as to which node you are using to access the data (possible one of the node-red-contrib nodes). Also you have not said what you see in the mariadb log, which can show you exactly the query that is failing.

20

Hi Members, sorry for silence. I do manage to resolve the issue. Basically i didn't understand how the mariadb works with json data type format.

MariaDB starting with version 10.2.7.

With attached link the shows clearly the json data type works in single column of the table. We can have many columns if needed.

Thank you for supports