Hi Community team,
I have an requirement to fetch all the Schema Names and their associated 'Table Names with their Sizes' to display them in the appian interface.
In order to achieve this, From the cloud database I have executed the following SQL statement which gives me the output:
SELECT table_schema as `DB`, table_name AS `Table`, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size` FROM information_schema.TABLES WHERE TABLE_SCHEMA IN ("Appian", "SchemaA", "SchemaB") ORDER BY `Size` DESC;
On executing the result: EX
Similarly, I'm trying to implement the same functionality in Appian using Query Database Smart Service in Process Model, on the set-up tab I'm able see only one schema(Selecting Pre-defined schema ) details:
Instead of that I need to retrieve all the mentioned schema details. Kindly let me know any solution for this.
Thanks in advance,
Chandrasekhar
Discussion posts and replies are publicly visible
You will have to execute the smart service once for every schema in your database....
Create multiple Query Database Smart Service activities within your process model, one for each schema you want to query. Each activity will have a static SQL statement specific to the targeted schema.After each activity execution, use a decision shape or appropriate logic to combine the retrieved data from each schema into a single list. You can achieve this by concatenating the output lists from each activity.
Hi
Thanks for your response, by selecting the pre-defined schema it gives only that schema details. In my case I need all Schema's details.
Hi Shubham Aware,
Appreciate your response, like need to use 'AND ' Gate append each schema details into the list and debug the process, correct?
And, We are looking for other alternatives ways to display them dynamically. Open for suggestions and solutions.
I know.... that's why you ned to create as much datasources as schemas in your environment, and execeute the smart service once for each one of them
Yes Correct!!Use separate Query Database Smart Services for each schema.After each execution , append retrieved data to a single list using appendDisplay the final list containing combined data.
append