Referencing msg variable in environment type parameter

[elia-sysdesign]

Hello.

I have defined a subflow that logs a message to a table in a database by calling a stored procedure sp_write_execution_log.

I have configured the following environment variables:

Here's an export of the subflow.

[{"id":"fa1cba182af4c59b","type":"subflow","name":"writeLogMessage","info":"","category":"","in":[{"x":180,"y":180,"wires":[{"id":"f5777903346cf866"}]}],"out":[{"x":1040,"y":180,"wires":[{"id":"132992b4fc8fd735","port":0}]}],"env":[{"name":"subprocedureName","type":"str","value":""},{"name":"messageType","type":"num","value":""},{"name":"message","type":"str","value":""}],"meta":{},"color":"#C0DEED","icon":"node-red/db.svg"},{"id":"0edb2e0176656a33","type":"Stackhero-MySQL","z":"fa1cba182af4c59b","server":"","name":"insert_log","x":700,"y":180,"wires":[["132992b4fc8fd735"]]},{"id":"85c39fcb98799607","type":"function","z":"fa1cba182af4c59b","name":"set_query","func":"// create temporary object\nmsg.tmp = {};\n\n// move current topic to temporary location\nif (msg.topic)\n{\n    msg.tmp.topic = msg.topic;\n}\n\n// move current payload to temporary location\nif (msg.payload)\n{\n    msg.tmp.payload = msg.payload;\n}\n\nif (msg.log)\n{    \n    // define query\n    let sql = `CALL DB_22BS01.sp_write_execution_log(:procedureName, :procedureTimestamp, :subprocedureName, :messageType, :message);`;\n    // define query parameters\n    let sqlParameters = {\n        procedureName: msg.log.procedureName,\n        procedureTimestamp: msg.log.procedureTimestamp,\n        subprocedureName: msg.log.subprocedureName,\n        messageType: msg.log.messageType,\n        message: msg.log.message\n    };\n\n    msg.topic = sql;\n    msg.payload = sqlParameters;\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":520,"y":180,"wires":[["0edb2e0176656a33"]]},{"id":"132992b4fc8fd735","type":"function","z":"fa1cba182af4c59b","name":"reset_msg","func":"\nif (msg.tmp)\n{\n    // move tmp topic to current location\n    if (msg.tmp.topic) {\n        msg.topic = msg.tmp.topic;\n    }\n\n    // move tmp payload to current location\n    if (msg.tmp.payload) {\n        msg.payload = msg.tmp.payload;\n    }\n\n    delete msg.tmp;\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":890,"y":180,"wires":[[]]},{"id":"f5777903346cf866","type":"function","z":"fa1cba182af4c59b","name":"set_log_obj","func":"\nif (msg.log)\n{\n    delete msg.log;\n}\n\nmsg.log = {\n    \"procedureName\": env.get(\"NR_FLOW_NAME\"),\n    \"procedureTimestamp\": flow.get(\"$parent.procedureTimestamp\"),\n    \"subprocedureName\": env.get(\"subprocedureName\"),\n    \"messageType\": env.get(\"messageType\"),\n    \"message\": env.get(\"message\")\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":180,"wires":[["85c39fcb98799607"]]},{"id":"35ca44cc6168a7df","type":"Stackhero-MySQL-Server","name":"","host":"","port":"","tls":false,"database":"","credentials":{}}]

Here's an example of how I would use it:

