Insert array contents into MSSQL table

jomacdon · 13 July 2020 14:14

Hello, may I please ask for some guidance... I have an array of data I want to insert into my MSSQL table called 'MyTable', here is the statement I have created:

INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) VALUES ('{{{payload.object.id}}}', '{{{payload.object.deviceId}}}', '{{{payload.object.label}}}', '{{{payload.object.message}}}', '{{{payload.object.serverity}}}', '{{{payload.object.activatedTime}}}', '{{{payload.object.clearedTime}}}')

Here is a screen shot of my Flow:

I think the following is going into my SQL Node:

13/07/2020, 14:12:52node: bb71a7c8.4d6348
msg.payload : array[2]
array[2]
0: object
id: "49cd2d85-0e12-4147-8bfa-444eb43d7a4e"
deviceId: "a03394b0-9a49-45c7-afb4-07541a8aba47"
label: "Handle Alarmed"
message: "Rack Door Handle alarmed for sensor Back Door at NBRK0750 with state "
severity: "CRITICAL"
activatedTime: "2020-06-19T10:38:24.199Z"
1: object
id: "9dadad16-38fe-4ee8-9f65-ada755a97e10"
deviceId: "acb8c96e-db30-4b72-bd06-ab8706b43efd"
label: "Local Authorization Alarm"
message: "Local Authorization alarm for Back Door at NBRK0750"
severity: "INFO"
activatedTime: "2020-04-29T14:28:57.594Z"

Thank you!

Steve-Mcl · 13 July 2020 14:41

can you attach a debug node (set to show complete message) to the output of the MSSQL node

I need to see the all the msg values (in particular the SQL generated by the mustache)

jomacdon · 13 July 2020 14:45

Hello again Steve-Mcl Here is the result of the debug node:

Here is my SQL table when it fails:

Thank you.

Colin · 13 July 2020 14:58

I wonder what that error means. Have you tried googling for it?

Steve-Mcl · 13 July 2020 15:01

I guess you are not using node-red-contrib-mssql-plus

when you use that node (apart from the many bug fixes & additional features) you get the compiled SQL statement in the debug output to help you see what is going wrong.

If you wish to upgrade...

stop node-red
open a command line prompt & CD to your node-red folder
npm uninstall node-red-contrib-mssql (or which ever derivative you are using)
npm install node-red-contrib-mssql-plus
start node-red

Steve-Mcl · 13 July 2020 15:04

Colin, i suspect the values are not being rendered into his SQL Statement (I suspect the duplicate violation is a red herring - and why I am asking him to install mssql-plus as it gives you the rendered SQL statement in the output msg object to see what is actually being executed against the SQL Server)

Colin · 13 July 2020 15:05

That makes sense.

jomacdon · 13 July 2020 15:06

Hi Steve-Mcl, I am actually using the 'node-red-contrib-mssql-plus' node:

Steve-Mcl · 13 July 2020 15:06

where did you attach the debug?

can you share your flow

```
like this
```

and a screen shot

jomacdon · 13 July 2020 15:08

Here is the Flow:

jomacdon · 13 July 2020 15:09

Steve-Mcl · 13 July 2020 15:10

Yeah - you need to show complete message option set on the debug node

Try again please.

jomacdon · 13 July 2020 15:15

Here is the debug output now:

13/07/2020, 16:13:17node: 734d2b68.1ad644
msg : Object
object
topic: ""
payload: object
headers: object
statusCode: 200
responseUrl: "https://api.ecostruxureit.com/rest/v1/organizations/069e2b9c-682d-4780-83c2-d87fe07e87dc/alarms"
redirectList: array[0]
parts: object
_msgid: "6a91ebff.7d9cb4"
query: "INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) VALUES ('', '', '', '', '', '', '')↵"

and a new screen shot:

Steve-Mcl · 13 July 2020 15:20

So as you can see - all of your values are empty

so the duplicate violation is occurring because you are always putting empty strings in the database.

Since your SQL is...



INSERT INTO [MyTable] (id, device_id, label, message, severity, activated_time, cleared_time) 
VALUES ('{{{payload.object.id}}}', '{{{payload.object.deviceId}}}', '{{{payload.object.label}}}', '{{{payload.object.message}}}', '{{{payload.object.serverity}}}', '{{{payload.object.activatedTime}}}', '{{{payload.object.clearedTime}}}')

... check the output of the template node Place each into X - does it have properties in payload.object.deviceId and payload.object.id etc?

jomacdon · 13 July 2020 15:40

Hi, thanks for your patience
Here is another screen shot showing the original output and then after the Split & Template:

I tested the output from the debug node looking for 'payload.object.id' as you suggested and I get nothing:

jomacdon · 13 July 2020 15:45

Inside Split No.1 I have two Objects in the Array, each has a key identifier with a corresponding value, it's these values I would like to Inject into my SQL table.

Steve-Mcl · 13 July 2020 15:46

your split isnt working.

Can you paste a copy (using the debug copy button) of the data from the API node

```
paste copy from debug like this
```

jomacdon · 13 July 2020 15:51

Hi Steve-Mcl, thanks! Here is the debug output from the API call:

13/07/2020, 16:49:24node: acce12ef.05cb8
msg.payload : Object
object
alarms: array[297]
[0 … 9]
[10 … 19]
[20 … 29]
[30 … 39]
[40 … 49]
[50 … 59]
[60 … 69]
[70 … 79]
[80 … 89]
[90 … 99]
[100 … 109]
[110 … 119]
[120 … 129]
[130 … 139]
[140 … 149]
[150 … 159]
[160 … 169]
[170 … 179]
[180 … 189]
[190 … 199]
[200 … 209]
[210 … 219]
[220 … 229]
[230 … 239]
[240 … 249]
[250 … 259]
[260 … 269]
[270 … 279]
[280 … 289]
[290 … 296]
offset: "14588213"

Steve-Mcl · 13 July 2020 15:54

I cant do anything with that data.

Can you do exactly what I asked & I will see if I can make this work for you by "faking the API call" (since i dont have access to the API, I need ACTUAL data - not a bunch of strings saying [0 … 9])

^ PRESS THE COPY BUTTON - PASTE INTO REPLY

jomacdon · 13 July 2020 15:59

Sorry - still getting to grips with this...

I got an error with the size of the reply (Body is limited to 32000 characters; you entered 94735.)

Here is the output after the filter node instead:

{"alarms":[{"id":"49cd2d85-0e12-4147-8bfa-444eb43d7a4e","deviceId":"a03394b0-9a49-45c7-afb4-07541a8aba47","label":"Handle Alarmed","message":"Rack Door Handle alarmed for sensor Back Door at NBRK0750 with state ","severity":"CRITICAL","activatedTime":"2020-06-19T10:38:24.199Z"},{"id":"9dadad16-38fe-4ee8-9f65-ada755a97e10","deviceId":"acb8c96e-db30-4b72-bd06-ab8706b43efd","label":"Local Authorization Alarm","message":"Local Authorization alarm for Back Door at NBRK0750","severity":"INFO","activatedTime":"2020-04-29T14:28:57.594Z"}],"offset":"14588213"}

Topic		Replies	Views
How to insert an array into mssql? General	23	4995	16 August 2019
How to access msg.payload array in mssql? General	10	2462	10 June 2020
Need help - Syntax MSSQL Insert Statement from a msg.payload General	3	532	9 December 2019
Insert new record into MSSQL General	5	339	18 March 2023
Select Array from MSSQL and insert into postgresql General	0	1077	5 December 2019

Insert array contents into MSSQL table

Related Topics