Active Query Builder Web API

Commands List


The TransformSQL command

Modifies a given SELECT SQL statement in order to change sorting order of resulting data, add filters, hide columns, limit result set rows and apply pagination. It also makes it possible to build an auxiliary query to get totals (aggregated values).

The TransformSql command is essential if you are building a data browsing facility, which displays data returned by an arbitrary user query. Regardless of the complexity of a given statement, this command modifies it according to the instructions provided in request parameters.

The TransformSQL command is very helpful for background modification of user queries for the purpose of hiding unwanted information from end-users (row-level security).

You can add new conditions for query column values, hide some of the result set columns, change sorting and set limitations on the number of rows returned by the query. All query changes are performed in strict accordance with the SQL syntax and database schema specified in the user’s account settings for the given unique identifier.

The command returns a modified SELECT statement that is 100% compatible with your database server. Active Query Builder supports dozens of SQL syntax dialects for the vast majority of today’s popular database servers.

The important point is that all query modifications that can be made with this command may only lead to the reorganization or limitation of the original result set: it is impossible to get a query that reveals any other data rather than the data returned by the original query.

The GetQueryColumns command

Returns a list of query columns for a given query.

Knowing the information about the SQL query execution context (database schema), this command returns the names and data types of result set columns without executing a query. It correctly expands asterisk items in the SELECT list, calculates columns returned by derived tables and CTEs.