Active Query Builder support area

Fail query validation if custom function is used and not in metadata xml

Avatar
  • updated
  • Completed

If a user types a UDF in the SQL Editor that is in the database, but isn't in the object tree or metadata, I'd like it to throw an error when they click the refresh/update icon just as if they had typed a table name that isn't there. Is this possible?

Avatar
Andrey Zavyalov, PM

Hello, Marc.

Are you talking about table-valued functions that can be used in the FROM clause or functions in SQL expressions? What you mean by "just as if they had typed a table name that isn't there" - are you talking about the error that is raised when you set the ParsingErrorOnUnknownObjects property to true? An example of what you mean will be very helpful.

Avatar
Marc

Yes, I would like to have it throw an error on unknown objects. I am using

QueryBuilderControl1.QueryBuilder.BehaviorOptions.ParsingErrorOnUnknownObjects = true;

An example would be using your online demo:

Select * From HumanResources.Department

Where someUnwantedFunction(HumanResources.Department.Name) = 1

Avatar

Unfortunately it not possible to setup such behavior with a single property switching. But using Active Query Builder you can get access to any single token of the query AST (abstract syntax tree), and you will know which token is a function, so you can alert the user if unwanted functions are found. I can send you the code sample of doing so if you wish.

Avatar

Here is the code snippet which lists all functions used in query:

using (var syntax = new MSSQLSyntaxProvider())
using (var queryBuilder = new QueryBuilder() {SyntaxProvider = syntax})
{
  queryBuilder.SQL = "Select * From HumanResources.Department"+
    " Where someUnwantedFunction(HumanResources.Department.Name) = 1";

  // get AST (abstract syntax tree) representation
  var queryAst = queryBuilder.QueryView.Query.QueryRoot.ResultQueryAST;
// collect all AST nodes in tree var allNodes = new List<AstNodeBase>(); queryAst.GetMyChildrenRecursive(allNodes); // filter function calls
var allFunctionCalls = allNodes.OfType<SQLExpressionFunction>(); // show function names
var sb = new StringBuilder(); foreach (var functionCall in allFunctionCalls) sb.AppendLine(functionCall.Name.QualifiedName);
MessageBox.Show(sb.ToString(), "Used Functions"); }

Avatar
Marc

Is there a way for me to have the control display the same error dialog as with other invalid statements so the user can see 'Unexpected function "someUnwantedFunction" at line X, pos X', i.e. display the error popup and set the qb-ui-editor-refresh-button-label text??

Avatar
Andrey Zavyalov, PM

Hello,

You can call the QB.Web.Application.MessageError(message) method to display an error.