mssql-plus issue with mustache format for server name and user/pwd

[Rramesh78]

Hi, I'm using mssql-plus 0.6.4 version. i could not connect to sql using env variable. This is helpful for me to connect to different db located in the different location. I tried to create a sub flow for sql that would help me reuse the same flow across different locations with different servers. It throws me an errr message code: "ESOCKET" message: "Failed to connect to :1433 - Could not connect (sequence)

you help would be really appreciable I've attached the flow with subflow

[{"id":"ebc85c42.463a8","type":"subflow","name":"SQLPlus064","info":"","category":"","in":[{"x":100,"y":80,"wires":[{"id":"bf1289cd.07fcb8"}]}],"out":[{"x":300,"y":80,"wires":[{"id":"bf1289cd.07fcb8","port":0}]}],"env":[{"name":"ServerName","type":"str","value":"10.1.2.111"},{"name":"UserName","type":"str","value":"appliUser"},{"name":"Password","type":"str","value":"1234556"},{"name":"DBName","type":"str","value":"DB_DEV"}],"color":"#DDAA99"},{"id":"bf1289cd.07fcb8","type":"MSSQL","z":"ebc85c42.463a8","mssqlCN":"c29068d1.97ef38","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"Select TOP 10 * FROM TestBenchResult","modeOpt":"","modeOptType":"query","queryOpt":"","queryOptType":"editor","paramsOpt":"","paramsOptType":"none","params":[],"x":200,"y":80,"wires":[[]]},{"id":"c29068d1.97ef38","type":"MSSQL-CN","z":"ebc85c42.463a8","tdsVersion":"7_4","name":"DB-Con","server":"\"ServerName\"","port":"","encyption":true,"trustServerCertificate":true,"database":"ShopfloorDB_DEV","useUTC":false,"connectTimeout":"50000","requestTimeout":"50000","cancelTimeout":"50000","pool":"5","parseJSON":false,"enableArithAbort":true},{"id":"c81636eb.e7d248","type":"tab","label":"MSSQLPlus-CheckSub","disabled":false,"info":""},{"id":"7c1a36cd.c54a88","type":"inject","z":"c81636eb.e7d248","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":100,"wires":[["30366732.3efba8"]]},{"id":"de6a1325.7c759","type":"debug","z":"c81636eb.e7d248","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":440,"y":100,"wires":[]},{"id":"30366732.3efba8","type":"subflow:ebc85c42.463a8","z":"c81636eb.e7d248","name":"","env":[{"name":"ServerName","value":"10.1.2.111","type":"env"},{"name":"Password","value":"123456789","type":"str"}],"x":300,"y":80,"wires":[["de6a1325.7c759"]]}]

Thanks Ramesh R

[candoumbe]

Any update on this issue ?

[Steve-Mcl]

@Rramesh78 my apologies for not seeing this earlier.

The issue is your understanding of how ENV VARs in subflows work. (env var docs)

Here is a demo with correctly configure parameters and even proper markdown help...

Demo flow and updated subflow (use CTRL-I to import)...

