Generic SQL Node

[mikebywater]

How does the templating work in this node?

I tried using [[ msg.id ]] and [[ msg.payload.id ]] as in the very limited postgres storage docs but it doesn't seem to substitute. Is it not possible or am I missing something silly?

[ab73863]

Hi @mikebywater ,

Thanks for reporting the gap in the documentation. We will update with template guide details ASAP. In Generic SQL node if you are trying dynamic queries by substituting values then you can refer the below example.

Query SELECT * FROM EMPLOYEE WHERE NAME = 'Tom'

In the above query the value for the column NAME has to be passed dynamically using JSONata expression. In Kumologica following is the expression you need to use in Generic SQL node in order to construct the query.

"SELECT * FROM EMPLOYEE WHERE NAME = '" & msg.payload.name &"'"

In the above query template & operator is used for concatenating the first section which is "SELECT * FROM EMPLOYEE WHERE NAME = '" and the second section which is msg.payload.name. The second & is for concatenating single quote to the end of the value.

Sample flow for reference.

Input of the flow is

{"name": "Tom"}

flow.json that you can import to the designer

[{"id":"a2fe4afd.1c6558","type":"EventListener","z":"main.flow","name":"POST /employee","provider":"aws","eventSource":"api","dynamodbOperation":"","apiMethod":"post","apiUrl":"/employee","albMethod":"any","albUrl":"","bucketName":"","event":"s3:ObjectCreated:*","cognitoTrigger":"any","keventSource":"","kapiMethod":"any","kapiUrl":"","kcronexpression":"","zeventSource":"","zapiMethod":"any","zapiUrl":"","x":622.5,"y":960,"wires":[["997a1329.cf58"]],"caname":"event-handler","category":"general"},{"id":"af303614.e07e18","type":"EventListener-End","z":"main.flow","name":"EventListener-End","statusCode":"200","headers":{"Content-Type":"application/json"},"payload":"msg.payload.rows[0]","x":1102.5,"y":960,"wires":[],"caname":"eventlistenerend","category":"general"},{"id":"997a1329.cf58","type":"GenericSQL","z":"main.flow","name":"Generic SQL","provider":"postgres","host":"","port":"5432","dbName":"","timeout":"10000","username":"","password":"","query":"\"SELECT * FROM EMPLOYEE WHERE NAME = '\" & msg.payload.name &\"'\"","x":902.5,"y":960,"wires":[["af303614.e07e18"]],"caname":"genericsql","category":"storage"}]

Please try and let us know.

[mikebywater]

Thanks for the really quick response, working perfectly thanks.

I feel like the query is fairly difficult to maintain though when inserting a few different values and wonder if a basic templated way to express variables my be helpful.

Works really well though so thanks for taking the time to respond.