[{"id":"fa1cba182af4c59b","type":"subflow","name":"writeLogMessage","info":"","category":"","in":[{"x":180,"y":180,"wires":[{"id":"f5777903346cf866"}]}],"out":[{"x":1040,"y":180,"wires":[{"id":"132992b4fc8fd735","port":0}]}],"env":[{"name":"subprocedureName","type":"str","value":""},{"name":"messageType","type":"num","value":""},{"name":"message","type":"str","value":""}],"meta":{},"color":"#C0DEED","icon":"node-red/db.svg"},{"id":"0edb2e0176656a33","type":"Stackhero-MySQL","z":"fa1cba182af4c59b","server":"35ca44cc6168a7df","name":"insert_log","x":700,"y":180,"wires":[["132992b4fc8fd735"]]},{"id":"85c39fcb98799607","type":"function","z":"fa1cba182af4c59b","name":"set_query","func":"// create temporary object\nmsg.tmp = {};\n\n// move current topic to temporary location\nif (msg.topic)\n{\n    msg.tmp.topic = msg.topic;\n}\n\n// move current payload to temporary location\nif (msg.payload)\n{\n    msg.tmp.payload = msg.payload;\n}\n\nif (msg.log)\n{    \n    // define query\n    let sql = `CALL DB_22BS01.sp_write_execution_log(:procedureName, :procedureTimestamp, :subprocedureName, :messageType, :message);`;\n    // define query parameters\n    let sqlParameters = {\n        procedureName: msg.log.procedureName,\n        procedureTimestamp: msg.log.procedureTimestamp,\n        subprocedureName: msg.log.subprocedureName,\n        messageType: msg.log.messageType,\n        message: msg.log.message\n    };\n\n    msg.topic = sql;\n    msg.payload = sqlParameters;\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":520,"y":180,"wires":[["0edb2e0176656a33"]]},{"id":"132992b4fc8fd735","type":"function","z":"fa1cba182af4c59b","name":"reset_msg","func":"\nif (msg.tmp)\n{\n    // move tmp topic to current location\n    if (msg.tmp.topic) {\n        msg.topic = msg.tmp.topic;\n    }\n\n    // move tmp payload to current location\n    if (msg.tmp.payload) {\n        msg.payload = msg.tmp.payload;\n    }\n\n    delete msg.tmp;\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":890,"y":180,"wires":[[]]},{"id":"f5777903346cf866","type":"function","z":"fa1cba182af4c59b","name":"set_log_obj","func":"\nif (msg.log)\n{\n    delete msg.log;\n}\n\nmsg.log = {\n    \"procedureName\": env.get(\"NR_FLOW_NAME\"),\n    \"procedureTimestamp\": flow.get(\"$parent.procedureTimestamp\"),\n    \"subprocedureName\": env.get(\"subprocedureName\"),\n    \"messageType\": env.get(\"messageType\"),\n    \"message\": env.get(\"message\")\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":330,"y":180,"wires":[["85c39fcb98799607"]]},{"id":"35ca44cc6168a7df","type":"Stackhero-MySQL-Server","name":"db_22bs01","host":"localhost","port":"3306","tls":false,"database":"DB_22BS01"},{"id":"31e2b49952892852","type":"subflow:fa1cba182af4c59b","z":"180ebda9eb3fda15","name":"","env":[{"name":"subprocedureName","value":"test_sub_procedure","type":"str"},{"name":"messageType","value":"-4","type":"num"},{"name":"message","value":"this is a test. a: ${msg.test.a}","type":"env"},{"name":"procedureTimestamp","value":"currentTimestamp","type":"env"},{"name":"procedureName","value":"test","type":"str"},{"name":"subProcedureName","value":"sub_test","type":"str"}],"x":1000,"y":760,"wires":[["8fa6af7bca5968dd"]]},{"id":"401a3b0d576d74ee","type":"inject","z":"180ebda9eb3fda15","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"goodbye","payload":"hello","payloadType":"str","x":590,"y":760,"wires":[["33d8a1fc16b4adbc"]]},{"id":"8fa6af7bca5968dd","type":"debug","z":"180ebda9eb3fda15","name":"debug 4","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1200,"y":760,"wires":[]},{"id":"33d8a1fc16b4adbc","type":"function","z":"180ebda9eb3fda15","name":"function 1","func":"\nflow.set(\"procedureTimestamp\", new Date());\n\nmsg.test = {\n    a: 5\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":800,"y":760,"wires":[["31e2b49952892852"]]}]

I would like to set the message parameter with a dynamic message and eventually inject the value of a msg or flow variable.
I tried executing it with a static string and it works fine but I can't figure out how to dynamically inject values.

What would be the best way to achieve this?

Thanks in advance

[E1cid]

If I understand correctly you can use the or ||, so if msg.message is undefined or null then the env is used.
e.g.

"message": msg.message || env.get("message")

[elia-sysdesign]

Hello @E1cid,

Thanks for the fast response.
Unfortunately, that's not my problem.

If you look at the writeLogMessage instance, that is in the second exported flow of my first message, I'm trying to inject the value of msg.test.a into the message parameter:

this is a test. a: ${msg.test.a}

But this is what gets logged: this is a test. a:.

The variable is set in a function block before the writeLogMessage instance.

How do I inject the value of msg.test.a into my message parameter?

Thanks again

[E1cid]

