SQL Syntax correctness validation

Active Query Builder can check SELECT queries for syntax correctness against dozens of database servers. You define the needed SQL dialect by assigning the appropriate syntax provider to the QueryBuilder.SyntaxProvider property. Don't use the Auto syntax provider unless you establish live connection to your database. If you can't find the right syntax provider for your server, use the ANSI SQL/2003 or SQL/92 syntax providers which can help you to validate SQL queries complying with respective standards.

To validate a query against specific SQL syntax with Active Query Builder .NET Edition, use the ValidateQuery() method of appropriate syntax provider:

MSSQLSyntaxProvider syntax = new MSSQLSyntaxProvider();
string errorInfo = "";

if (!syntax.ValidateQuery("select * from Orders", ref errorInfo))
{
   MessageBox.Show(errorInfo);
}

Having the VCL or ActiveX Edition of Active Query Builder, you can assign SQL query text to the TacQueryBuilder.SQL property in a try-except (try-catch) block.

try
  acQueryBuilder.SQL:='select * from Orders';
except
  on e:EacSQLError do
    ShowMessage('Unexpected token "'+e.ErrorTokenValue+'" in line '+IntToStr(e.ErrorPos.line);
end;

Such a validation does not perform validation against specific database context, i.e., the presence of tables and fields mentioned in the query in the database schema.

Checking a query for validity against the database schema

If you don't have Active Query Builder but have a live database connection established, you can execute the query without data retrieval by calling the following method:

SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly).

But acting this way you will load your database server and sometimes get vague or obscure responses.

Having Active Query Builder, you can learn if a query complies with your schema without actually executing it. To load the information about your database schema, Active Query Builder can use the live database connection (loading only the needed information from it) or without it, having loaded full information about your schema beforehand (read more about this for the .NET edition and other editions).

After assigning a query to the SQL property, you can check if objects and fields found in the query exist in the database schema by reading the QueryBuilder.QueryStatistics.UsedDatabaseObjects and ...UsedDatabaseObjectFields collections. Each item in those collections has a reference to the MetadataObject or MetadataField object in the metadata container. If Active Query Builder resolves a reference of a particular object in the query to a certain object in the MetadataContainer, this property points to it; otherwise, it is null.

You can also instruct the component to generate parsing error for queries with unknown database objects on the stage of assigning a query to the SQL property by setting the QueryBuilder.BehaviorOptions.ParsingErrorOnUnknownObjects property to true.

Note that Active Query Builder can parse and analyse SELECT statements only. For the rest of the statements, it switches to the Sleep Mode. You may deny switching to this mode by setting the QueryBuilder.BehaviorOptions.AllowSleepMode to false.