Data type being outputed when using ncd-wireless-node in node-red

needing to know what exact data type the string values being out-puted are…i.e. data.sensor_name so that I can align the sql database to match. It will not work directly with varchar(max) in sql. See photo below for string value examples (items in " ")

image

When I stick a string value in data.sensor_name…this is the error I get. I am using the mssql-contrib-plus node

image

The data output by the ncd-wireless node is a JSON object which contains information about a particular device. The values in the JSON object will be strings, decimal formatted numbers(floats), or integers.

If I am not understanding your question correctly please let me know.

Thank you,
Travis Elliott

For a reason I believe to be a variable type issue between SensorName column in sql (which is setup as a varchar(max = 100) and data.sensor_name type. When I replace {{{data.sensor_name}}} with ‘testing’ I have no issues. So it does take a string value, just not from data.sensor_name.

Do I need to convert the json string value in data.sensor_name to a varchar? If so what is the easiest way to do that?

INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES ({{{payload.temperature}}}*1.8+32,{{{payload.humidity}}},{{{data.nodeId}}},{{{data.battery}}},{{{data.sensor_name}}})

Flow:

[{"id":"24a7e6c1.bbd94a","type":"ncd-wireless-node","z":"a41e8b1b.0dadc","name":"Room 1 Encapsulation Sensor (Semi)","connection":"99bc8983.06925","config_comm":"99bc8983.06925","addr":"00:13:a2:00:41:a3:d1:cd","sensor_type":"1","auto_config":true,"node_id":0,"delay":"300","destination":"0000FFFF","power":4,"retries":10,"pan_id":"7FFF","change_enabled":"","change_pr":"0","change_interval":"0","cm_calibration":"60.6","bp_altitude":"0","bp_pressure":"0","bp_temp_prec":"0","bp_press_prec":"0","amgt_accel":"0","amgt_mag":"0","amgt_gyro":"0","impact_accel":"0","impact_data_rate":"4","impact_threshold":25,"impact_duration":1,"activ_interr_x":1,"activ_interr_y":2,"activ_interr_z":4,"activ_interr_op":8,"filtering":0,"data_rate":5,"time_series":0,"reading_type":1,"x":300,"y":300,"wires":[["df6ab3eb.c0147","93e2f5e.0e0a108","6b76aa4d.37758c","86b55769.fca548","febbac70.a19a2"]]},{"id":"86b55769.fca548","type":"MSSQL","z":"a41e8b1b.0dadc","mssqlCN":"48d548c2.d378f","name":"MSSQL","query":"INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES ({{{payload.temperature}}}*1.8+32,{{{payload.humidity}}},{{{data.nodeId}}},{{{data.battery}}},'again a test')","outField":"payload","returnType":"0","throwErrors":"0","x":840,"y":300,"wires":[[]]},{"id":"99bc8983.06925","type":"ncd-gateway-config","z":"","name":"Wireless Modem","comm_type":"tcp","ip_address":"sanitized","tcp_port":"sanitized","port":"","baudRate":"115200","pan_id":"7FFF","rssi":false},{"id":"48d548c2.d378f","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"sanitized","server":"sanitized","port":"1433","encyption":false,"database":"EnvironmentMonitoring","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5"}]

Hi,

Honestly I am not an SQL expert but one thing I did notice is sensor_type uses double quotes on it’s value and when you replace it with testing you use single quotes. Looking at this StackOverflow post it looks like single quotes are preferred in SQL. You could try modifying the output of the NCD Wireless node to convert those double quotes to single quotes and see what that does for you.

I believe the quotes are there in the debug window to indicate that it is a string value, but the quotes are not actually part of the value.

I added another relevant screen shot to to the main message. I don’t understand why it is looking for a column named Me, when the sql syntax is telling it that data.sensor_name is value.

I’m not familiar with the MSSQL node, but perhaps it needs you to target the msg object a la msg.data.sensor_name ?

I did attempt that as well, with no success.

Are you able to access other parts of the data but just not sensor_name?

I can access everything. I just cant send the string output values to the sql database. All numerical values go into the db just fine.

Yes, but can you send something like the temperature value to the SQL database?

Yes. (I edited my previous reply).

Can you post the Type variable to the db?

image

I also tried nvarchar(max)

Sorry, I meant can you send data.type to the db. Trying to see if it is all string values or just sensor_name.

The node spits out the same error with data.type

image

That error is indicating that your column names are incorrect. Something in your Insert statement is telling it to put the data in a column called ‘sensor_data’ which doesn’t exist.

If your insert statement still looks like:
INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES ({{{payload.temperature}}}*1.8+32,{{{payload.humidity}}},{{{data.nodeId}}},{{{data.battery}}},{{{data.sensor_name}}})

Then I would say it might be an issue with the MSSQL node as that looks like it should be target the columns (temp,humidity,nodeID,voltage,SensorName).

It was all about sql syntax. See the link below.

Great! Thank you for letting us know. Always nice to have a conclusion to a problem on the forum so others can reference it in the future.

Take care and let us know if we can help with anything else.