New to the site and looking to develop a program object that will return the results of a BQL query to a numeric slot. In our case we are looking to see how many points in a device folder with alarm extensions are in alarm. We have played with the Vykon Pro BQL modules, but they are setup to return point values that equal defined values.
Found this sample code but doesn’t compile in Program Editor
Sample BQL station:|slot:/Drivers/SnmpNetwork/Alpha_DCPlant_1/points|bql:select * from alarm:AlarmSourceExt where alarmState > ‘0’
station:|slot:/Drivers/SnmpNetwork/Alpha_DCPlant_1/points|bql:select * from control:ControlPoint where alarm.status = ‘true’
public class MyBqlProgram extends BProgram {
public void onExecute() throws Exception {
// Define the BQL query as an ORD string using the cell schema
// This example counts all points in a station with “Fault” status
BOrd queryOrd = BOrd.make(“station:|slot:/|bql:select count(status) from control:Point where status.fault = ‘true’|cell:0,0”);
// Fetch the result from the station
Object result = queryOrd.get(Sys.getStation());
// Cast the result to a BValue (or a specific type like BDouble if expected)
// and extract the value
if (result instanceof BValue) {
double faultCount = ((BValue) result).getDouble();
// Use the faultCount in your program logic, e.g., setting a point's value
setDevicesInFault(faultCount);
}
}
// Define an output point or field (e.g., a ‘devicesInFault’ NumericPoint field)
public final BDouble devicesInFault = new BDouble();
public void setDevicesInFault(double v) { setValue(“devicesInFault”, BDouble.make(v));
}
}
We have been using a developer who retired and haven’t found a replacement. Had Ecilpse with the Niagara plugin working in the AX days, but that was years ago.
I’ve got this working on a demo station to show you how it looks, check out the screenshots I attached. The first image shows the Program object successfully pulling a count of 2.00, and the second shows the FCU1_1 graphic where those alarms are originating.
Here is the working code I used for that Program object:
java
public void onExecute() throws Exception
{
try {
BOrd basePath = getQueryPath();
if (basePath == null) {
basePath = BOrd.make("station:|slot:/Drivers");
}
// Use |cell:0,0 to extract single value directly
String queryStr = basePath.toString() +
"|bql:select count(*) from alarm:AlarmSourceExt|cell:0,0";
BOrd queryOrd = BOrd.make(queryStr);
Object result = queryOrd.get();
double count = 0.0;
if (result instanceof BInteger) {
count = ((BInteger)result).getInt();
} else if (result instanceof BLong) {
count = ((BLong)result).getLong();
} else if (result != null) {
count = Double.parseDouble(result.toString());
}
BStatusNumeric out = getAlarmCount();
out.setValue(count);
} catch (Exception e) {
e.printStackTrace();
}
}
Setup:
Create a Program object
Add slots via the Slots tab:
alarmCount (Type: baja:StatusNumeric, Flags: so)
queryPath (Type: baja:Ord, Flags: so)
Set queryPath to your folder, e.g.: station:|slot:/Drivers/SnmpNetwork/Alpha_DCPlant_1/points
Trigger execution via schedule or link
Alternative Approaches (No Programming Required)
Depending on your needs, these might be simpler:
AlarmService Counts - If you assign unique Alarm Classes to each device folder, the AlarmService tracks counts per class automatically. Check station:|slot:/Services/AlarmService for properties like openAlarmCount.
AlarmConsole/AlarmDb - The alarm database has built-in count properties you can link to directly.
Bound Labels - For display-only (not logic), a BoundLabel in a PX view can show BQL results directly without a program.
This looks like a great start. Need to add the code for an interval timer and a way to pick the parent folder for the query. I am curious if you have any concerns about system resource with a bunch of these alarm summary programs running concurrently at 10-30 second intervals.
This requirement is for a multi site customer that we display the number of alarms and comm faults for each site on the dashboard. Some of the alarms come from a Jace and others are devices that are directly mapped to the Server using ModbusTCP, SNMP or BacnetIP. As this system is expanding we are pushing to use Jaces instead of the direct mapping path.
Thanks for the help with this. I have another function I have asked Tridium for help with and only heard crickets!
Good catch on the empty result issue! When count(*) returns no rows, the |cell:0,0 throws an IndexOutOfBoundsException. Here’s the fixed code with null/empty handling:
java
public void onExecute() throws Exception
{
try {
BOrd basePath = getQueryPath();
if (basePath == null) {
basePath = BOrd.make("station:|slot:/Drivers");
}
// Query for active alarms only
String queryStr = basePath.toString() +
"|bql:select count(*) from alarm:AlarmSourceExt where alarmState != 'normal'|cell:0,0";
BOrd queryOrd = BOrd.make(queryStr);
double count = 0.0;
try {
Object result = queryOrd.get();
if (result != null) {
if (result instanceof BInteger) {
count = ((BInteger)result).getInt();
} else if (result instanceof BLong) {
count = ((BLong)result).getLong();
} else {
count = Double.parseDouble(result.toString());
}
}
} catch (IndexOutOfBoundsException e) {
// Empty result set = 0 alarms
count = 0.0;
}
BStatusNumeric out = getAlarmCount();
out.setValue(count);
} catch (Exception e) {
e.printStackTrace();
}
}
The key fix is wrapping the query execution in its own try-catch for IndexOutOfBoundsException - when no alarms exist, we just set count to 0.
Performance Concerns
For a handful of programs at 10-30 second intervals, you should be fine - BQL count queries are lightweight. However, with “a bunch” running across many sites, here are some considerations:
Stagger execution times - Don’t trigger all programs at exactly :00/:30. Use slightly offset schedules (site1 at :00, site2 at :02, etc.)
Consolidate into one program - Instead of 20 separate programs, one program could iterate through a list of paths and update a set of output slots. Runs once, updates all counts.
Example of that code could be something like this:
Sounds like a typical Tridium quirk, why not return zero when asking for a count instead of throwing an error? I will give Tridium a hard time at the AHR show this week. If you are going I would love to buy you dinner/drinks for your help.
Curious why the check for all the different types? Does Tridium not return count values as an Integer or Long values? I would think this would all you need:
if (result != null) {
count = ((BInteger)result).getInt();
This program will be used on each device (Jace or Supervisor) that will connect to an Enum that provides a simple status.
We then aggregate the pXStatus (our name for the enum) points by site to show on a dashboard alarms/comm faults per Site and State.
We deal with all sorts of devices, some with a common alarm and others that have dozens of system alarms along with any points we add extensions to. Typically we link the alarm status to a SuperOr which is pain, and if we add a point need to make sure it gets linked.
Last thing we need is code to pick up the parent folder just like an alarm or history ext does.
Also is there a decent reference for all the functions for things like IndexOutOfBoundsException descriptors?
Yeah, no real reason for all the type checking - just AI being overcautious lol.
As for references - the Tridium docs really help with the classes you want to extend, but when it comes to the compiler and tracking down errors it’s usually me, Google, and whatever AI going at it until I get the results I want. The standard Java documentation covers all exceptions:
Between some YouTube videos, the great @CharlesJohnson, and lots of late nights just going at it, there really hasn’t been much more than trial and error until you get what you want. Once you have the Java basics and patterns understood along with the baja docs, it does slowly start making more sense.
So in the last year or so of learning I think I’ve figured out just enough to have it all be useless come N5 I’m sure lol. But keep learning! If you need any help or want to share backups and chat about other options just let me know.
It returns an integer, and if I understand what your looking for you can add the folder path so when you drop the program it fills the queryPath then you can execute and get count here:
public void onStart() throws Exception
{
// Auto-set queryPath to parent folder on startup
BComponent parent = (BComponent)getComponent().getParent();
if (parent != null) {
String parentPath = "station:|" + parent.getSlotPath().toString();
setQueryPath(BOrd.make(parentPath));
}
}
public void onExecute() throws Exception
{
try {
BOrd basePath = getQueryPath();
// Safety check if queryPath not set
if (basePath == null || basePath.toString().isEmpty()) {
BComponent parent = (BComponent)getComponent().getParent();
if (parent != null) {
basePath = BOrd.make("station:|" + parent.getSlotPath().toString());
} else {
return; // Can't run without a valid path
}
}
String queryStr = basePath.toString() +
"|bql:select count(*) from alarm:AlarmSourceExt where alarmState != 'normal'|cell:0,0";
BOrd queryOrd = BOrd.make(queryStr);
int count = 0;
try {
Object result = queryOrd.get();
if (result != null) {
count = ((BInteger)result).getInt();
}
} catch (IndexOutOfBoundsException e) {
count = 0;
}
getAlarmCount().setValue(count);
} catch (Exception e) {
e.printStackTrace();
}
}
So version 1 is working but I not happy with the fact most of time there are no alarms and my queries will be empty most of the time throwing an error. I like the way the Vykon Pro BQL functions and builds a list of elements once (and refreshed at a longer interval or manually) and we count the state of the points, or in my case the number of Alarms Extensions that are in alarm.
I was hoping this thread was still active to come up with a better way to query for points in alarm. My code is working but seems that running frequent queries that are null causing an exception is not the best way to do this.
Using the VykonPro bql modules as an example, There must be a way to build a query of all the alarm extensions in a folder that you can just check to see which are not in alarm:
query1= station:|slot:/Drivers/AlarmTest|bql:select * from alarm:AlarmSourceExt
BOrd queryOrd1 = BOrd.make(query1);
This query would only execute if you added and deleted points with alarm extension. and instead of this code that returns an exception when everything is nomal:
// Use |cell:0,0 to extract single value directly
String queryStr = basePath.toString() +
"|bql:select count(*) from alarm:AlarmSourceExt where alarmState != 'normal'|cell:0,0";
// Return count of active alarms
BOrd queryOrd = BOrd.make(queryStr);
Object result = queryOrd.get();
To something that would work on the saved query of alarm extensions
"|bql:select count(*) from queryOrd1 where alarmState != 'normal'|cell:0,0"
Not sure how to save a query handle and run another query against it.
