Hello,
Could you please help with ideas how to implement the following:
I run SQL query by SQL Executor that checks something in PostgreSQL DB and as a result provides set of SQL queries that should be executed in the database afterward. How to execute those newly generated queries?
How can I run all those resulted queries after SQL Executor?
You should be able to expose the SQL queries after the initial SQLExecutor as an attribute. Then in a 2nd SQLExecutor, use that attribute as input for the SQL statement parameter.
As
Which makes them available to use in more SQLExecutors in the Workspace
When you do something like that and work with user input be aware of Little Bobby Tables (i.e. SQL injection).
Thanks! I assume with the simple sql query it is working, if select some existing attribute. I tried this. But in my case it is not working. I assume because my first SQLExecutor generates sql queries (that should be executed in the next step), based on predefined in the DB functions. So I don’t understand what should be added to capture them into exposed attribute.
An example of the result of the first SQL query is likely needed in order to suggest a solution.
If the result of the first SQL executor is literally an SQL string, then all that is needed is to make sure that the result field has a name given to it in the first SQL statement Eg. SELECT ‘SELECT somefield FROM sometable’ AS SQLstring…
...then can expose the name SQLstring in the first SQLExecutor to use in the second.
First of all, coming from my database background: It is almost never a good idea to store SQL queries in a database. But you may have no influence on that, and you may not have a choice but to work with it, I know that can happen (I myself have to work with a nightmare of a datamodel...).
Secondly: You’ll need to tell us a bit more about your data if you want us to help you.
in my case it is not working
is not very helpful. Why isn’t it working? Do you not get any results, or unexpected results, or error messages, or errors in FME logs? What happens? You do realize that whatever your first query returns can and should be exposed, which means the results of that query are stored in attributes, right?
The basic principle as posted by the others works, I use that myself frequently enough. But in order to help you further, we’d need a bit more info on what and how you are querying, and what you expect to happen afterwards. You do not need to use your own data, if that is too sensitive, but you could create a simple example that shows what you have. That is, if bwn’s example is not enough to help you further.
Can you see the returned sql in an attribute?
Does it have a ; on the end of the query?