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
;