I am doing an Update on a DB through a DB SQL Executor node (must be that node), and one of the fields I need to update is a SQL statement that comes in as text. The problem is that the SQL statement references a flow variable (not from the workflow being executed) and I need the statement to be as-is with the variable name (not the value because the variable does not exist in the workflow in which it is executed), and I can’t find a way, so the SQL execution for the Update tries to find the value of a variable that does not exist. How do I make that variable go as text?
The SQL:
UPDATE xx.xxxx.xxx
SET sentencia_sql = ‘select * from BIProteccionDW.APOLO.DimPersonas WITH(NOLOCK) where skPersona between $${DRangoInicio}$$ and $${DRangoFinal}$$’,
WHERE pk_programacion_ingesta = ‘10’;
The node looks for $${DRangoInicio}$$ and $${DRangoFinal}$$', but I just need to read the name but not the value, so I’m getting the error shown below.
Before injecting the SQL query, can you use Expression node, to replace the variable names to its respective name representations? Then you can pass this new curated query to DB SQL Executor node.
Also, a side note, you can use, DB Query along with DB Update node, for an update transaction
Thank you very much for the answer. Can you help me with “its respective name representations”, please? I really need the names to go as they are going to be used in a query where the variables do exist, so I can’t modify the names.
Indeed the DB Updater works great, but the client has some node restrictions and for now we can only use DB Query Executor for this.
Before injecting the variables to the query, I would process the flow variables like this, such that I get just the name of it as the value against the variable. Here, replaceChars(string("$${DRangoFinal}$$"),"${}D" ,"" )
I used a String Manipulation (Variable) node for this. However, if you are on version 5.3, I would encourage you to use Expression node. Now once you add this to the string containing the SQL statement, it should work.
