Query to extract data from MariaDB using SELECT statement to link with the RFID card detected

General
22

Thanks for your detailed message. Now, I have changes the data that I was getting via MQTT listenr node to just the uID(context.data.uID), there is no more a string attached with it. Now, it just reads the uID from the MQTT node.
So, the msg.payload is now just the context.data.uID which I want to relate with the uID of the column in my database. Please check the attached image.

IMG_20191009_150654
IMG_20191009_150654.jpg1478×1238 1.03 MB

23

It is a sample database to understand my problem

24

So do you have in msg.payload something like "24924824148"?

If so then exactly what do you want to appear in the select statement?

25

My flow on Nodered looks like this

IMG_20191009_151808
IMG_20191009_151808.jpg4160×1092 2.24 MB

26

Thanks @ukmoose for your reply, I am getting in the end,
after the sql node

msg.payload:array[0]

27

So the one place you need to place a debug node to see what query you are sending is the one place you don't have a debug.

If you want to check the query you you are sending TO the database, you need to see the message that goes IN to the database node.
Place a debug node there, and set it to Output complete message object ( so that you can see the msg.topic and the msg.payload)

28

In the first msg.payload, I am getting the ouput as one of the uID from my table(depends on which Tag is being detected).

In the select statement, I want the item, date... column with the detected Tag to be sent over MQTT to my remote machine, also I have one debug node connected with my SQL node, to see what data is the output.

29

Please pay attention! Put a debug node on the output of the node feeding the sql node. You need to see what select statement you are feeding into the sql node.
See what the debug node shows and check the sql. Does that look right? There is no point looking at the output of the sql until you know you are feeding it the correct select statement.
If it does not look right but you do not understand how to fix it then put another debug node showing the input to the function and screenshot the output from the two debug nodes (not the one on the output of the sql node) and paste them here. Also show us what you have in the function node.
Note that the fact that the value is coming via mqtt from your remote machine is completely irrelevant to this question.

30

IMG_20191009_162656
IMG_20191009_162656.jpg3090×1036 1.42 MB
IMG_20191009_162717
IMG_20191009_162717.jpg3118×725 555 KB

31

I am really sorry for the poor quality but because I am using Nodered on Rpi and I do not know how to take screenshot on Pi, so I just clicked and sent.

Thanks for helping me out!

32

I think the mistake in the sql is that you asking for uID to be the number 732.... whereas you want it to be string, so it needs to be surrounded by quotes
You can do that either using
msg.topic = "SELECT * from TAG_READ WHERE 'uID' = '" +msg.payload+ "'"
or you can use the more modern and easier to read string interpolation as in the link that I posted

`msg.topic = "SELECT * from TAG_READ WHERE 'uID' = '${msg.payload}'"`
33

This is the output I am getting after SQL node by using both the above methods:

SELECT * from TAG_READ WHERE 'uID' = '${msg.payload}' : msg.payload : array[0]

[ empty ]

34

Look at the debug going IN to your database node again

35

In the second one those are backticks surrounding the string. As described in the link I posted. I suspect you have not done that.
Also I do not believe that the first one gave that query as $ does not appear in my suggestion.

36

I'm really sorry but I'm out right now. Thanks for all your help, I will continue with your recommendations tomorrow once again and will try to find a solution

37

No need to apologise. No-one expects instant response, we all have lives outside of node-red. Well most of us do at least.

1 Like
38

Thanks Colin for your suggestion. Now the scenario looks like as below, after implementing the procedure you advised me.

  1. Before that, I would like to mention that now my msg.payload is just a 'uID' as I have removed the string "RFID detected is:". So that I can just get my uID for context.data.uID from altairsmartcore via MQTT.

  2. Below shows the function node syntax that I used and the 2 outputs that I got before and after the function node, but the debug node after my SQL node is not giving any output.

  3. Is it necessary for the column in my Database to be a "Primary Key" to be able to extract data when equated with msg.payload?

Thanks a lot for having patience and answering my not so wise questions. Please find the images attached.

IMG_20191010_111730
IMG_20191010_111730.jpg2080×1560 1.82 MB

IMG_20191010_111734
IMG_20191010_111734.jpg2080×1560 1.49 MB

Best Regards

39

@ukmoose I have shared the screenshot of my INPUT to Database node below

40

In your function node I would rather use something like this.

let data = msg.payload;

msg.topic = 'SELECT * from TAG_READ WHERE uID = ?';
msg.payload = [data];

return msg;

The query is passed in the msg.topic as usual, but having the ? placed for each parameter. The values are passed in the same order in your msg.payload as an array.

Otherwise you can end up with some nasty SQL injections.

1 Like
41

One other thing I saw. Don't use 'uID', just uID in your query. You are literally comparing two strings, and not evaluating against your uID column.

Say your payload is the string '12345678', then your WHERE clause would look like 'uID' = '12345678'. This condition will never be true, thus the empty result.

(Unless your uID is literally 'uID' of course, then you would receive the content of the whole table :boom::grinning:)