Ncd device string data to sql using mssql plus node

General
1

Not certain if I have the right area for this. I am having a heck of time trying to get a string value from an ncd device to a mssql database. The specific value I am attempting to get into the db is data.sensor_type. I am running the latest supported versions of node-red, node.js, and mssql plus node.

The error I am receiving from the node is calling it an invalid column, but I believe the sql syntax is correct.

image

I can force a string value (when entered directly into the sql syntax) and it works just fine getting into the db. All numerical values go into the db just fine. I have tried sensor_type (as string value) and it gives me the same type of error from the node.

Sql Syntax in the MSSQL node:

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"}]

Output area

Sql data types

2

The error in the mSSQL node says invalid column name "Humidity"

the insert statement you show used humidity are the column names case sensitive??

3

Can you post the full object output from MSSQL node? I added a propertymsg.query that is the final rendered SQL that is executed against the SQL server. It is for this kind of debugging exactly. You can then try running that in your SQL application if need be but I suspect you will spot your issue.

4

Look again at your screen shot, shouldn't you be accessing temperature and humidity from {{{data.sensor_data.temperature}}} and {{{data.sensor_data.humidity}}}?

You know there is a copy button appears to the right of each property in the debug sidebar to ensure no typos?

5

Steve-Mcl,
I noticed I may have not clarified the item I am trying to get into the db. data.sensor_name (Temperature/Humidity) is the sensor_name value.

Thanks for the msg.query info.

Is this what you are referring to for 'full output'?

INSERT INTO [temperaturehumidity] (temp,humidity,nodeID,voltage,SensorName) VALUES (18.86*1.8+32,29.59,0,3.2940600000000004,Temperature/Humidity)

After looking at it it appears that Temperature/Humidity (the sensor name) is missing ' before and after the value. So it should be 'Temperature/Humidity' to properly go into the sql db. I am assuming I need to append the value to include the apostrophes.

On the separate note you pointed out about temp and humidity data.....

It appears I can get it from either data.sensor_data.temperature (humidity) or payload.temperature (humidity)

image

6

That'll be it. Put them in the MSSQL node string e.g.

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

hah! Thank you. Just learning some sql syntax :slight_smile: