Im facing problem with MQTT raw data input.
I need function that splits topic into multiple columns in MSSQL , but this split must happen before data input into database.
In my case, i need to split this topic XXX/111_222_333/352311000743/sensors/+/measurements/derived_cpc after each slash(/).
Im trying to do it with following function:
var str = msg.topic;
var parts = msg.topic.split("/");
msg.topic = {
name: parseInt[0],
location: parseInt(parts[1]),
location2: parseInt(parts[2]),
id: parseInt(parts[3]),
try: parseInt(parts[4]),
status: parts[5],
}
return msg;
Unfortunately you posted in the "creating nodes" section which is for developers creating contrib nodes (like the MSSQL-PLUS node is a non built in custom contrib node). No worries, I corrected the category to General
So the topic should always be a string but you are pretty much on the money.
Instead, this would work...
var str = msg.topic;
var parts = msg.topic.split("/");
msg.payload = {
name: parseInt[0],
location: parseInt(parts[1].replace(/_/g, '')),
location2: parseInt(parts[2]),
id: parts[3],
try: parseInt(parts[4]),
status: parts[5],
}
return msg;
NOTES:
I removed the parseInt from the id part as i suspect it is not a number (add it back in if I was wrong)
I do a replace of _ as parseInt("111_222_333") will result in 111 instead of 111222333
then feed that into the MSSQL-PLUS node like this...
Steve, I really appreciate your help, im new one here, and your help means, that this community rocks!
Still im getting "Must declare the scalar variable" , i tried to change in SQL: DECLARE @name nvarchar(MAX); - nada.
Is it possible to input these data without scalar variable but "simple"var?
I should apologize, im pretty green in JS.
Are you using MSSQL-PLUS? Do you see the variables I added in the screenshot?
Ps. Unless you click the reply button under my post (not the reply button at the very bottom of the thread) I don't know you have replied as I don't get a notification.
I see, we are using 2 different palletes!
Now im struggling with another problem, i have to uninstall my node-red-mssql which is EOL version I guess, but it shows as "in use" so i cant uninstall it from GUI. from centos7 npm uninstall, it shows that uninstall has happened, but still i can see that pallete.
I'll update this post after I solve this issue in my side.
Steve, thanks for trying to help, and leading me to correct path!
Wow, so detailed! I fixed my case as noob, just moving old_MSSQL folder away, did systemctl stop/start and installed new MSSQL module.
Thanks for helping out!
Either way, you need to align the parameters in the database with the paramater types set in the MSSQL node adn with the types in the function node (e.g. if you want to send a string, dont use parseInt since this turns the string into a number)
For other application integration. (Mandatory all fields must be nvarchar(50), except ID)
Even in my case it would be better to send string instead of parsed intiger! Which method would fit the best?
Thanks Steve!
Thanks Steve, i hope this will be my last question!
So it's working now as expected,but i have one more question:
Now we have devided topic and it works fabulous, but now i dont understand how to grab MQTT Value/Measurement:
let measurement = msg.payload; //<<grab value from MQTT payload
msg.payload = {
name: parts[0],
location: parseInt(parts[1].replace(/_/g, '')),
location2: parseInt(parts[2]),
id: parts[3],
try: parseInt(parts[4]),
status: parts[5],
measurement: measurement //<< add measurement to payload
}
then, in the MSSQL variables, point the new measurement variable source to msg.payload.measurement
This is a very basic (and important) discipline in node-red - you would do well to learn the below technique...
There’s a great page in the docs that will explain how to use the debug panel to find the right path to any data item.
Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.
I will definetly dive into node-red documentation which looks really neat after this little project will be solved !
I couldn't answer yesterday , as im new @ this forum!
Thanks Steve for diving into my problem and for great explanation!
Have a nice life mate , and see you around!
