Hi,
I am trying to execute stored procedure which is having UDT parameter using node-red-contrib-mssql-plus node.
the query i used is,
exec Rolemaanagement '{{{payload.name}}}',{{{payload.id}}},{{{payload}}};
The msg.payload has array data for UDT parameter.
And I am getting the below error from MSSQL node.
I don't know how to send array object as parameter in MSSQL. Kindly share your inputs if anybody knows the solution.
As you can see from msg.query you are not sending valid SQL.
What does a valid SQL statement look like for that stored procedure?
Thanks for your reply @Steve-Mcl.
The same procedure i have executed successfully in c#.Net platform. In this I am passing DataTable as parameter value.
I am using visual studio to execute this code. DT is a object for DataTable class. And it's declaration statement looks like DataTable DT = new DataTable();
below image shows the debug output of DT
In node-red, msg.payload having the same table data.
Hope this information is sufficient.
The node doesn't support parameters - you must generate valid SQL strings.
So can you do this with standard types (strings, numbers etc) in a normal SQL statement? If yes, you can do it with this node.
thanks @Steve-Mcl.
hope this features will be added in further updates in mssql node.
So that we can execute more complex sql queries also.
I'm gonna look at the complexity- see if I can implement this easily.
I know the underlying libraries do support it but I have to figure out how parameters types will be created at node-red side and passed to the command. It has to be easy for the user otherwise I'll get inundated with questions.
Hi @Steve-Mcl,
Good to see this. your post confirms parameter type is varchar and integer. can we send the complete object array as a parameter?. so that we can send table data to UDT type parameters also.
UDT is one of the options but I need help testing that.
Would you be willing to beta test?
Hi @dhanasekar
In GIT now, under branch params (link)
I have contacted a co-author regarding publishing a beta to NPM but until then, you can install from github if you feel up to it? you can git clone https://github.com/bestlong/node-red-contrib-mssql-plus.git and npm install ~/node-red-contrib-mssql-plus - OR - install direct from github npm install bestlong/node-red-contrib-mssql-plus#params --save
UDT and some other types are "not supported" (this is a tedious driver limitiation)
TVP can only be used with stored procedure (this is tedious driver stipulation)
Using the example sql type and procedure here, you would need data in the value to be in the following format...
{
"columns": [
{
"name": "a",
"type": "VarChar(50)"
},
{
"name": "b",
"type": "Int"
}
],
"rows": [
[ "hello tvp", 777 ],
[ "bye tvp", 888 ],
[ "call the cops", 999 ]
]
}
this flow works with the above example...
[{"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}]
@dhanasekar I have further updated the github repository (now V0.5.0-beta.1)
It includes a demo flow (import -> examples -> mssql-plus -> tvp)
Installation is same as previous post.
Please feedback.
Dear @Steve-Mcl,
I am not able to succeed with the node installation. I have tried like the picture shown below.
and also I have downloaded files from github and tried like below,
I am a newbie for github. Please correct if I am wrong.
Hi. Don't worry about it. I am preparing a better beta which I will publish to npm. I didn't like the radio buttons (due to the override method I had to employ) so I'm changing things to typedInputs to provide clearer functionality and more flexibility.
Should be ready later today.
I have now published a beta version to NPM
You can install like this...
cd your .node-red directory e.g...
cd c:\Users\vstoresft2\.node-rednpm uninstall node-red-contrib-mssql-plusnpm install node-red-contrib-mssql-plus@betaAs before - "It includes a demo flow (import -> examples -> mssql-plus -> tvp)"
Please feedback
Hi @Steve-Mcl,
Sorry for the delay in response. After installation npm install node-red-contrib-mssql-plus@beta. I am not able to start node-red successfully.
Not good. I assume it was working immediately before?
NOTE: dont worry, all is not lost (we can fix this)
First, please make a backup of your flow files from the .node-red folder (they will be called flowsxxxx.json)
On to your issue - please check this thread by @knolleary (there is an edge case he wishes to track down - you appear to have hit it)
if you edit the file C:\Users\vstoresft2\AppData\Roaming\npm\node_modules\node-red\node_modules@node-red\registry\lib\registry.js and look at line 186 you should see:
set.types.forEach(function(t) {
if (nodeTypeToId.hasOwnProperty(t)) {
Please add the following immediately before it:
// START DEBUG
if (!set.types) {
console.log("Module:",module.name);
console.log("Path:",module.path);
console.log("Set:",setName);
}
// END DEBUG
The next time you restart Node-RED you'll get a bit more information in the log about what node module its loading when it hits the error
I will follow up shortly on how we can get you running once more
Hi @Steve-Mcl,
Node-red started after deleting ui-etable node.
Now I have checked with TYP type parameter. It shows invalid table error. But the msg.payload has proper table value in object array format. This is my configuration,
Glad it wasnt my work that caused your issue 
Looking at your parameters values, you have msg.payload and msg.payload.name
That tells me your parameter data is NOT formatted correctly.
The format of the TVP parameter must be like this...
{
"columns": [
{
"name": "a",
"type": "VarChar(50)"
},
{
"name": "b",
"type": "VarChar(50)"
}
],
"rows": [
[ "row 1 col 1", "row 1 col 2" ],
[ "row 2 col 1", "row 2 col 2" ],
]
}
As your second parameter GroupName has a value of msg.payload.name that instantly tells me your TVP parameter msg.payload is incorrect.
Can you show me the format of the msg.payload BEFORE it enters the MSSQL node?
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy