Hello everyone, I need some help with a sql query. I'm using the node-red-contrib-oracle-mod node with a complex sql query (about 75 lines). This query is working when I post it in the "SQL query" section inside the oracledb node. This is really static and I need to make it more dynamic. I have an ID that I should put inside the query, to get different results of the query.
I think the query should been moved out in a function node and then return a msg.query to the oracledb node, but I cannot figure out how to do this by JavaScript.
I've also tried to understand the field mapping without luck, so if anyone have tips/examples please let me know 
You could define a database view to offload the work to oracle and your query becomes a simple select with a where clause.
But in a function node something like this should work
msg.query = "select blah, blah "
msg query += "from blah, blah "
msg.query += " where blah blah"
msg.query += "and blahblah = " + msg.something
Etc
Note this is not a safe way to include msg.something in the query; I'm not sure how prepared queries work in oracle
Disclaimer: I use the MySQL note a lot and it's been a couple decades since I last used native Oracle myself. The node-RED module documentation sucks, as the github source doesn't include any general howto documentation or examples. However, this module is really just a wrapper around Oracle's node.js library with the query parameter containing the Oracle SQL query, and payload the bind values. This node.js module also has a lot of useful examples on Github that give you a lot of use cases.
In general you should avoid building queries at runtime as this is inefficient and can introduce injection vulnerabilities. Far better to use bind values; browse the Internet / YouTube to learn more.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
