BQL is already a powerful tool for querying several different data sources in Niagara including component data, alarms, and histories. There is some documentation available in the docDeveloper module that is worth a read. It includes a nice high level view of BQL syntax and lots of examples. While examples are always nice, and more are always better, you can really only get so far with examples. Eventually you'll come across a problem that isn't covered by an example and if you don't understand how it works, you're out of luck. My goal here is to provide a better understanding of the mechanics of how a BQL query is evaluated so that when you have that report you're building that keeps you coming back to the brute force approach or pulling your hair out, you will naturally think BQL instead.

Ok, I've also got an ulterior motive: if I can get more people using BQL, I'll get more feedback and input on how it can be enhanced to provide more value in future versions.

Tools of the trade

If you're going to get good at crafting BQL queries there are some concepts and tools you need to be comfortable with.

1. You need to understand Niagara modules and types. Every component in a station database has a type, and every type is defined in a module. The identifier for a type is called its typespec and it is a string formatted as moduleName:typeName. For example, control:NumericPoint is the typespec for all points in a Niagara station that have a numeric output value.

2. You need to know how to read the SlotSheet view. It is a good tool for finding the name and type for all of the properties of a component. That's important because in most other places where you see a property name, you're actually seeing the display name. BQL uses the real property names, not display names.

3. You need to be comfortable using Bajadoc. Bajadoc not only shows you the property names and types for any component, but also shows you the public methods. A powerful feature of BQL is its ability to invoke methods on components (depending on the method signature). When you're reading the Bajadoc, it's a good idea in this case to flatten the inheritance so you see all of the available properties and methods.

The "select" query

For this discussion I'm going to stick to querying against the component tree. That tends to be the most common need and is also the most interesting from a technical perspective (or at least I think so). What makes it interesting is that you're querying a tree. In this case, BQL's job can be stated like this: take an arbitrarily deep tree structure, pull some nodes out of it, and flatten them into a table of rows and columns.

So let's build a query. The thought process for building a query should really be a methodical, step-by-step process and I just happen to have a good idea of what those steps should be:

1. Decide what part of the tree you care about. Do you want to search the whole tree? or just a network? or even just a device? Give that decision some serious consideration, because choosing too broad a scope can really slow down your query times if that's not what you really need. For example, if all of your VAV's are Lon devices and you've named them all to start with VAV, you don't need to search the whole station database to find them. Instead you can scope your query to only search the LonNetwork.

2. Decide what type of components you're looking for. Think tables here. What is the type of each row in your result? What if you don't know? The easiest way to find the type you need is to find it in the tree, workspace, or property sheet, right click on it, and select Views->Bajadoc Help. That'll open the Bajadoc for the component type and you'll see the name of the module that it comes from and the type. When you're constructing the typespec, drop the 'B' from the name. The typespec for BControlPoint is control:ControlPoint.

3. Decide which ones you care about. The subtree that you selected in step 1 may contain many components of the type you selected in step 2. Do you really want to see all of them? If so, skip this step, if not, read on. The "where" clause allows you to filter components out of the result. You can test property values of the component of interest. You can also test property values of its ancestors or descendants in the tree. You can even invoke methods and test the return value just as if you were accessing a property.

4. Pick your columns. The first three steps help you get your rows. Now you need to decide what information you want in each row. In the simple case, your columns are just properties of the row component and you can just list them by name, separated by commas.

   select name, out from control:NumericPoint
   

   But you can also dive deeper or even move up the tree. "out" on a NumericPoint includes the value and the status (check out the Bajadoc, it's a BNumericStatus). What if I only want the value? Go get it. If you just want the "value" property of the "out" property, then:

   select name, out.value from control:NumericPoint
   

   It's pretty simple if you know that the "out" property on NumericPoint is a StatusNumeric, and if you know that the numeric value is just the "value" property of the StatusNumeric. Again, Bajadoc is key.

Consider the above steps and look at the query syntax:

<base ORD>|bql: select <projection> from <extent> where <predicate>

• Step 1 is selection of the base ORD (Ex. slot:/Config/Drivers/LonNetwork)
• Step 2 is selection of the extent (Ex. control:NumericPoint)
• Step 3 is selection of the predicate (Ex. where name like 'VAV*' or out.value > 70)
• Step 4 is selection of the projection (Ex. name, out)

It may also be helpful to know what's really happening during the query execution. The execution process happens to map pretty closely to the steps that I've already lined.

1. The base ORD is resolved to determine the starting point for searching the tree.
2. Including the starting point, the tree is searched recursively visiting every component and comparing it against the "from" type.
3. For every component that matches the type, the where clause is evaluated. If the where clause is true, the component is included in the result, otherwise it is skipped.
4. For each component that matches by type and passes the where clause, the columns are extracted and added to a table.

And there's your result.

That seems like enough to absorb all at once. Check out the BQL doc in docDeveloper for more help on syntax. I'll try to follow up soon with some tricks and techniques. If you have any suggestions, please post a comment.

Some possible future topics on BQL:

• BQL for developers - Using BQL in code and getting data from the javax.baja.collection API.
• BQL and PX - Using PX and BQL to embed charts and tables in PX views.
• BQL expressions - Combining BQL with slot path to get data that isn't available from component properties.