Node-red-contrib-mssql-plus => value in database is empty

General
1

Processing: Nodered.pdf...

Hello,

I am a new user of Nodered and am starting to create my first flux.

I am having difficulty with the "node-red-contrib-mssql-plus" node, I can connect to my table, a row is created but my values ​​don’t update. The result is empty.

When debugging after my mssql-plus node, I have this return as a value:

INSERT INTO [GPAOSQL]. [GPSQL]. [Ab_edi_OT_CCF] (ts_pos_wo_id, wo_instruction, type, category, reason)

VALUES ('', '', '', '', '')

I found a topic with a similar problem but I cannot find where the error is.

If anyone had an example flux with an update of the values ​​in a database, that would be really great.

I thank you in advance.

Alex

2

You are likely not populating the values correctly.

  • Put a Debug node before and after the SQL node (set it to show full message)
  • Show us what is in the query.
3

Hi Steve-Mcl, thank's for your response.

My debug before SQL is:

14/10/2021, 13:01:32node: 14b54ae5.065de5
msg.payload : array[1]
array[1]
0: object
ts_pos_wo_id: "67868-8998"
wo_instruction: "CREATE"
type: "POST"
category: "POST"
reason: 0

My debug after SQL is:

msg : Object
object
_msgid: "5c44203e.ccff2"
payload: undefined
topic: ""
statusCode: 200
responseUrl: "https://virtserver.swaggerhub.com/Clear-Channel/apist/v3/v3/OTsortant"
redirectList: array[0]
headers: object
query: string
INSERT INTO [dbo].[ab_edi_OT_CCF] (ts_pos_wo_id,wo_instruction,type,category,reason)
VALUES ('','', '','','')
queryMode: "query"
queryParams: array[0]
sqlInfo: array[0]

My request SQL in SQL node is:

INSERT INTO [dbo].[ab_edi_OT_CCF] (ts_pos_wo_id,wo_instruction,type,category,reason)
VALUES ('{{{payload.ts_pos_wo_id}}}','{{{payload.wo_instruction}}}', '{{{payload.type}}}','{{{payload.category}}}','{{{payload.reason}}}')

I think, my problematic is "payload: undefined", but i don't now how i can define payload?

Thank's a lot

Alex

4

Hi, so a couple of things first.

  1. Please post code between backticks
    ```
    like this
    ```

  2. When copying values from the debug sidebar, use the "Copy Value" button that appears under your mouse cursor when you hover over the message
    BX00Cy7yHi

On you your issue...

The debug BEFORE SQL shows payload is an array but in your SQL, you try to access payload.ts_pos_wo_id - an array is accessed by [] square brackets.

To simplify things (and avoid mistakes) use the "Copy Path" button that appears under your mouse cursor when you hover over the item of interest & use that.

Lastly,

I strongly recommend you use the parameters instead of {{{mustache}}} - this will protect your database against SQL injection.

image
image666×922 29.7 KB

5

Really, thank you Steve-Mcl,

It was the solution

Thank's

Alex

6

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