Uploading data to a mySQL database

General
1

hello,

i'm trying to upload diffrent variables to my mySQL database but i can't get it to inject the info correctly. this has something to do with my queries. Can someone help me?

This is one of the queries i'm using:

INSERT INTO algemeen
    (machine, persoon, status, overtime, timestamp)
VALUES
    ("+ flow.get('machine') +","
    + flow.get('persoon') +","
    + flow.get('statusGoed') +","
    + flow.get('overtime') +",
    CURRENT_TIMESTAMP);
2

Are you putting that into msg.topic? I think you are just missing a quote or two.
Edit - If your data contains strings you may need to wrap it in single quotes, for example
+ " '" + flow.get('machine') +"'," (Single quotes inside the double quotes)

msg.topic = "INSERT INTO algemeen  (machine, persoon, status, overtime, timestamp) "
+ "VALUES  ("
+ flow.get('machine') +","
+ flow.get('persoon') +","
+ flow.get('statusGoed') +","
+ flow.get('overtime') +","
+ CURRENT_TIMESTAMP);

Do you actually have to give a value to timestamp or does the database definition give it a default?

I have seen other, perhaps cleaner, ways explained in this forum to insert records.

3

The template node is also good for building strings like this, with embedded quotes...

image

4

Also, before you feed it into the db node feed it into a debug node set to Output Complete Message and check that msg.topic contains what you expect.

5

thanks, i already saw one of your posts and that's what i used

6

My query does not contain strings so thats unnecessary.

image
image633Ă—678 23.7 KB

and i did put the query in the topic.

You can probably configure the database in a way that it automatically logs the timestamp but this works quite well so i'm not going to change it

7

image
image1579Ă—526 86.2 KB

this is the output i got:

INSERT INTO algemeen 
(machine, persoon, status, overtime, timestamp) VALUES ("+ flow.get('machine') +, + flow.get('persoon') +, + flow.get('statusGoed') +, + flow.get('overtime') +, CURRENT_TIMESTAMP); : msg.payload : string[4]
8

update:
I think i managed to fix the query but i got a new error code

Current query:

INSERT INTO algemeen
    (machine, persoon, status, overtime)
VALUES
    (flow.get('machine') +','
    + flow.get('persoon') +','
    + flow.get('statusGoed') +','
    + flow.get('overtime'));

error message:

"Error: ER_PROCACCESS_DENIED_ERROR: execute command denied to user 'lgout_dev'@'%' for routine 'flow.get'"
9

Is that the user you have put in the mysql node? It doesn't look like a valid user name.

10

image
yes it is.

11

I misinterpreted the error. It is saying user lgout_dev on machine %. I don't know what the % means.
Can you connect to the database using a different sql client from the machine running node-red?

12

well the strange thing is. i can recieve data from the database. I manually put some data in the database and i am able to recieve that data.

13

Does the user have privileges to write to the database?

14

In that case it is the permissions that the user has in the database. The user possibly only has read permissions. If the permissions look ok then can that user write to the database from the machine running node red, using a different mysql client (such as the command line client)?

15

@zenofmud & @Colin I thought that as well but i checked of the user has the required permission and those are all selected so that can't be the problem. But what dou you mean with client? You mean a library you can download from the manage palette section?

16

A client is an application using another application. So in this case node-red is a client trying to use the mysql server. In order to determine whether it is a node-red problem then you can connect to the server using a different piece of s/w. Such as this one: https://dev.mysql.com/doc/refman/8.0/en/mysql.html

17

The syntax error shown in your debug output is complaining about the "+" signs in your select statement. This section is written as if it is being interpreted by a function node, NOT the template node -- which just does simple string substitution. If all of your values are numbers, then your template should look more like this:

(
    {{ flow.get('machine') }},
    {{ flow.get('persoon') }},
    {{ flow.get('statusGoed') }},
    {{ flow.get('overtime') }}
)

This will only work IF mustache substitution works for flow.get('xxx') inside the curly braces -- which I admit I have never tried to do. The use of flow context should generally be avoided, and the query values would then come from msg fields instead.

1 Like
18

I have tried your piece of query code which looks like this:

INSERT INTO algemeen
    (machine, persoon, status, overtime)
VALUES
(
    {{ flow.get('machine') }},
    {{ flow.get('persoon') }},
    {{ flow.get('statusGoed') }},
    {{ flow.get('overtime') }}
)

unfortunatly i'm still getting an error code:

"Invalid property expression: unexpected ' at position 4"
19

Add a debug node (set to display the complete msg object) connected to the output of the template. Run the flow and expand the debug output to show the topic and copy and paste that to a reply. This way we can see what the statement is.

20

I thought you had got past the syntax error and were getting the permission denied error now.