MSSQL-PLUS, Same query, different response

[JonasH]

Hi all,

I´m using MSSQL-PLUS to read out a value from MS SQL DB.
I created the Query manually, than I get the correct response from the DB.
I create the Query automatically, than I get an "empty" response from DB.
What I´m doing wrong?
Thank you in advance!

[Colin]

Please connect the debug node to the function and the inject and show what they give.

Also experiment with the query and find which bit is failing. If you remove the where clause (in the function) do you get records? If you mis-spell the database name in the function what happens?

[JonasH]

The msg.payload is the same in both ways.

What is the difference between string [22] and [9] ?

Removing the WHERE Clause, I get the data of the DB with both versions.

[Steve-Mcl]

You are including the single quotes in the where clause but the database does not have single quotes in the data.

Export your flow and I will show you how to simplify this using parameters.

[JonasH]

The flow...

[{"id":"6bab9a99.9c35b4","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"6daabca1.3208b4","type":"inject","z":"6bab9a99.9c35b4","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":140,"wires":[["9b57933b.1214a"]]},{"id":"9b57933b.1214a","type":"modbus-getter","z":"6bab9a99.9c35b4","name":"Lese Auftragsnummer","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"34950","quantity":"20","server":"b80bda77.541c78","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":240,"y":220,"wires":[["3fdf7993.047b26"],[]]},{"id":"3fdf7993.047b26","type":"function","z":"6bab9a99.9c35b4","name":"WORD to STRING","func":"msg.payload = Buffer.from(msg.payload);\nmsg.payload = msg.payload.toString();\nmsg.payload = \"'\"+msg.payload+\"'\";\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":320,"wires":[["c7c7b3c9.47d44","803cb449.e7dc98"]]},{"id":"803cb449.e7dc98","type":"MSSQL","z":"6bab9a99.9c35b4","mssqlCN":"b95d3c1d.51301","name":"Get Pruefauftrag","outField":"payload","returnType":"1","throwErrors":"0","query":"SELECT * FROM Pruefauftrag;","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","params":[{"output":false,"name":"aNr","type":"VarChar(20)","valueType":"global","value":"Auftrag"}],"x":650,"y":420,"wires":[["f2b6fa59.ba56e8"]]},{"id":"5e5ca7c4.200ee8","type":"inject","z":"6bab9a99.9c35b4","name":"","topic":"","payload":"'PA-1212'","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":560,"wires":[["c7c7b3c9.47d44","803cb449.e7dc98"]]},{"id":"f2b6fa59.ba56e8","type":"debug","z":"6bab9a99.9c35b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":790,"y":500,"wires":[]},{"id":"c7c7b3c9.47d44","type":"debug","z":"6bab9a99.9c35b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":430,"y":420,"wires":[]},{"id":"b80bda77.541c78","type":"modbus-client","z":"","name":"mTRON T CPU","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.178.46","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"b95d3c1d.51301","type":"MSSQL-CN","z":"","tdsVersion":"7_4","name":"MS Azure SQL-DB","server":"test-server-eng.database.windows.net","port":"1433","encyption":true,"trustServerCertificate":true,"database":"TestDatenbank","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

Admin edit - surround flow with backticks to make it importable*

[Steve-Mcl]

Dont add more quotes

Use parameters

[{"id":"6daabca1.3208b4","type":"inject","z":"6bab9a99.9c35b4","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":188,"y":128,"wires":[["9b57933b.1214a"]]},{"id":"9b57933b.1214a","type":"modbus-getter","z":"6bab9a99.9c35b4","name":"Lese Auftragsnummer","showStatusActivities":false,"showErrors":false,"logIOActivities":false,"unitid":"1","dataType":"HoldingRegister","adr":"34950","quantity":"20","server":"b80bda77.541c78","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":false,"keepMsgProperties":false,"x":388,"y":128,"wires":[["3fdf7993.047b26"],[]]},{"id":"3fdf7993.047b26","type":"function","z":"6bab9a99.9c35b4","name":"WORD to STRING","func":"msg.payload = Buffer.from(msg.payload);\nmsg.payload = msg.payload.toString();\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":218,"y":208,"wires":[["803cb449.e7dc98","3dcb9e6a.6848c2"]]},{"id":"803cb449.e7dc98","type":"MSSQL","z":"6bab9a99.9c35b4","mssqlCN":"b95d3c1d.51301","name":"Get Pruefauftrag","outField":"payload","returnType":"1","throwErrors":"0","query":"SELECT * FROM Pruefauftrag \nwhere Pruefauftrag = @Pruefauftrag;","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"editor","params":[{"output":false,"name":"Pruefauftrag","type":"VarChar(20)","valueType":"msg","value":"payload"}],"x":480,"y":208,"wires":[["f2b6fa59.ba56e8"]]},{"id":"5e5ca7c4.200ee8","type":"inject","z":"6bab9a99.9c35b4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"PA-1212","payloadType":"str","x":252,"y":320,"wires":[["803cb449.e7dc98"]]},{"id":"f2b6fa59.ba56e8","type":"debug","z":"6bab9a99.9c35b4","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":642,"y":208,"wires":[]},{"id":"3dcb9e6a.6848c2","type":"debug","z":"6bab9a99.9c35b4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":230,"y":256,"wires":[]},{"id":"b80bda77.541c78","type":"modbus-client","name":"mTRON T CPU","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.178.46","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"1","commandDelay":"1","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"b95d3c1d.51301","type":"MSSQL-CN","tdsVersion":"7_4","name":"MS Azure SQL-DB","server":"test-server-eng.database.windows.net","port":"1433","encyption":true,"trustServerCertificate":true,"database":"TestDatenbank","useUTC":true,"connectTimeout":"15000","requestTimeout":"15000","cancelTimeout":"5000","pool":"5","parseJSON":false,"enableArithAbort":true}]

PS, to simplify modbus data conversion to usable types, you might find node-red-contrib-buffer-parser useful (it was made for this type of operation)

[JonasH]

Thank you for your explanations!
But I still have the same problem.

[Steve-Mcl]

Am i right in thinking the bottom (inject) works?

As for the modbus version - your string conversion might have un-printable characters.

thats why i suggested buffer-tools.

Try adding .trim() call to your function node.

PS, expand the "queryParams" on the failed query & show me what is in there?

[Colin]

@JonasH

What is the difference between string [22] and [9] ?

I don't know if it is still relevant, but the difference is that the long one must have some unprintable characters in it.

[JonasH]

I changed the parsing and now I have a string without un-printable characters.
Now it works!
Thank you so much!