Undefined tag in node-red

General
1

We are getting the value in the SQL but POD tag shows undefine.
tag property define as One Item in the IOT device.

debug output:
9/25/2020, 11:12:51 PMnode: 3cdb5ae5.2dec26
msg.payload : string[275]
"INSERT INTO [2WE TVS_NFFPP].[dbo].[MQTTData] (DateTime, POD, Pos1_op, Pos1_ETA, Pos1_Weight, Pos1_rdy,Pos1_Length,Pos1_1_2_cut,Pos1_All_cut,Pos1_DD,Detex,Number_Ply) VALUES ('9/25/2020 23:12:51','undefined', '58.047','50','9.91138','OFF','7.745','OFF','OFF','OFF','2200','3')"

Function Node:

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

dtstmp = new Date().toString();

pld =       "INSERT INTO [2WE TVS_NFFPP].[dbo].[MQTTData] "
pld = pld + "(DateTime, POD, Pos1_op, Pos1_ETA, Pos1_Weight, Pos1_rdy,Pos1_Length,Pos1_1_2_cut,Pos1_All_cut,Pos1_DD,Detex,Number_Ply) "
pld = pld + "VALUES ('"+ dformat+"','" + msg.payload.POD[0] + "', '" + msg.payload.Pos1_op + "','" + msg.payload.Pos1_ETA + "','" + msg.payload.Pos1_Weight + "','" + msg.payload.Pos1_rdy + "','" + msg.payload.Pos1_Length + "','" + msg.payload.Pos1_1_2_cut + "','" + msg.payload.Pos1_All_cut + "','" + msg.payload.Pos1_DD + "','" + msg.payload.Detex + "','" + msg.payload.Number_Ply + "')"

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

this is the tags
tags

In the CSV file, it shows POD-0 but we try with POD-0 also it shows 0 is not defined.

2

What SQL database?

Are we supposed to guess which one? :slight_smile:

Can you show a debug output?

Also, in order to make code more readable and importable it is important to surround your code with three backticks
```
like this
```

You can edit and correct your post by clicking the pencil icon.

See this post for more details - How to share code or flow json

3

Just to clarify, what we need to see is the data going into your function node.

4

thanks, it is working, the POD tag was array, so I convert into string and after the value is showing.

5

You never did say...

I am curious as to which database. If you are sending to MSSQL, there are better ways to achieve this.

If you are not interested, no worries.

Another question, that image looks like a redlion tag list - an I correct? I am curious how you are transmitting the data from redlion unit to node-red & what PLC you are getting data from.

6

Hi,
other ways you mean SQL bridge? please let me know your suggestion.

Yes, you are right we are using Redlion product. In redlion we have an MQTT connector, where we get the data into Node-red through MQTT broker and we are using Siemens PLC to connect with Redlion.

7

No I mean if you are using MSSQL (you still havent answered that) then you can avoid the messy string concatenation in the function node by using parameters (you also avoid SQL injection attacks)

image
image873×861 33.6 KB

As for the redlion - great product however in your scenario, an additional failure point. I would simply use the S7 nodes to comminicate node-red directly to PLC.

8

Yes we are using MSSQL Server.
in the redlion we are getting issue to sync with MSSQL per second, we want to sync the data every 5 second from redlion to MSSQL.(not possible in redlion product)
why we use the MQTT because redlion have the generic MQTT connector and why we use node-red because we want to combine the tags and generate unique ID that send to Barcode printer, i think that all possible in node-red.
Yes, you are right that we will make another failure point but that we will avoid later once our node-red flow will be perfectly running.

9

Well, if you use mssql-plus package, you get the ability to use parameters (like in my screen shot) and avoid sql injection.

Also, I think you can agree, entering parameters in the UI is much cleaner than

pld =       "INSERT INTO [2WE TVS_NFFPP].[dbo].[MQTTData] "
pld = pld + "(DateTime, POD, Pos1_op, Pos1_ETA, Pos1_Weight, Pos1_rdy,Pos1_Length,Pos1_1_2_cut,Pos1_All_cut,Pos1_DD,Detex,Number_Ply) "
pld = pld + "VALUES ('"+ dformat+"','" + msg.payload.POD[0] + "', '" + msg.payload.Pos1_op + "','" + msg.payload.Pos1_ETA + "','" + msg.payload.Pos1_Weight + "','" + msg.payload.Pos1_rdy + "','" + msg.payload.Pos1_Length + "','" + msg.payload.Pos1_1_2_cut + "','" + msg.payload.Pos1_All_cut + "','" + msg.payload.Pos1_DD + "','" + msg.payload.Detex + "','" + msg.payload.Number_Ply + "')"
10

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