Hello there,
I am trying to integrate node-red with different databases, Recently I working with mssql.
for that I am useing node-red-contrib-mssql-plus.
In this node I want to access msg.payload which output is in an array format.
how to put each array data into a table column of my DB?
hi and welcome to node-red forum.
Unfortunately, that is a loaded question that cannot be answered fully (as you have given too little information.
- what does the source array of data look like?
- Do you know how to write SQL statements?
- Are you familiar with node-red and how a msg is passed from node-to-node (if not watch this excellent Node-RED Essentials playlist from the developers)?
Inserting data into the database is easy - read the built in help ...
This is possibly enough to get you started...
[{"id":"5073e8a4.5e96e8","type":"MSSQL","z":"21106466.70e60c","mssqlCN":"3a2a3be.e1920c4","name":"","query":"INSERT INTO mytable (data)\nVALUES ({{{payload}}}","outField":"payload","returnType":0,"throwErrors":1,"x":740,"y":680,"wires":[["87cb162d.358eb8"]]},{"id":"dd7d061a.a1d0a8","type":"inject","z":"21106466.70e60c","name":"input data - [\"my\",\"data\",\"array\"]","topic":"","payload":"{}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":290,"y":680,"wires":[["82b3343a.2af888"]]},{"id":"82b3343a.2af888","type":"split","z":"21106466.70e60c","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":530,"y":680,"wires":[["ab98acab.3241","5073e8a4.5e96e8"]]},{"id":"ab98acab.3241","type":"debug","z":"21106466.70e60c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":740,"wires":[]},{"id":"87cb162d.358eb8","type":"debug","z":"21106466.70e60c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":770,"y":740,"wires":[]},{"id":"3a2a3be.e1920c4","type":"MSSQL-CN","z":null,"tdsVersion":"7_4","name":"","server":"","port":"1433","encyption":true,"database":"","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false}]
hello,
Thanks for quick reply.
here I attached my project flow.
flows.json (1.3 KB)
In my MSSQL quary i have 5 columns and Ii have to fill my array into each column
You have attached a flow with 1 node...
You haven't shown the debug output from before or after the MSSQL node so I have no idea what is in msg.payload so I still have no clue as to your problem
You haven't even said what error you get!
I am sorry, we are good - but we are not psychic 
regardless of that, there is no need to attach files -
```
just paste your flow between back ticks like this
```
That all said, this is easy to figure out if you use debug nodes....
like this...
Attach a debug BEFORE the MSSQL-PLUS node, check that msg.payload really is what you think it is.
Attach a debug node (set to "complete msg object") AFTER the MSSQL-PLUS node - check what is in msg.query - see that ALL values are populated by the mustache peroperties as you expect them to be.
All of this really does become second nature - once you watch that playlist.
hello,
here I am attached my debug log screen short and my flow file
flows (6).json (1.9 KB)
Thanks
Swapnil
2 things...
- Please dont attach small files
- Read the built in help...
compare your SQL mustache to the help info
This is the problem -
hello, In query i wrote like below.
i want my msg.payload array use to store in my mssql column.
in mysql I am easily store my data into it's table with help of this method
msg.topic = "INSERT INTO device_history ( name, email, username, pwd,num ) VALUES (?,?,?,?,?)";
msg.payload = ["swapmicro","swapnil.champaneria@dnkmail.in","swap","zzz@321",55.66];
return msg;
thanks
swapnil
go back and look at the example in the info tab of the mssql nodeinfo tab. What is the difference in the example query and your query for inserting the data from the msg?
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.