If you enter it as a string then you could use a template node and mustache to reference the msg var, as a work around

[{"id":"fa1cba182af4c59b","type":"subflow","name":"writeLogMessage","info":"","category":"","in":[{"x":40,"y":180,"wires":[{"id":"745898d86faefe20"}]}],"out":[{"x":620,"y":200,"wires":[{"id":"132992b4fc8fd735","port":0}]}],"env":[{"name":"subprocedureName","type":"str","value":""},{"name":"messageType","type":"num","value":""},{"name":"message","type":"str","value":""}],"meta":{},"color":"#C0DEED","icon":"node-red/db.svg"},{"id":"132992b4fc8fd735","type":"function","z":"fa1cba182af4c59b","name":"reset_msg","func":"\nif (msg.tmp)\n{\n    // move tmp topic to current location\n    if (msg.tmp.topic) {\n        msg.topic = msg.tmp.topic;\n    }\n\n    // move tmp payload to current location\n    if (msg.tmp.payload) {\n        msg.payload = msg.tmp.payload;\n    }\n\n    delete msg.tmp;\n}\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":490,"y":200,"wires":[[]]},{"id":"f5777903346cf866","type":"function","z":"fa1cba182af4c59b","name":"set_log_obj","func":"\nif (msg.log)\n{\n    delete msg.log;\n}\n\nmsg.log = {\n    \"procedureName\": env.get(\"NR_FLOW_NAME\"),\n    \"procedureTimestamp\": flow.get(\"$parent.procedureTimestamp\"),\n    \"subprocedureName\": env.get(\"subprocedureName\"),\n    \"messageType\": env.get(\"messageType\"),\n    \"message\": msg.message\n};\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":210,"y":180,"wires":[["132992b4fc8fd735"]]},{"id":"745898d86faefe20","type":"change","z":"fa1cba182af4c59b","name":"","rules":[{"t":"set","p":"template","pt":"msg","to":"message","tot":"env"}],"action":"","property":"","from":"","to":"","reg":false,"x":130,"y":80,"wires":[["66a1e2844b6ba7b7"]]},{"id":"66a1e2844b6ba7b7","type":"template","z":"fa1cba182af4c59b","name":"","field":"message","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"","output":"str","x":300,"y":80,"wires":[["f5777903346cf866","ec70fe70fac4fb7b"]]},{"id":"ec70fe70fac4fb7b","type":"debug","z":"fa1cba182af4c59b","name":"debug 300","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":530,"y":60,"wires":[]},{"id":"401a3b0d576d74ee","type":"inject","z":"b9860b4b9de8c8da","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"goodbye","payload":"hello","payloadType":"str","x":140,"y":3540,"wires":[["33d8a1fc16b4adbc"]]},{"id":"33d8a1fc16b4adbc","type":"function","z":"b9860b4b9de8c8da","name":"function 1","func":"\nflow.set(\"procedureTimestamp\", new Date());\n\nmsg.test = {\n    a: 5\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":350,"y":3540,"wires":[["31e2b49952892852"]]},{"id":"31e2b49952892852","type":"subflow:fa1cba182af4c59b","z":"b9860b4b9de8c8da","name":"","env":[{"name":"subprocedureName","value":"test_sub_procedure","type":"str"},{"name":"messageType","value":"-4","type":"num"},{"name":"message","value":"this is a test. a: {{test.a}}","type":"str"},{"name":"procedureTimestamp","value":"currentTimestamp","type":"env"},{"name":"procedureName","value":"test","type":"str"},{"name":"subProcedureName","value":"sub_test","type":"str"}],"x":550,"y":3540,"wires":[["8fa6af7bca5968dd"]]},{"id":"8fa6af7bca5968dd","type":"debug","z":"b9860b4b9de8c8da","name":"debug 4","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":750,"y":3540,"wires":[]}]

Removed mysql nodes so i could test.

[elia-sysdesign]

Thank you @E1cid!

Totally works!
Cheers!

[elia-sysdesign]

Hello @E1cid again.

I have one last question.
What if I want to print a whole object instead of a single variable.
For example, I want to log the whole object test instead of test.a ?

Thanks again.

[E1cid]

Then I would probably use a function instead of template. You would probably have to parse out the {{test}} section, then check type (string,number object etc) and deal with it accordingly, i.e convert test object to json string, then replace {{test}} with the string

[elia-sysdesign]

Ok @E1cid.

Thanks for the advice.