How to insert an array into mssql?

General
1

DECLARE @val1 NVARCHAR(MAX)
DECLARE @val2 NVARCHAR(MAX)
DECLARE @myVal NVARCHAR(MAX)

SET @myVal={{{msg.payload}}};
SET @val1= myVal[0];
SET @val2= myVal[1];

INSERT INTO SPLT
(
VALUE1,
VALUE2
)
VALUES
(
@val1,
'value'
)

2

im getting some syntax error. anyone please help.

3

Hi Bilal. Please export your flow and add it here by using the </> button you see in the post editor. Next, add the error you are getting. We are unable to help you without any of these information.

Also please add your Node-RED version, NodeJS version and the name of the custom node for mssql you are using.

4

My bet would be @val1 is empty OR its a string and you need ' single quotes around @val1.

If you are using node-red-contrib-mssql-plus then attach a debug node after the MSSQL node (with the debug node set to output complete msg object`).

Then in the debug window, have a look at what is in msg.query (msg.query will contain the final SQL ran against the SQL server)

5

Node red version : v0.20.7
Node.js version: v10.16.2

Here is my nodes:

[{"id":"7888aca5.823a44","type":"tab","label":"Flow 2","disabled":false,"info":""},{"id":"22aa6555.667b9a","type":"inject","z":"7888aca5.823a44","name":"","topic":"message","payload":"Bilal,Mustafa","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":180,"wires":[["b7873ce4.b2385"]]},{"id":"117859aa.6ce246","type":"debug","z":"7888aca5.823a44","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":550,"y":120,"wires":[]},{"id":"649a41e6.78c85","type":"MSSQL","z":"7888aca5.823a44","mssqlCN":"b63f32ce.fe4a3","name":"","query":"DECLARE @val1 NVARCHAR(MAX)\nDECLARE @val2 NVARCHAR(MAX)\nDECLARE @myVal NVARCHAR(MAX)\n\nSET @myVal='{{{payload}}}';\nSET @val1= myVal[0];\nSET @val2= myVal[1];\n\nINSERT INTO SPLT\n    (\n        VALUE1,\n        VALUE2\n    )\n    VALUES\n    (\n        @val1,\n        'value'\n    )\n","outField":"payload","x":350,"y":260,"wires":[["117859aa.6ce246"]]},{"id":"b7873ce4.b2385","type":"function","z":"7888aca5.823a44","name":"splitFun","func":"var myMsg= msg.payload;\nmsg.payload=myMsg.split(\",\");\nreturn msg;","outputs":1,"noerr":0,"x":300,"y":100,"wires":[["649a41e6.78c85","117859aa.6ce246"]]},{"id":"b63f32ce.fe4a3","type":"MSSQL-CN","z":"","name":"pme-57","server":"pme-57.cxzip7yq152r.ap-south-1.rds.amazonaws.com","encyption":false,"database":"test2"}]
6

bro i'm splitting Bilal,Mustafa using split function. here is my function's code:

var myMsg= msg.payload;
msg.payload=myMsg.split(",");
return msg;
7

in which case its a string and you need single quotes in your SQL values around the @val1.
EDIT ^ ignore that

Again...

If you are using node-red-contrib-mssql-plus then attach a debug node after the MSSQL node (with the debug node set to output complete msg object). Then in the debug window, have a look at what is in msg.query` (msg.query will contain the final SQL ran against the SQL server)

8

same error again and again

RequestError: Incorrect syntax near '1'.

9

are you using node-red-contrib-mssql-plus?

If so, do what I said and see what the final SQL is being sent to the SQL Server.

10

bro i'm using node-red-contrib-mssql.

should i use node-red-contrib-mssql-plus???

11

ah man...
you cant do that haha

try...

SET @val1= {{{msg.payload[0]}}};
SET @val2= {{{msg.payload[1]}}};
12

yes. (IMHO) it is much more comprehensive and fixes some bugs like querying wrong SQL Server when more than one config & it has much more features like throwing errors, additional outputs (to help in situations like this) & more.

13

getting a new error hahahah
RequestError: Incorrect syntax near ';'.

here is my query:

DECLARE @val1 NVARCHAR(MAX)
DECLARE @val2 NVARCHAR(MAX)
--DECLARE @myVal NVARCHAR(MAX)

--SET @myVal={{{payload}}};
SET @val1= {{{msg.payload[0]}}};
SET @val2= {{{msg.payload[1]}}};

INSERT INTO SPLT
    (
        VALUE1,
        VALUE2
    )
    VALUES
    (
        @val1,
        @val2
    )
14

that's great i'll use it...

15

are you certain msg.payload IS AN ARRAY?

put a debug node BEFORE the MSSQL to and inspect msg.payload

TBH, I never tried accessing [array elements] inside {{{mustache}}}.

If thats the issue then maybe add a change node BEFORE MSSQL to set msg.val1 to msg.payload[0] and set msg.val2 to msg.payload[1]

then use {{{msg.val1}}} in the SQL.

...

Install the mssql-plus node & debug the output - this will be far easier to fix.

16

yes i have array in msg.payload.

17

having same error....

anyone can help me or guide me a syntax for adding array into db.

18

post a screen shot and copy/paste the text from the debug output of MSSQL-PLUS (debug node output set to full msg object)

In particular, I want to see msg.query

Be sure to set the debug node to show full object
image

19

Edited the title to something more descriptive.

1 Like
20

Actually, as the mustache is converted to text, you will need SQL compatable quotes around that part too...

SET @val1= '{{{msg.payload[0]}}}';
SET @val2= '{{{msg.payload[1]}}}';