How to retrieve integer value from SQLite for display and comparison

Good Day, I am new to the node red environment. The project at hand is a Smart Vending machine which is for my final year project. I have managed to get a bit done on my own by reading up and using advice from the forums. Currently I am struggling to correctly read data from my DB in order to display and use those values in other functions.

As you can see above I have a table populated with data , I require to retrieve the INT values from the Price column but when I do that I receive a the full payload object as "[{ Price: 20 }]" , i tried using the split function but best I got out was "20}]" but in a string format.

The topic request to the DB is " select Price from DB where ID == 1" which is working but as stated above its not returning just the INT value as required. Also i would like to store this value and compare it to the monetary amount the user will be required to insert into the machine.

I have tried everything with my given abilities to no avail, any help or recommendations would be highly appreciated.

Read the section Working with messages.

You don't split the values, that string you showed is JSON - use the JSON node to convert the JSON to a JS object then simply access msg.payload[0].Price

Trust me, spend 10 mins in that section of the very helpful extensive documentation - it will teach you about these things.

I don't think the sqlite node should return a string "[{ Price: 20 }]", I would have expected it to return the array itself [{ Price: 20 }]. If you feed it into a debug node you will see whether it is a string or an array. If it is already an array then you won't need the JSON node to convert it from string to array.

I thought the same but the OP said he managed to split it but gets "20}]"

Partly the reason I pointed the OP to the docs so he learns how to inspect the messages :+1:

Good point
@teladia is that string coming straight out of the sqlite node? If so which have you installed node-red-node-sqlite or one of the other sqlite nodes?

Hi Steve

Thanks for the feedback, I did give that a read and it definitely gave me a better understanding of the messages and its properties.

Yes I forgot to mention that it was passed through a JSON node before going into the split node. I have tried the above with and without using the JSON node before feeding into my function. I am however receiving the following error : "Function tried to send a message of type number"

From that error I take it that the function is actually just receiving the INT value of 20 but doesn't like that. Could you please explain why that is , or am I going about it wrongly?

Hi Colin

Sorry for that, as I stated to Steve I forgot to mention that I passed the data through a JSON node first , so that would explain the msg received. The SQLite is the original and does pass the proper object through. Thanks!

Hi Steve

I managed to used a change node to get the INT value straight out and store it as a flow by using the msg.payload[0].Price as you stated.
I will continue to see if I can create a function to compare that value against the users input amount.

1 Like

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.