Active Query Builder support area

Replace output SELECT * with output columns

Avatar
Marc
  • updated
  • Completed

I'd like to replace the SELECT * with the actual column names in a query for the output columns. I think I can use QueryStatistics.OutputColumns and loop over the OutputColumn.Expression to create a comma separated list, but is there a better way to replace the SELECT * that just with string manipulation? I'm using the ASP.NET version C#

Replies 4
Avatar
0
Andrey Zavyalov, PM

Hello, Marc.

This task has already been completed in the QueryTransformer object. The sample is below.

QueryTransformer _queryTransformer = new QueryTransformer { 
    QueryBuilder = _queryBuilder,
    AlwaysExpandColumnsInQuery = true
};
_queryBuilder.SQL = _queryTransformer.SQL;

Let me know if you have any further questions.


Avatar
0
Marc

Isn't that just in the v3? I'm using the ASP.NET edition. I think I figured it out. Please confirm: 

var queryBuilder = QueryBuilderControl1.QueryBuilder;
var selectExpression = FindFirstSelect(queryBuilder.ResultQueryAST);
queryBuilder.SyntaxProvider.ExpandQueryColumns(selectExpression);

    private SQLSubQuerySelectExpression FindFirstSelect(SQLSubQueryExpressions queryGroup) {
        SQLSubQuerySelectExpression result = null;
        for (int i = 0; i < queryGroup.Count; i++) {
            if (queryGroup[i] is SQLSubQuerySelectExpression) {
                return (SQLSubQuerySelectExpression) queryGroup[i];
            } else if (queryGroup[i] is SQLSubQueryExpressions) {
                result = FindFirstSelect((SQLSubQueryExpressions) queryGroup[i]);
                if (result != null) {
                    return result;
                }
            }
        }
        return result;
    }
Avatar
0
Andrey Zavyalov, PM

Hello, Marc.

I confirm that the QueryTransformer object exists in the ASP.NET version. You can use both ways to cope with this task.

Avatar
0
ActiveDBSoft support team

Sorry for a mistake, this API is not yet backported from v3.

You could use low-level API for this: 

var ctx = queryBuilder1.SQLContext;
var syntax = ctx.SyntaxProvider;

using (var queryAst = (SQLSelectStatement) queryBuilder1.ResultQueryAST.Clone(ctx))
{
    syntax.ExpandQueryColumns(queryAst.FirstUnionSubQuery());
    var resultSql = queryAst.GetSQL(ctx.SQLBuilderExpressionForServer);

    MessageBox.Show(resultSql);
}