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?
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
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.
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");
}
Hello,
You can call the QB.Web.Application.MessageError(message) method to display an error.
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.