Filter a SQL query with variables from a previous one in Node Red

[SigmaMan]

Hello all,

I try to query one SQL database in a Node Red flow to extract a few fields and declare them as variables, then re-use them to filter a second SQL query from another database.

I want to put the two msSQL nodes consecutively, without using flow variables.

example:
1st query:
"DECLARE @N_1Shift VARCHAR;
SELECT TOP (1)
@N_1Shift = [ShiftNo]
FROM db1"

2nd query:
"DECLARE @N_1Shift VARCHAR;
SET @N_1Shift = '{{msg.payload.N_1Shift}}'
SELECT TOP(1)
[ShiftStart]
FROM db2
WHERE [ShiftNo] = @N_1Shift"

Do you know what is not working here? I tried both '{{ }}' and '{{{ }}}'

[SigmaMan]

Anyone know how to do this?

[Steve-Mcl]

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

can you share your flow? (Select the 5 nodes and use CTRL+E to export, paste using the forum toolbar code </>` button)

can you capture data from the 1st MSSQL node and paste it into a reply so I can try to emulate it?

[SigmaMan]

I'm using " node-red-contrib-mssql "

[{"id":"1e7f608b.990d6f","type":"inject","z":"a095fc4.58a32","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":460,"y":1600,"wires":[["caf57e0a.f4034"]]},{"id":"caf57e0a.f4034","type":"MSSQL","z":"a095fc4.58a32","mssqlCN":"d5935af4.d78498","name":"db1 Shifts","query":"/* QUERY0: Definition of previous shift bounds, with UTC correction */\t\t\nDECLARE @N_1Shift INT\t\t\nDECLARE @ShiftStart INT\t\t\nDECLARE @ShiftEnd INT;\t\t\n\t\t\nSELECT TOP (1)\t\t\n\t\t\n\t@N_1Shift = z.[ShiftSeq]\t\n\t,@ShiftStart = z.[tShiftStart] + z.[TimezoneGap]\t\n\t,@ShiftEnd = z.[tShiftEnd] + z.[TimezoneGap]\t\n\t\t\nFROM\t\t\n(\t\t\nSELECT TOP(1000)\t\t\n\t\t\n\t[ShiftSeq]\t\n\t,[tShiftStart]\t\n\t,[tShiftEnd]\t\n\t,DENSE_RANK() OVER (ORDER BY [ShiftSeq] DESC ) AS [RANK_SHIFT]\t\n\t,ROUND( ( CONVERT( FLOAT, GETUTCDATE() ) - CONVERT( FLOAT, GETDATE() ) ) * 86400 , 0) AS [TimezoneGap]\t\n\t\t\nFROM db1\t\t\nWHERE DATEADD(S, [tShiftEnd], '1970-01-01') > GETDATE() - 2\t\t\n\t\t\n) z\t\t\n\t\t\nWHERE z.RANK_SHIFT = 2;\t\t\n\nSELECT\n\t@N_1Shift AS [N_1_Shift]\n\t,DATEADD(S, @ShiftStart, '1970-01-01') AT TIME ZONE 'CENTRAL EUROPEAN STANDARD TIME' AS [SStart]\n\t,DATEADD(S, @ShiftEnd, '1970-01-01') AT TIME ZONE 'CENTRAL EUROPEAN STANDARD TIME' AS [SEnd]","outField":"payload","x":610,"y":1600,"wires":[["ba769cde.3f59b"]]},{"id":"ba769cde.3f59b","type":"MSSQL","z":"a095fc4.58a32","mssqlCN":"3a3d660b.4b118a","name":"db2 Data","query":"msg.shifts = msg.payload\n\nDECLARE @N_1Shift VARCHAR\t\t\nDECLARE @ShiftStart DATETIME\t\t\nDECLARE @ShiftEnd DATETIME;\n\nSET @N_1Shift = '{{msg.shifts.N_1_Shift}}'\nSET @ShiftStart = '{{flow.shiftvar.SStart}}'\nSET @ShiftEnd = '{{flow.shiftvar.SEnd}}';\n\nSELECT\n    [dateHeure]\n\t,[num_balancelles]\n\t,[numTransac]\n\t,@N_1Shift AS [ShiftSeq]\nFROM db2\n\nWHERE [dateHeure] BETWEEN @ShiftStart AND @ShiftEnd\n\nORDER BY [dateHeure] DESC","outField":"payload","x":760,"y":1600,"wires":[["c2b0b364.a423"]]},{"id":"c2b0b364.a423","type":"debug","z":"a095fc4.58a32","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":910,"y":1600,"wires":[]},{"id":"d5935af4.d78498","type":"MSSQL-CN","z":"","name":"Guichen_MES","server":"SGUIWP0370.ad.ponet\\INSTPROD01","encyption":true,"database":"mattec_prohelp"},{"id":"3a3d660b.4b118a","type":"MSSQL-CN","z":"","name":"Guichen_SQP","server":"SGUIWP0370.ad.ponet\\INSTPROD01","encyption":true,"database":"sqpdb"}]

Data from the first query looks like this:

N_1_Shift	SStart	SEnd
202209282	2022-09-28 19:10:00.000 +02:00	2022-09-29 03:00:00.000 +02:00

[Steve-Mcl]

@SigmaMan

" node-red-contrib-mssql "

this node is very old, and has many known issues and more importantly doesnt support parameters which is ideal for what you are attempting (and also removes the chances of SQL injection hacks)

For example, using MSSQL-PLUS...

@SigmaMan

Data from the first query looks like this:

N_1_Shift	SStart	SEnd
202209282	2022-09-28 19:10:00.000 +02:00	2022-09-29 03:00:00.000 +02:00

I cannot use a screenshot to simulate so when I said...

@Steve-Mcl

can you capture data from the 1st MSSQL node and paste it into a reply so I can try to emulate it

I meant use the "copy value" button and paste as text (usable JSON)

[SigmaMan]

My bad, here are the values:

[{"N_1_Shift":202209290,"SStart":"2022-09-29T01:00:00.000Z","SEnd":"2022-09-29T09:00:00.000Z"}]

I will check with my IT to see if we can implement mssql-plus. Thank you for your answer

[SigmaMan]

Hello,
I have tries with MSSQL-PLUS, and find this:
Either the second nodes finds no declared value, or I declare it in SQL, and then receive the field, but null.

[{"id":"f796c5bdf579f511","type":"inject","z":"614035ec92d63219","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":1440,"wires":[["f3cff0b8319b8a00"]]},{"id":"f3cff0b8319b8a00","type":"MSSQL","z":"614035ec92d63219","mssqlCN":"","name":"db1 shifts","outField":"payload","returnType":"1","throwErrors":1,"query":"SELECT TOP (1)\t\t\r\n\r\n\t[ShiftSeq] AS [N_1_Shift]\r\n\r\nFROM\t\t\r\n(\t\t\r\nSELECT TOP(1000)\t\t\r\n\t\t\r\n\t[ShiftSeq]\t\r\n\t,[tShiftStart]\t\r\n\t,[tShiftEnd]\t\r\n\t,DENSE_RANK() OVER (ORDER BY [ShiftSeq] DESC ) AS [RANK_SHIFT]\t\r\n\t,ROUND( ( CONVERT( FLOAT, GETUTCDATE() ) - CONVERT( FLOAT, GETDATE() ) ) * 86400 , 0) AS [TimezoneGap]\t\r\n\t\t\r\nFROM db1\t\t\r\nWHERE DATEADD(S, [tShiftEnd], '1970-01-01') > GETDATE() - 2\t\t\r\n\t\t\r\n) z\t\t\r\n\t\t\r\nWHERE z.RANK_SHIFT = 2;","modeOpt":"queryMode","modeOptType":"query","queryOpt":"payload","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"rows","rowsType":"msg","params":[],"x":340,"y":1440,"wires":[["f96ff984f1288358"]]},{"id":"f96ff984f1288358","type":"MSSQL","z":"614035ec92d63219","mssqlCN":"","name":"db2 Data","outField":"payload2","returnType":"1","throwErrors":"0","query":"--DECLARE @N_1Shift INT;\n\nSELECT\n\t@N_1Shift * 2 AS [ShiftSeq]","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","rows":"","rowsType":"msg","params":[{"output":false,"name":"@N_1Shift","type":"int","valueType":"msg","value":"payload.N_1_Shift","options":{"nullable":true,"primary":false,"identity":false,"readOnly":false}}],"x":480,"y":1440,"wires":[["44dd311515c73c82"]]},{"id":"44dd311515c73c82","type":"debug","z":"614035ec92d63219","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload2","targetType":"msg","statusVal":"","statusType":"auto","x":640,"y":1440,"wires":[]}]

[Steve-Mcl]

Does msg.payload.N_1_Shift even contain a value (my money is on NO!)

TIP: use debug node to check what comes out of db1 shifts

TIP2: Since your 2nd query is SELECT @N_1Shift * 2 AS [ShiftSeq] there is no point in even asking the database to do this. Just set your first query to select [ShiftSeq] * 2 as [ShiftSeq]

---

Other tip

There’s a great page in the docs (Working with messages : Node-RED) that will explain how to use the debug panel to find the right path to any data item.

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

[SigmaMan]

I do get data from the "db1 shifts" node, when looking at the whole payload, but not with payload.N_1_Shift:

As for the second tip, it doesn't seem to change anything, and I want to keep the "SELECT" syntax for when I reintroduce my 2nd SQL query properly.

[Steve-Mcl]

@SigmaMan

I do get data from the "db1 shifts" node, when looking at the whole payload, but not with payload.N_1_Shift:

Yep, I know. And as I said...

@Steve-Mcl

Pay particular attention to the part about the buttons that appear under your mouse pointer when you over hover a debug message property in the sidebar.

In other words, it is NOT msg.payload.N_1_Shift

Then use that in the next MSSQL node

[SigmaMan]

My bad, I didn't read your reply properly.
The payload was "payload.recordset[0].N_1_Shift" and now everything works as intended .

Thank you very much for your support!

[SigmaMan]

For anyone interested, another mistake I made was using "@" in the variable name in the MSSQL-PLUS nodes Parameters Editor, which was unneccessary and caused the variable to be unrecognized.