[{"id":"ebc85c42.463a8","type":"subflow","name":"SQLPlus064","info":"Connects to a MQTT broker and publishes messages.\r\n\r\n### Inputs\r\n\r\n: query (string) :  the SQL Query to run.\r\n: *queryParams* (string) :  Parameters (optional).\r\n\r\n\r\n### Outputs\r\n\r\n: payload (array) :  the data.\r\n\r\n### Details\r\n\r\n#### queryParams \r\nWhen including Query parameters, `msg.queryParams` must be an aray of 1 or more parameters\r\n\r\nexample...\r\n```\r\n[\r\n    {\r\n        \"output\": false,\r\n        \"name\": \"parameter name\",\r\n        \"type\": \"parameter type\",\r\n        \"value\": \"parameter value\"\r\n    },\r\n    {\r\n        \"output\": true,\r\n        \"name\": \"parameter name\",\r\n        \"type\": \"parameter type\"\r\n    }\r\n]\r\n```\r\n\r\n#### Query Example (with parameters)...\r\n\r\n##### Query\r\nSELECT TOP {{{payload.maxRows}}} * FROM [MyTable] WHERE Name = @name AND quantity >= @minQty\r\n##### Parameters\r\nIn/Out input , Name name , Type varchar(20) , Value stephen\r\nIn/Out input , Name minQty , Type int , Value 5","category":"storage","in":[{"x":100,"y":80,"wires":[{"id":"bf1289cd.07fcb8"}]}],"out":[{"x":380,"y":80,"wires":[{"id":"bf1289cd.07fcb8","port":0}]}],"env":[{"name":"SERVER","type":"str","value":"10.1.2.111","ui":{"icon":"font-awesome/fa-server","label":{"en-US":"SQL Host/IP"},"type":"input","opts":{"types":["str","env"]}}},{"name":"USER","type":"str","value":"appliUser","ui":{"icon":"font-awesome/fa-user-o","label":{"en-US":"User Name"},"type":"input","opts":{"types":["str","env","cred"]}}},{"name":"PASS","type":"cred","ui":{"icon":"font-awesome/fa-user-secret","label":{"en-US":"Password"}}},{"name":"DB","type":"str","value":"DB_DEV","ui":{"icon":"font-awesome/fa-database","label":{"en-US":"DB Name"},"type":"input","opts":{"types":["str","env"]}}},{"name":"PORT","type":"num","value":"1433","ui":{"icon":"font-awesome/fa-support","type":"input","opts":{"types":["num","env"]}}}],"meta":{"type":"SQLPlusSubFlow1","version":"1.0.0"},"credentials":{"PASS":""},"color":"#DDAA99","status":{"x":500,"y":180,"wires":[{"id":"076f4ef481a2722f","port":0}]}},{"id":"bf1289cd.07fcb8","type":"MSSQL","z":"ebc85c42.463a8","mssqlCN":"c29068d1.97ef38","name":"","outField":"payload","returnType":0,"throwErrors":1,"query":"","modeOpt":"","modeOptType":"query","queryOpt":"query","queryOptType":"msg","paramsOpt":"queryParams","paramsOptType":"msg","rows":"","rowsType":"msg","params":[],"x":240,"y":80,"wires":[[]]},{"id":"076f4ef481a2722f","type":"status","z":"ebc85c42.463a8","name":"","scope":["bf1289cd.07fcb8"],"x":350,"y":180,"wires":[[]]},{"id":"c29068d1.97ef38","type":"MSSQL-CN","z":"ebc85c42.463a8","tdsVersion":"7_4","name":"DB-Con","server":"${SERVER}","port":"${PORT}","encyption":true,"trustServerCertificate":true,"database":"${DB}","useUTC":false,"connectTimeout":"50000","requestTimeout":"50000","cancelTimeout":"50000","pool":"5","parseJSON":false,"enableArithAbort":true},{"id":"7c1a36cd.c54a88","type":"inject","z":"c81636eb.e7d248","name":"select @@servername as [server name], getdate() as [server time]","props":[{"p":"query","v":"select @@servername as [server name], getdate() as [server time]","vt":"str"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":680,"y":100,"wires":[["30366732.3efba8"]]},{"id":"de6a1325.7c759","type":"debug","z":"c81636eb.e7d248","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":850,"y":160,"wires":[]},{"id":"30366732.3efba8","type":"subflow:ebc85c42.463a8","z":"c81636eb.e7d248","name":"","env":[{"name":"SERVER","value":"192.168.1.38","type":"str"},{"name":"USER","value":"sa","type":"str"},{"name":"PASS","type":"cred"},{"name":"DB","value":"testdb","type":"str"},{"name":"ServerName","value":"10.1.2.111","type":"env"},{"name":"Password","value":"123456789","type":"str"}],"credentials":{"PASS":"__PWRD__"},"x":630,"y":160,"wires":[["de6a1325.7c759"]]}]

[Steve-Mcl]

@candoumbe What is your issue?

[candoumbe]

Hi @Steve-Mcl I was looking for the correct syntax to use in order to use env. vars for username and password. I let you know if applying your answer solved the issue.

[Steve-Mcl]

I will close this as I think it is resolved. Please reopen if you still have a problem.