Connection lost when select an option from dropdown node

QGIO · 3 May 2022 10:39

Hi all,

I have the following problem: when I select an option from my dropdown node list, I have the message Connection lost.

This is part of the my flow:

[{"id":"f8692ac4f4dadd6c","type":"ui_dropdown","z":"2380dbebb998c1d1","name":"","label":"Aggiornamento STATO","tooltip":"","place":"Select option","group":"ef2301c8f15e8f4a","order":1,"width":8,"height":2,"passthru":true,"multiple":false,"options":[{"label":"","value":"","type":"str"}],"payload":"","topic":"topic","topicType":"msg","className":"","x":1880,"y":680,"wires":[["11f3b9bc3ac5d3f6","dc9b2e03f5cf6950"]]},{"id":"1660a9ca483e580d","type":"MSSQL","z":"2380dbebb998c1d1","mssqlCN":"6fb70e68d89e36c4","name":"","outField":"payload","returnType":"0","throwErrors":"0","query":"SELECT [dbo].[SCADENZIARIO].ID as ID_Scad, [dbo].[StrumentazioneAttrezzature].CODIFICA\nFROM [dbo].[SCADENZIARIO]\nINNER JOIN  [dbo].[StrumentazioneAttrezzature]\nON [dbo].[SCADENZIARIO].ID_STRUM = [dbo].[StrumentazioneAttrezzature].ID\nWHERE [dbo].[StrumentazioneAttrezzature].CODIFICA = (@CODIFICA) and STATO IS NULL","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"CODIFICA","type":"VarChar","valueType":"msg","value":"payload","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":1220,"y":700,"wires":[["e73936731827d3e6","b8d64552c13ac229"]]},{"id":"427941bfd5b6c2f6","type":"change","z":"2380dbebb998c1d1","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"payload.ID_Scad[]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":1560,"y":760,"wires":[["f8692ac4f4dadd6c","1354333af10e2ca2"]]},{"id":"11f3b9bc3ac5d3f6","type":"change","z":"2380dbebb998c1d1","name":"","rules":[{"t":"set","p":"ID_Scad","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1930,"y":800,"wires":[["c8cebad2ef48d467","ba69791ba16e0839"]]},{"id":"b8d64552c13ac229","type":"switch","z":"2380dbebb998c1d1","name":"","property":"payload","propertyType":"msg","rules":[{"t":"empty"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1390,"y":700,"wires":[["203a80f32bfe8454"],["427941bfd5b6c2f6"]]},{"id":"203a80f32bfe8454","type":"change","z":"2380dbebb998c1d1","name":"","rules":[{"t":"set","p":"options","pt":"msg","to":"[]","tot":"json"}],"action":"","property":"","from":"","to":"","reg":false,"x":1560,"y":700,"wires":[["f8692ac4f4dadd6c"]]},{"id":"ef2301c8f15e8f4a","type":"ui_group","name":"Inserimento Stato Tarat./Manut.","tab":"21286fd2ee7bdc0c","order":5,"disp":true,"width":"10","collapse":false,"className":""},{"id":"6fb70e68d89e36c4","type":"MSSQL-CN","tdsVersion":"7_4","name":"DB_Strumentazione","server":"192.168.95.30\\SQLEXPRESS","port":"","encyption":true,"trustServerCertificate":true,"database":"DB_Strumentazione","useUTC":true,"connectTimeout":"","requestTimeout":"","cancelTimeout":"","pool":"","parseJSON":false,"enableArithAbort":true},{"id":"21286fd2ee7bdc0c","type":"ui_tab","name":"Inserimento Date Controllo","icon":"assignment","order":5,"disabled":false,"hidden":true}]

Could you suggest me how to fix this problem?

Thanks

Steve-Mcl · 3 May 2022 10:44

At a guess, whatever happens in your flow AFTER the dropdown changes is crashing node-red (perhaps a loop or bad node)? Difficult to tell without more info.

What do you see in node-red console? Are you getting an unhandled exception?

QGIO · 3 May 2022 10:51

I don't see any error message.

If I run the flow to local host (to a local PC database and not server connection) I don't see the problem.

Steve-Mcl · 3 May 2022 10:58

Can your node-red instance ping the remote database?

What is the remote database? Is it a SQL SERVER Instance?

Can do a simple flow to SELECT getdate() from from the remote database?

Which MSSQL contrib node are you using (provide a link to it or the full node-red-contrib-xxxx-xxx name of the node)?

QGIO · 3 May 2022 11:16

I have several connections to SQL server and they work.
It is the only point in the flow where this happens (I was thinking to a lood or bad node as you suggest before).

What I don't understand if it is a flow error it is: why in local I don't have this problem?!

Steve-Mcl · 3 May 2022 11:34

do you mean you have several connections OR several MSSQL nodes that perform SQL against a single connection?

And please answer my questions...

The information is necessary to offer you good advice.

QGIO · 3 May 2022 11:47

I mean several MSSQL nodes in my flow, but I get the message Connection lost only when I act on the dropdown node I have reported above.

It is SQL server istance

I have not well understood the question: Can your node-red instance ping the remote database?

I use MSSQL node-red-contrib-mssql-plus 0.7.3.

Steve-Mcl · 3 May 2022 11:56

Example: node-red runs on LAPTOP
Remote SQL runs on REMOTE_SQL_COMPUTER

Can LAPTOP ping REMOTE_SQL_COMPUTER

e.g...

What port number is this remote SQL Server instance using AND what PORT number have you input on the MSSQL-Config node?

QGIO · 3 May 2022 12:08

Ok thanks.

I made the ping to remote SQL IP and it works. The average time of connection is 2 ms.

The port is 2000.

But the point is that, all the MSSQL nodes I have used in my flow have the same properties of connection (port, istance etc), so if the other connections are ok, I'm asking why only this give me a problem?

Steve-Mcl · 3 May 2022 12:20

ok, I just wanted to establish the playing field since you said..

Where do you see "Connection lost" on the MSSQL node or on the dashboard?

Can you add 2 debug nodes (set to show complete msg)
1 before the MSSQL node
1 after the MSSQL node
Fully expand both debug messages and show me what you see.

QGIO · 3 May 2022 12:31

This is the message of Connection lost:
immagine

It appears when Aggiornamento STATO is selected.

The debug node output:

(it is easier to report this instead of the flow screenshot, is it ok?)

Steve-Mcl · 3 May 2022 12:35

Do you see any errors in the browsers console?

QGIO · 3 May 2022 12:40

I see only the first image where Connection lost is reported in red. And after a while, there is like a reset of the page (all the filled fields become empty)

In my flow I see that the debug after the dropdown selection is not executed

Steve-Mcl · 3 May 2022 12:42

And if you look in the browsers console log what do you see?

Thats why I asked you to

But you did not. (you posted only payload, not the FULL msg)

QGIO · 3 May 2022 12:54

Steve-Mcl · 3 May 2022 13:01

So first you "I see that the debug after the dropdown selection is not executed" - so how does the SQL node get triggered?

Also, please can you please read my questions properly & answer them otherwise I cant give accurate advice.

That debug picture does NOT show all parts of the msg expanded.
Do you see any errors in the browsers console?

QGIO · 3 May 2022 13:16

The query is performed, I see the query result in the dropdown node list. As I told, when I select one option from this list, the error Connetion lost is shown (in the /ui).

Debug node result After SQL query:
3/5/2022, 14:53:48node: After Request SQL

msg : Object

object

payload: array[1]

socketid: "xS62RHKdfqU2olmuAAAB"

_msgid: "d6b701d230bc5d7c"

query: string

SELECT [dbo].[SCADENZIARIO].ID as ID_Scad, [dbo].[StrumentazioneAttrezzature].CODIFICA FROM [dbo].[SCADENZIARIO] INNER JOIN [dbo].[StrumentazioneAttrezzature] ON [dbo].[SCADENZIARIO].ID_STRUM = [dbo].[StrumentazioneAttrezzature].ID WHERE [dbo].[StrumentazioneAttrezzature].CODIFICA = (@CODIFICA) and STATO IS NULL

queryMode: "query"

queryParams: array[1]

0: object

output: false

name: "CODIFICA"

type: object

type: function

length: undefined

value: "prova"

options: object

nullable: true

primary: false

identity: false

readOnly: false

sqlInfo: array[0]

No

Steve-Mcl · 3 May 2022 13:29

@QGIO I cannot see what is in the payload - can you? I cant help if you dont provide information

Either expand all properties and send a screen shot OR better still, use the Copy Value button

And ALWAYS post code/flow/data in a code block
```
like this
```

Lasty...

Do you see any errors in the browsers console? PLEASE ANSWER THIS

QGIO · 3 May 2022 13:46

Here the Value (I used copy value now)

{"payload":[{"ID_Scad":81,"CODIFICA":"prova"}],"socketid":"xS62RHKdfqU2olmuAAAB","_msgid":"d6b701d230bc5d7c","query":"SELECT [dbo].[SCADENZIARIO].ID as ID_Scad, [dbo].[StrumentazioneAttrezzature].CODIFICA\nFROM [dbo].[SCADENZIARIO]\nINNER JOIN  [dbo].[StrumentazioneAttrezzature]\nON [dbo].[SCADENZIARIO].ID_STRUM = [dbo].[StrumentazioneAttrezzature].ID\nWHERE [dbo].[StrumentazioneAttrezzature].CODIFICA = (@CODIFICA) and STATO IS NULL","queryMode":"query","queryParams":[{"output":false,"name":"CODIFICA","type":{},"value":"prova","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"sqlInfo":[]}

I have aswered in the previus post (point 2), in any case the answer is No.

Steve-Mcl · 3 May 2022 14:04

ok, so using the value you posted, I could simulate your flow.

I changed the SQL node to a function containing your data - I can confirm there is nothing wrong with the flow you posted earlier.

chrome_xMuht6e5al

So we have come full cirle back to my original comment...

Coming back to where you said "In my flow I see that the debug after the dropdown selection is not executed" I suspect it IS happening but something else happens.

Can you share more of the flow?

Topic		Replies	Views
Dropdown node in node red General	34	5458	9 April 2021
Unable to save and retrieve value from DropDownList Dashboard node-red-dashboard	3	344	25 September 2021
Node Red Crashes on Dropdown Menu Selection? Dashboard	12	693	23 January 2022
Unable to select whole message (msg.) on debug node General	3	250	16 April 2021
"Connection lost" on a specific dashboard tab Dashboard	3	149	5 January 2023

Connection lost when select an option from dropdown node

Lasty...

Related Topics