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