# Can not send data to table MariaDB, please help

**URL:** <https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324>\
**Category:** General\
**Created:** [8 August 2020 20:20 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324 "2020-08-08T20:20:03Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [8 August 2020 20:20 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/1 "2020-08-08T20:20:03Z")

</div>

I try to make sense how to connect with a MariaDB.

I use the following node:

> **[node-red-node-mysql](https://flows.nodered.org/node/node-red-node-mysql)**
>
> A Node-RED node to read and write to a MySQL database

I do have connection with my database.  
I've created the table in the database.

My function node to inject data in database node:

```auto
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

Can someone point me in the right direction?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 August 2020 21:08 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/2 "2020-08-08T21:08:28Z")

</div>

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](https://www.guru99.com/insert-into.html) it seems that you need backticks not single quotes.

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [8 August 2020 22:52 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/3 "2020-08-08T22:52:51Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [9 August 2020 03:49 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/4 "2020-08-09T03:49:58Z")

</div>

You need to insert strings with single quotes.

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 07:30 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/5 "2020-08-09T07:30:12Z")

</div>

Thank you.

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Nodi.Rubrum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/nodi.rubrum/32/107482_2.png) [@Nodi.Rubrum](https://discourse.nodered.org/u/Nodi.Rubrum)\
**Post date:** [9 August 2020 07:44 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/6 "2020-08-09T07:44:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 13:29 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/7 "2020-08-09T13:29:49Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 August 2020 14:19 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/8 "2020-08-09T14:19:10Z")

</div>

> [@WesleyFranken](#):
>
> the function node say it has an error.

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

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 14:34 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/9 "2020-08-09T14:34:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 August 2020 14:38 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/10 "2020-08-09T14:38:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 14:45 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/11 "2020-08-09T14:45:06Z")

</div>

Hi Colin,

```auto
[{"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"]]}]

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 August 2020 14:53 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/12 "2020-08-09T14:53:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 August 2020 14:55 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/13 "2020-08-09T14:55:04Z")

</div>

> [@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?

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 August 2020 14:58 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/14 "2020-08-09T14:58:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 15:18 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/15 "2020-08-09T15:18:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 August 2020 15:19 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/16 "2020-08-09T15:19:27Z")

</div>

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](https://discourse.nodered.org/t/function-node-flags-octal-assignments-with-leading-0s-warns-at-deploy-but-runs-fine/31346)

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 15:20 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/17 "2020-08-09T15:20:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 August 2020 15:21 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/18 "2020-08-09T15:21:55Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![WesleyFranken](https://avatars.discourse-cdn.com/v4/letter/w/51bf81/32.png) [@WesleyFranken](https://discourse.nodered.org/u/WesleyFranken)\
**Post date:** [9 August 2020 15:33 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/19 "2020-08-09T15:33:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [9 August 2020 15:35 UTC](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324/20 "2020-08-09T15:35:41Z")

</div>

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

[Next page](https://discourse.nodered.org/t/can-not-send-data-to-table-mariadb-please-help/31324.md?page=2)