MSSQL-PLUS - passing array to UDT parameter (TVP param in a stored procedure)

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.

Coming soon...

1 Like

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?

Sure @Steve-Mcl. I'll test and give you the feedback.

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

Notes...

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.

@dhanasekar

I have now published a beta version to NPM

You can install like this...

  1. stop node-red
  2. open a command window
  3. cd your .node-red directory e.g...
    1. cd c:\Users\vstoresft2\.node-red
  4. npm uninstall node-red-contrib-mssql-plus
  5. npm install node-red-contrib-mssql-plus@beta
  6. start node-red

As before - "It includes a demo flow (import -> examples -> mssql-plus -> tvp)"

Please feedback

@dhanasekar did you have success with this?

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.


I have just followed the same instruction given in your previous reply. Am I missing something?

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 :slight_smile:

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?

Please check the below images.


Same payload array passed to TVP parameter
image
Change node rules are,
image

So, as I said, the format is incorrect.

Your UDT_Rolemanagement parameter is set to get data from msg.payload - a TVP MUST be formatted as I have pointed out.


Try this.

  1. Change the UDT parameter value to payload.table;

  2. BETWEEN the switch node and MSSQL, add a function node with this code inside...

//convert the payload array of objects to a flat array
var rows = msg.payload.map(e => [e.functionid, e.Accessid]);

//get the id and name from the payload.
var id = msg.payload.id;
var name = msg.payload.name;

//setup the table and its columns
var table = {};

table.columns = [
        {
            "name": "functionid",
            "type": "int"
        },
        {
            "name": "Accessid",
            "type": "int"
        }
    ];

//add the rows to the table
table.rows = rows;

//create a NEW payload with .id, .name and .table properties
msg.payload = {
  id: id,
  name: name,
  table: table
}

//return the updated message to the next node
return msg;
  1. Add a debug AFTER this function node and show me the output - it should look like this...
{ 
  id: 0,
  name: 'a3',
  table:
   { 
      columns: [
        { name: 'functionid', type: 'int' },
        { name: 'Accessid', type: 'int' },
      ], 
      rows: [ [2,1], [3,1] ] 
    } 
}

Wow. It is working like a charm.

Thank you @Steve-Mcl for your continuous support.

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.