Can not send data to table MariaDB, please help

[WesleyFranken]

Thank you.

I've found out that the following is working (function node into node-red-node-mysql node):

client = 'client1'
project = 'project1'
meter_id = 'meter_id1'
date = msg.payload;
value = 1
msg.topic = "INSERT INTO `digitalempire`.`meterdata` (`client`, `project`, `meter_id`, `date`, `value`) VALUES ('client1, 'project1', 'meter1', '2020-08-09 09:03:55', '10');"
return msg;

Now I only need to figure out how to implement the placeholders.

[Nodi.Rubrum]

Using a template node is one way to avoid the injection risk scenario. I have seen a thread in the forum on this, just have not found it yet.

[WesleyFranken]

The following is working (function node that pushes data into node-red-node-mysql node):

var client = "client5";
var project = "project5";
var meter_id = "meter_id5";
var year = 2020;
var month = 09;
var day = 09;
var hour = 09;
var minutes = 03;
var seconds = 56;
var date = year + "-"+ month +"-"+ day +" "+ hour +":"+ minutes +":"+ seconds;
var value = 5;
msg.topic = "INSERT INTO `digitalempire`.`meterdata` (`client`, `project`, `meter_id`, `date`, `value`) VALUES ('"+ client +"', '"+ project +"', '"+ meter_id +"', '"+ date +"', '"+ value +"');";
return msg;

The only thing what's odd is that the function node say it has an error. But the function node does work and the data appears in the database. If I have a fix within 2 months I will send an update.

Thanks again Nodi.Rbrum. You gave me the push in the right direction.

[Colin]

@WesleyFranken

the function node say it has an error.

What is the error (hover over the error indication) and which line is it?

[WesleyFranken]

Hi Colin,

That is the strange thing, it does say it has an error but when I open the node it doesn't give any error on any line of code? When I hover on the error indicator on the node it gives the message:

Invalid properties:

• noerrr

[Colin]

Export that node (select it then Export to clipboard in the hamburger menu) and paste it here using the </> button.

[WesleyFranken]

Hi Colin,

[{"id":"e726cd44.7e1e4","type":"function","z":"56c8b957.4603b8","name":"MySQL Function","func":"var client = \"client5\";\nvar project = \"project5\";\nvar meter_id = \"meter_id5\";\nvar year = 2020;\nvar month = 09;\nvar day = 09;\nvar hour = 09;\nvar minutes = 03;\nvar seconds = 56;\nvar date = year + \"-\"+ month +\"-\"+ day +\" \"+ hour +\":\"+ minutes +\":\"+ seconds;\nvar value = 5;\nmsg.topic = \"INSERT INTO `digitalempire`.`meterdata` (`client`, `project`, `meter_id`, `date`, `value`) VALUES ('\"+ client +\"', '\"+ project +\"', '\"+ meter_id +\"', '\"+ date +\"', '\"+ value +\"');\";\nreturn msg;","outputs":1,"noerr":1,"initialize":"","finalize":"","x":650,"y":320,"wires":[["63b05a97.8dfac4"]]}]

[zenofmud]

The leading 0's in month, day, hour and minute is causing it.

[Colin]

@zenofmud

The leading 0 's in month, day, hour and minute is causing it.

Yes, I just worked that out. Is it not legal javascript to have a leading 0 on a number?

[zenofmud]

Found this in stack overflow

A leading 0 indicates an octal number in JavaScript. An octal number cannot contain an 8; therefore, that number is invalid. Moreover, JSON doesn't (officially) support octal numbers, so formally the JSON is invalid, even if the number would not contain an 8. Some parsers do support it though, which may lead to some confusion. Other parsers will recognize it as an invalid sequence and will throw an error, although the exact explanation they give may differ.

since the assignment is
var month = 09;
and 9 is not an octal number, I'd guess that is why it was flagged

I'm going to open a seperate thread about just this issue.

[WesleyFranken]

How did you found out? Just asking out of interest.

[zenofmud]

I imported your function and then commented out all the lines, found it was ok, so uncommented lines till the problem occurred.

The thread about the octal issue is Function node flags octal assignments with leading 0's, warns at deploy but runs fine

[WesleyFranken]

Ok. Thank you very much for your (zenofmud en colin) effort.

[Colin]

@WesleyFranken the solution is to remove all the leading zeros from the numbers.

However, what are you trying to achieve with the date? I presume what you are doing here is not the final solution. If you want to write the current date/time then you can specify in the database table design that you want the current date/time to be written automatically when you insert a record, then you don't have to do anything. Alternatively I would have expected that the database would expect an ISO format string for writing a timestamp rather than the string you are providing.

[WesleyFranken]

I try to export meterdata to a MariaDB so I can look to this energie data later. This data needs to be collected beacause of the EED and the controllers used in location are Unipi Axon PLC with Node-RED.

[Colin]

I was asking what you are trying to do with the date (not the data).

[WesleyFranken]

I try to deliver the timestap in a way that the MariaDB accepts.

I can simulate an inject with the app HeidiSQL and this is what I got back:

INSERT INTO digitalempire.meterdata (client, project, meter_id, date, value) VALUES ('client7', 'project7', 'meter_id7', '2020-08-09 17:37:37', '7');

So this is the reason I om chopping the timestamp into bits and pieces and then shuffle them in a way that works.

I hope I make sense

[Colin]

Are you trying to write the current time? If so then use a Timestamp column and tell it to automatically insert the current time for you, DEFAULT CURRENT_TIMESTAMP. Then you don't have to do anything.

[WesleyFranken]

Thank you all very much!

My issues are completely resolved.