MQTT Topic split before output to MSSQL

Hi there!

I hope im posting in right section!

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;

MSSQL Push function:

d = new Date(),
dformat = [d.getMonth()+1,
    d.getDate(),
    d.getFullYear()].join('/')+' '+
    [d.getHours(),
    d.getMinutes(),
    d.getSeconds()].join(':');

pld =       "INSERT INTO [sandbox].[dbo].[MQTTData] "
pld = pld + "(Topic, Value, Timestamp, Building) "
pld = pld + "VALUES ('" + msg.topic + "', '" + msg.payload + "', '" + dformat + "','" + msg.building + "')"

msg.topic = ''
msg.payload = pld
msg.building = '666'
return msg;

But i cant get neccesary result.
I hope somebody can suggest simple solution. :slight_smile:

Hi, welcome to the forum.

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...

Demo flow...

[{"id":"619cb6bb044bde1d","type":"inject","z":"e78eac1c.8b9cf","name":"dummy MQTT data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"XXX/111_222_333/352311000743/sensors/123/measurements/derived_cpc","payloadType":"date","x":1630,"y":700,"wires":[["89efd6a77bf9de66"]]},{"id":"89efd6a77bf9de66","type":"function","z":"e78eac1c.8b9cf","name":"topic to object","func":"var str = msg.topic;\nvar parts = msg.topic.split(\"/\");\nmsg.payload = {\n    name: parseInt[0],\n    location: parseInt(parts[1].replace(/_/g, '')),\n    location2: parseInt(parts[2]),\n    id: parseInt(3),\n    try: parseInt(parts[4]),\n    status: parts[5],\n}\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1840,"y":700,"wires":[["5a66158cbe23166a","74561e31b78e58f0"]]},{"id":"74561e31b78e58f0","type":"MSSQL","z":"e78eac1c.8b9cf","mssqlCN":"8d9d212a.cea03","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"INSERT INTO mydb (\r\n    [name], \r\n    location, \r\n    location2, \r\n    id, \r\n    [try], \r\n    [status]\r\n) values (\r\n    @name, \r\n    @location, \r\n    @location2, \r\n    @id, \r\n    @try, \r\n    @status\r\n)\r\n","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[{"output":false,"name":"name","type":"VarChar","valueType":"msg","value":"payload.name","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"location","type":"Int","valueType":"msg","value":"payload.location","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"location2","type":"Int","valueType":"msg","value":"payload.location2","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"id","type":"VarChar","valueType":"msg","value":"payload.id","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"try","type":"int","valueType":"msg","value":"payload.try","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}},{"output":false,"name":"status","type":"VarChar","valueType":"msg","value":"payload.status","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":2080,"y":700,"wires":[[]]},{"id":"5a66158cbe23166a","type":"debug","z":"e78eac1c.8b9cf","name":"check values in debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1840,"y":760,"wires":[]},{"id":"8d9d212a.cea03","type":"MSSQL-CN","tdsVersion":"7_4","name":"My DB","server":"192.168.1.38","port":"1433","encyption":false,"trustServerCertificate":true,"database":"","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]
1 Like

Ups, im sorry! Thanks for correcting me!

Mate, what a demo flow!!!
I will definetly update this thread with my results!
Cheers!

So, now im getting error like this , when im trying to push data into MSSQL with your query:
image

A typo in function - try this...

var str = msg.topic;
var parts = msg.topic.split("/");
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],
}
return msg;

image

1 Like

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.

1 Like

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! :wink:

That old version (4.9y old) might still work on some systems but it has numerouse bugs and has far less features and far less users)

It is probably in a configuration node (visible in the RH side-bar under configuration nodes).

image

If you dont want to delete the config node you can ...

  • stop node-red
  • cd into your .node-red folder (usually ~/.node-red on linux and c:\users\USERNAME\.node-red on windows
    • e.g. cd ~/.node-red
      or
    • c:\users\USERNAME\.node-red
  • type npm uninstall node-red-contrib-mssql then press enter
  • type npm install node-red-contrib-mssql-plus then press enter
  • start node-red

But much easier to delete all MSSQL nodes (including config nodes from the RH side bar) then you can uninstall, install from the palette

1 Like

khm khm khm.... and its me again!
Now im fighting to get location2 data type! I tried many nvarchar(MAX);int;etc. nothing helps! :smiley:

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! :wink:

This is more of a database question than a node-red question but...

  • What is the database field set as?
  • What value are you sending in that field? (hint, use the debug node BEFORE the payload goes to SQL node)
  • What is the database field set as?
  • nvarchar(50)
  • What value are you sending in that field?
    -352311000743

Ok, maybe we shouldn't solve this problem!
I'll definetly update this post with end results!
Cheers!

Why? Shouldn't this be an Int field?

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)

1 Like

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!

If your database expects nvarchar(50) for all fields then remove all the parseInts and set the params on the MSSQL node to nvarchar

1 Like

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:

I tried to create another msg.payload.string , but still it doesnt provide MQTT data but fills in NULL
image

Thanks in advance!

add the value to the object...

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.

BX00Cy7yHi

https://nodered.org/docs/user-guide/messages

1 Like

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!

1 Like

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