Error: ER_PARSE_ERROR

#1

Good day, Below is json data coming from MQTT. I am trying to insert in mariadb.
"{"DeviceId":4,"Line":1,"outPin":"GPIO 7","count":50,"Time":"2018-10-08 12:04:48"}"

I did create function block with the following script.

var obj = msg.payload;
var json = JSON.parse(obj);

msg.topic ="INSERT INTO mytable1 (DeviceId,outPin,count,Time) VALUES (?,?,?,?,?)";
msg.payload = [json.DeviceId, json.outPin, json.count, json.Time];

return msg;

Error msg : "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"

msg.payload output is
array[4]

0: 4

1: "GPIO 7"

2: 50

3: "2018-10-08 12:13:06"

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=25
;

0 Likes

#2

You have too many question marks.

0 Likes

#3

In your insert you name 4 columns but have 5 ‘?’

0 Likes

#4

Cheers. this drives me crazy.

0 Likes