Beta V0.5.0-beta - support "execute" and parameters

[Steve-Mcl]

Hi @bestlong , contributers and anyone passing...

I have added a branch called params

The purpose of these modification is 2 fold...

1. Improve protection against sql injection (through use of parameters)
2. permit sending TVP (Table Values Parameters) to stored procedures.

See this thread for some additional insight

The UI has numerous changes to permit static or dynamic entry of parameters.

I have tested the changes in node-red of several machines and the user beta testing has (almost) got it working on his machine.

---

If any one could test and feedback that would be great.

---

Install -

• stop node-red
• open a terminal window
• cd your .node-red directory e.g...
• npm uninstall node-red-contrib-mssql-plus
• npm install node-red-contrib-mssql-plus@beta
• start node-red

This is a demo flow...

[{"id":"3eb71b1f.47fdb4","type":"MSSQL","z":"595a5dd5.a963a4","mssqlCN":"a51e405c.10f64","name":"","query":"MyCustomStoredProcedure","outField":"payload","returnType":"1","throwErrors":"0","queryMode":"execute","params":[{"output":false,"name":"tvp","type":"TVP(TestType)","valueType":"json","value":"{\"columns\":[{\"name\":\"a\",\"type\":\"VarChar(50)\"},{\"name\":\"b\",\"type\":\"Int\"}],\"rows\":[[\"hello tvp\",777],[\"bye tvp\",888],[\"call the cops\",999]]}"}],"x":500,"y":520,"wires":[["bfde3540.a91018"]]},{"id":"614325ad.afd22c","type":"inject","z":"595a5dd5.a963a4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":500,"y":480,"wires":[["3eb71b1f.47fdb4"]]},{"id":"bfde3540.a91018","type":"debug","z":"595a5dd5.a963a4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":510,"y":580,"wires":[]},{"id":"a51e405c.10f64","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"Local SQL","server":"192.168.99.99","port":"1433","encyption":false,"database":"testdb","useUTC":false,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false}]

[Steve-Mcl]

2 beta testers success (as well as my on my own private machines)...

• https://discourse.nodered.org/t/mssql-plus-passing-array-to-udt-parameter-tvp-param-in-a-stored-procedure/30049/23
• https://discourse.nodered.org/t/sending-multiple-modbus-data-values-to-sql-server-azure-in-one-stored-procedure/30488/18

[Steve-Mcl]

I have released a 3rd & hopefully final beta on NPM (same install method npm install node-red-contrib-mssql-plus@beta)

This is mostly UI tweaking (underlying functionality relatively unchanged since beta.1)

I would still appreciate it if any of the other contributes or users would test and feedback before I publish changes into master / NPM.

[Steve-Mcl]

After several users and my own production testing, I am happy the additions are working well.

So I will close this issue and publish the changes,

Please re-open if you wish to discuss.