I try to make sense how to connect with a MariaDB.
I use the following node:
I do have connection with my database.
I've created the table in the database.
My function node to inject data in database node:
client = 'client1'
project = 'project1'
meter_id = 'meter_id1'
date = msg.payload;
value = 1
msg.topic = "INSERT INTO 'meterdata' ( 'client', 'project', 'meter_id', 'date', 'value') VALUES ( "+ client+", "+ project +", "+ meter_id +", "+ date +", "+ value +");"
return msg;
After I inject I get the following message:
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 ''meterdata' ( 'client', 'project', 'meter_id', 'date', 'value') VALUES ( clie...' at line 1
Start by using a sql statement that works from another client, such as the mysql command line (I don't know whether that can be used with mariadb, if not then whatever the equivalent is). Then at least you know you have a valid query.
However the thing that jumps out at me is that you don't seem to have the right sort of ticks around the database name. Looking at https://www.guru99.com/insert-into.html it seems that you need backticks not single quotes.
If you are using Windows 10? You can use HeidiSQL client application with mariaDB. It has a very friendly interface for Windows 10. It has a nice query editor, that provides details feedback on SQL query syntax errors.
As others have noted, you have a number of errors in your SQL query syntax. Strongly suggest you use a SQL query syntax reference and correct your query.
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.
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:
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 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.
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.
