Query database dynamic table name

Question

Hi all, 
 I'm curre ntly designing a post-deployment process and I need to be able to query database tables dynamically based on the application being deployed. I&rsquo;ve tried using the Query Database smart service with variables such as ac!table , but I get an error each time.

I&rsquo;ve also tried setting the full SQL statement in an ac! variable to create the complete query and pass it, but this approach hasn't been successful either.

Has anyone encountered a similar issue or can suggest a solution to dynamically query tables based on deployment context? Any insights would be greatly appreciated! 
 Thanks in advance!

Mike Schmitt · Answer

What's your use case, specifically? 
 The QDB node is, by everything I can tell, ancient, and its funcitonality is extraordinarily clunky - I recommend that its use be extremely limited, if it's even used at all. As you've seen, there are hidden and (afaik) undocumented syntactical limitations to exactly how variables can be used in it, and when and where. If at all possible you should look into using Query Entity - depending on the nature of the different tables / data store entities you need to query, a single expression rule could probably handle deciding which table to query and return data to your process.

Mathieu Drouin · Answer

Pretty sure you can't do that since Appian uses prepared statements to prevent SQL injection. Your best best is probably to implement a Stored Procedure.

Chaitanya Teja · Answer

I had a similar requirement to dynamically pass the column name, table name, and primary key as parameters. Since Appian doesn't support dynamic queries, I am planning to create a stored procedure in the database to handle this and use Execute stored procedure smart service instead of Query database. The procedure will accept these parameters and return the required result. 
 However, I am also exploring the possibility of achieving this using a query entity or query record.