Active Query Builder Web API

Online SQL Builder Demo

Online SQL Query Builder - HTML Demo

This page demonstrates a full-featured data browsing user interface with Online SQL Query Builder created using the Active Query Builder Web API.

Such Online SQL Query Builder can be added to a web project of any programming environment and architecture, including Java, NodeJS, JavaScript, PHP, Python, Ruby, Perl, etc.

All user-requested data manipulations, such as sorting, filtering, pagination and calculation of totals are performed on the server side by database server. Meanwhile, SQL text is not passed to/from the client ensuring 100% protection from SQL injection attacks.

Custom JqGrid JqxGrid
Online SQL Query Builder
Click the column headers to change sorting by a column. Hold the Ctrl key on clicking to sort by several columns.
Server-side totals calculation is possible only when sorting is defined.
Online SQL Query Builder
Source code
Online SQL Query Builder
Source code

Online SQL Query Builder Architecture

Cloud

Active Query Builder Web API User account with a preloaded database schema snapshot.

Client

Web client JavaScript library for Active Query Builder Web API: direct download or npm package.

Online SQL Query Builder Client configuration

Query Transformer object

Create a new instance of the QueryTransformer object and pass the URL that will be used to for accessing the web server, obtaining the list of query columns and passing the user's commands.

var QT = new ActiveQueryBuilder.QueryTransformer(url);

Two handlers must be defined on the server: GetQueryColumns and TransformSql (see the "Server configuration" section below).

For example:

var QT = new ActiveQueryBuilder.QueryTransformer("http://localhost:54459");

The list of query columns will be requested from "http://localhost:54459/GetQueryColumns", and user commands will be passed to "http://localhost:54459/TransformSql".

Criteria Builder control

To display the Criteria Builder control, call the criteriaBuilder.Init method and pass the id of the container HTML element as a parameter:

QT.criteriaBuilder.init(id);

Server configuration

To send a request to Active Query Builder Web API, you must pass the identifier of your database schema snapshot via the "_guid" parameter, and the SQL query text to modify via the "_sql" parameter.

Request for query columns

Define a server-side method to handle the GetQueryColumns request as follows.

public ActionResult GetQueryColumns()
{
    ActiveQueryBuilderApi api = new ActiveQueryBuilderApi();
    SqlQuery query = new SqlQuery(_guid, _sql);
    List<QueryColumn> columns = api.GetColumnsPost(query);
    return Json(columns);
}

Request for data

Define a server-side method to handle the TransformSql request as follows.

public ActionResult Transform(Transform transform)
{
    transform.Guid = _guid;
    transform.Sql = _sql;
    ActiveQueryBuilderApi api = new ActiveQueryBuilderApi();
    TransformResult result = api.TransformSqlPost(transform);
    if(!string.IsNullOrEmpty(result.Error))
        return new HttpBadRequestResult(result.Error);
    return GetDataMsSql(result.Sql); // send data to the client
}

Working with the client library

Getting list of query columns and initial data request

const QT = new ActiveQueryBuilder.QueryTransformer("http://localhost:54459"); 
QT.pagination.skip( skipCount );
QT.pagination.take( takeCount );
 
var columns = [];
QT.on('columnsLoaded', function(_columns) {
    columns = _columns;
    QT.criteriaBuilder.init('criteriaBuilder'); 
    QT.transform(); // send request to server
}
 
QT.on('sendTransform', function() {
    //show loader
});
 
QT.on('dataReceived', function(rows) {
    renderTable(columns, rows); //display data table
    // hide loader
});
QT.loadColumns(); // get query columns

Sorting

$(document).on('click', '.order-asc', function() {
    var column = $(this).text(); //get clicked column name   
    QT.sortings.orderBy(column, 'asc');
    QT.transform(); 
    //the dataReceived event handler will render data table
});

Pagination

function paginate(skipCount, takeCount) {
    QT.pagination.skip( skipCount );
    QT.pagination.take( takeCount );
    QT.transform(); 
}

Filtration

function nameIsNotNull() {
    QT.filters.isNotNull( 'name' ); // WHERE 'name' is not null
    QT.transform(); 
}
function idBetween( from, to ) {
    QT.filters.Between( 'id', from, to ); // WHERE 'id' BETWEEN "from" AND "to"
    QT.transform(); 
}

Alternate way using the Column object:

function idBetween( from, to ) {
    var column = QT.getColumnByName( 'id' );
    QT.filters.add( column.between( from, to ) );
    QT.transform(); 
}

Totals

function maxId() {
    QT.totals.max( 'id' );
    QT.transform(); 
}

Alternate way using the Column object:

function maxId() {
    var column = QT.getColumnByName( 'id' );
    QT.totals.add( column.max() );
    QT.transform(); 
}

Note: The 'demo.js' library contains full JavaScript code of this Online SQL Query Builder web page.

Have questions or comments?

Leave your feedback below.