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

Answered

Comments

6 comments

  • Avatar
    Sergey Smagin (Product manager)

    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.

    0
    Comment actions Permalink
  • 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

    0
    Comment actions Permalink
  • Avatar
    ActiveDBSoft support team

    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.

    0
    Comment actions Permalink
  • Avatar
    ActiveDBSoft support team

    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"); }

    0
    Comment actions Permalink
  • 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??

    0
    Comment actions Permalink
  • Avatar
    Sergey Smagin (Product manager)

    Hello,

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

    0
    Comment actions Permalink

Please sign in to leave a comment.