Dynamic Queries

  

Hi

I am writing an eSpace that allows users to create a dashboard report on the fly, based on a library of available queries.

I am allowing admin roles to create the queries and store the SQL in the db.

So when a user opens their dashboard a list SQL statements are fetched in turn, any user-specific parameters are injected and then the SQL is passed as a text as an Input parameter to an Advanced Query, the Advanced Query then returns the result value to be shown in the dashboard report.

Are there any performance issues in Outsystems when using input parameters that contain the entire query, often containing complex SQL?  Can they be mitigated?

Are there any better patterns to implement this type of functionality dynamically?


Any advice is welcome ;-)

Charles

Hi Charles,

I'm not DBA, but I would say that yes, you probably will have performance issues as OutSystems use the sp_executesql to run the sql in the database and than, your query will be, well... a parameter (that will be send to the DB like it is).  

Besides that, I see tremendous, huge, absurdly high potential to, well, absolutely caos?

No one is absolutely thrustworthy, doesn't matter if he has an "Admin" role.
You should, at least, pre process the queries to guarantee that only SELECT commands are being made (and even that is possible to get around...

Flexibility comes with a cost, and this cost is way too high, imho.

Why do you need such a thing?

Cheers.

P.S. If something like this is really necessary, the best would implement some kind of visual toll, like a pivot table, or something else that create a security layer between the user and the data, to avoid bigger problems.

Thanks - Point taken on the chaos theory.  I am pre-processing the SQL to enforce a select statement and taking other precautions.  

Unfortunately, the parameters are insufficiently homogeneous to fit well within a pivot style template and I also need to store a Description, Title etc alongside each value in the dashboard report.

Ok.

If you're sanitizing the SQL, less problematic... But you will have performance issues, almost for sure. But usually flexibility price is paid with less performance. If the user is aware, and accept it, than, this is not a problem.

I would try to avoid doing it, but don't have a good answer on how to do it.