Node red and relational database (mysql)?

General
41

Thanks for your help, but there is only one device that sends the mqtt topics (tamb, tcorp ...).

42

@Tefita

It is a very simple question that you have not answered.

When a message arrives on an MQTT topic, how does your flow know what patient it is for?

43

The flow does not know that, I just enter the patient's data manually so that it is stored in the "patients" table, and the CEDULA attribute is in the "data" relational table.

44

Ok - so at this point I don't know if you are still asking a question here.

45

Thanks for answering what I really want is that the attribute "CEDULA" continue to be stored with the data that comes from the topics but this only happens once. Is it necessary for me to open a new forum debate?

46

Please do not open another topic.

I'm not really clear on what you mean.

With your current flow, what do you get in the database? Can you show us an example of the data? And then can you show up what you would like the data to look like.

47

I get these results in the Database (Patients table and data table):

bd2
bd2.PNG1215×281 11.2 KB

I add the debug messages of each mysql node.bd3 bd4

But later the device keeps sending topics and I look for these to be stored continuously, but they are only stored once because I enter the attribute "CEDULA" only once.

48

Do you mean you want to update the existing row in the datos table where cedula=10002000 with the new data values coming from mqtt?

Or do you want to insert a new row each time a new set of data arrives?

49

Thank you for your answers: I am looking for continuously updating rows Tamb, Tcorp .... Ptcorp every time a new data arrives. But the data in the row "CEDULA" is kept and stored together with the other data, and this is updated only when a different attribute "CEDULA" is entered.

50

Just to be absolutely clear... you want one row for a given CEDULA value and you want that one row to update with the latest values? When a new CEDULA is entered, then a new row is added - or, if one already exists for that CEDULA, that row will be updated.

51

Answering the questions

  1. In the table "data" there is a row with the attribute CEDULA, and there are other rows for the data (Tamb, tcorp ...)
  2. The CEDULA row already exists and it must be updated when a new "CEDULA" data is entered.
52

@Tefita Can you give us a real world walk thru of what happens. Something like:

  1. a patient arrives in the office
  2. we enter his id (CEDULA??) into the system
  3. we then attach a bunch of sensors to him and the sensors start sending MTTQ msgs with data.
  4. when the test is done the patient leaves and a new patient enters and the process starts again.

That might give us an idea of what needs to be done becasue at this point, I'm confused.

53

It helps when you write down the sequence of events you want to happen. As @zenofmud suggests, a description of how this will get used, one step at a time, would help.

From what I think I understand, does the following sequence make sense?

  1. a new CEDULA is entered by the user

    1. store that CEDULA in Flow Context so we remember what it is for later
    2. check for an existing row in the table for that CEDULA. If it doesn't exist, insert a row for the CEDULA with blank values for the data

  2. an mqtt message arrives

    1. get the current CEDULA from Flow Context.
    2. At this point we know there's already a row in the table because of the action taken above (step 1.2). So we can generate the appropriate UPDATE sql statement to update that row with this piece of mqtt data
54

Thank you very much for answering @zenofmud, and that's what I'm looking for.

55

Thanks also @knolleary

  1. If there is a row "CEDULA" in the "patients" table.

2.1 I get the attribute "CEDULA", but only once.
2.2 How to generate the appropriate UPDATE sql statement to update that row with this piece of mqtt data ?

Could you please help me

56

Have you taken an sql tutorial to see how to construct an update statement? Try googling ‘mysql update tutorial’

57

Thanks for the help, but I have reviewed the mentioned information and I have added the following in the node function "funcion almacenar":

msg.topic ="INSERT INTO datos (Tamb, Tcorp, rcard, Dcaid, Acorp, Aritm, Ccaid, Ccorp, Critm, Pamb, Pcorp, cedula)  VALUES ('" + msg.payload.tamb + "','" +msg.payload.tcorp+ "','" +msg.payload.rcard+ "','" +msg.payload.dcaid+ "','" +msg.payload.acorp+ "','" +msg.payload.aritm+ "','" +msg.payload.ccaid+ "','" +msg.payload.ccorp+ "','" +msg.payload.critm+ "','" +msg.payload.pamb+ "','" +msg.payload.pcorp+ "','" +msg.payload.ced+ "') ON DUPLICATE KEY UPDATE  cedula='" +msg.payload.ced+ "'";

And I only get the one-time insertion of the data. Any suggestions
regards

58

You use an INSERT to add data the first time. If you want to change that data you do an UPDATE

59

Thanks for answering @zenofmud