Hi Ken,
The answer to your question is long, so I’ll try to summarize it as follows: the reason I’m trying this approach is driven by the need I have and the fact that what I was trying before did not work.
The Need
Imagine that:
a) there are N types of entities: Ea, Eb, Ec, …, En.
b) I will only know at runtime how many and which entities I’m dealing with
c) I will only know at runtime how these entities are related among them
Once I know points a, b and c, I need to produce a list that shows how these entities are connected. For example (and sorry for my miserable attempt at indenting rows for readability):
Ea1 – Eb2
Eb2 -- Ec1
Eb2 -- Ec3
Eb2 -- Ec5
Ea1 – Eb8
Eb8 -- Ec1
Eb8 -- Ec2
Eb8 -- Ec7
Ea2 – Eb1
Eb1 -- Ec3
Eb1 -- Ec4
…
This is “walking through a relationship tree”, which in general, is solved with a set of nested loops. Points a, b, and c make it necessary to create in real time the SQL statement that needs to be executed.
The original failed approach
I wanted to create the sql statement using Wappler’s text handling abilities. In the end, I’d be “Set(ting the) Value” of a variable called mySQL_statement and then passing that statement by putting it as below in the body of a Database Custom Query step:
{{mySQL_statement}}
I’m doing this successfully for other complex queries.
The problem is that to generate mySQL_statement, because of points a, b, and c above, I need to invoke an API library recursively --i.e., the library calls itself.
Well…since I could not make that recursive API library work properly, I tried generating the LOOPs using SQL itself and cursors. The problem is that cursors need to be inside a stored procedure, and since its content has to be generated dynamically, my new SQL statement has to create the sp, call it, and then drop it. In the code to create the sp I found that the DELIMITER statement, that you need to define a stored procedure, does not work from inside Wappler.
Sorry for the long answer, but since you asked… 
I guess that I’ll have to go back to trying to get the recursive API call work properly. This is something I already spent hours on and I’m not relishing the reattemtp 
Any fresh idea on how to approach this problem from a different angle will be really appreciated!
Alex
