Named Queries: Building a Reusable SQL Library for SCADA Reports and Screens

Introduction

Ask an experienced SCADA engineer how reports and screens get their data from the plant's SQL historian, and you'll often hear the same story: every screen has its own query hardcoded into a script, every report has the same query with a slightly different WHERE clause, and when the database schema changes, someone has to hunt through the entire codebase to find and update them all.

Named queries fix this by treating SQL as a versioned, parameterized library. Write each query once, give it a name and typed parameters, and let screens, reports, scripts, and external systems call it by name.

The Problem With Ad-Hoc SQL

Consider a simple report: "show me batches on line 3 from the past 7 days." The obvious implementation is:

```sql

SELECT batch_id, start_time, end_time, status

FROM batches

WHERE line_id = 3 AND start_time >= DATEADD(DAY, -7, GETDATE())

```

That query now lives inside one report template. Next week, an operator asks for the same view but on line 5 — so another report is created with the query copy-pasted and edited. Then someone wants a screen that shows the same data but for the current shift — another copy appears in a screen script. Three months later, the batches table gets a shift column, and someone has to locate all three copies.

Worse, if any of those queries builds the WHERE clause by string-concatenating operator input, you've introduced a SQL injection risk into what was supposed to be a read-only dashboard.

What is a Named Query?

A named query is a saved, parameterized SELECT statement with:

• A stable name that callers use to look it up (e.g., batches_by_line)
• A SQL template with :paramName placeholders where values go
• A parameter definition — name, type (string / number / boolean), optional default
• A binding to a data source (an external DB, or the system's built-in SQLite)

The same batch-report example becomes:

```sql

-- Query name: batches_by_line

SELECT batch_id, start_time, end_time, status

FROM batches

WHERE line_id = :lineId

AND start_time >= :sinceDate

ORDER BY start_time DESC

```

Now every consumer calls batches_by_line with { lineId: 3, sinceDate: "2026-04-01" } and gets the rows back. Change the schema once, update the query once, and every consumer picks up the new version on their next call.

Safety: How Parameters Prevent Injection

The critical rule: values are bound as positional parameters, never concatenated into SQL. At runtime the engine converts :lineId into the dialect's native placeholder — $1 in PostgreSQL, ? in MySQL and SQLite, @p0 in SQL Server — and passes the actual value through the driver's parameter binding.

An operator typing 3; DROP TABLE batches as their line ID simply produces a query that looks for a batch whose line_id equals the string "3; DROP TABLE batches", which matches nothing. The drop statement is never executed because it's data, not code.

Additional guardrails worth enforcing at the engine level:

• Accept only SELECT and WITH queries — reject anything that starts with INSERT, UPDATE, DELETE, DROP, ALTER, CREATE, EXEC, or GRANT.
• Reject statement chaining — a semicolon followed by more non-whitespace should be refused, closing off piggyback attacks.
• Comments stripped before validation — -- and / / comments can't be used to smuggle a different statement past the SELECT check.

Four Ways to Consume a Named Query

1. Embedded in a synoptic screen

Drop a "Query Table" component onto a screen. Pick the named query, and every :param turns into a binding slot. Each slot can be:

• A tag reference — the query re-runs whenever the tag changes
• A literal — prefix with = (e.g., =3, ='critical')

A single Query Table bound to Line_ID (a tag) acts as a live dashboard that follows the operator's selection.

2. Reports

The report builder picks a named query as its data source. At preview / PDF time, the user fills in parameter values, and the rows become the report's body. Report templates save the parameter values so scheduled reports re-run with the same inputs.

3. Python event scripts

Inside a tag-change or scheduled script:

```python

result = run_named_query('batches_by_line', {'lineId': 3, 'sinceDate': '2026-04-01'})

if result['rowCount'] > 10:

log(f"Unusual batch volume detected: {result['rowCount']} in 7 days")

```

The script talks to the query engine over a loopback-only internal endpoint — scripts get first-class access to the library, but the internal endpoint is never exposed to the network.

4. External API calls

A production monitoring tool, a BI dashboard, or a weekly email job calls:

```http

POST /api/named-queries/batches_by_line/execute

Authorization: Bearer

Content-Type: application/json

{ "params": { "lineId": 3, "sinceDate": "2026-04-01" } }

```

Response: { queryId, name, rowCount, rows: [...] }.

The beauty of this shared endpoint is that the SQL lives in one place. External tools don't get direct database credentials; they get an authenticated, role-checked, rate-limited call to a vetted query.

Design Patterns

A few patterns tend to emerge once you start treating queries as a library:

• One query per "question" — name queries after the business question (recent_unacknowledged_alarms, overdue_batches) rather than the table (select_alarms). Keeps the library discoverable.
• Stable parameter names — :sinceDate, :maxRows, :severity used consistently across queries make composition easier.
• Sensible defaults — maxRows defaulting to 100 means callers that don't care about pagination still get a reasonable response.
• Comments inside the SQL — the SQL template itself is the best documentation. A short -- Returns ... ordered by ... comment saves future maintainers a lot of time.

When Not to Use Named Queries

Named queries are the right tool for read-only, cacheable, parameterized access. They are not a replacement for:

• High-frequency real-time polling — for live tag values, use the tag system directly. Named queries are for aggregated or historical data that changes at human timescales.
• Data ingestion / writes — by design, only SELECTs are allowed. Writes belong in dedicated endpoints with their own authorization and audit trail.
• Complex workflows with branching — if you're about to write five named queries and a script that orchestrates them, consider an API endpoint purpose-built for that workflow.

Conclusion

Named queries turn scattered, duplicated SQL into a vetted, versioned library that the whole SCADA system shares. Screens, reports, scripts, and external tools all call the same batches_by_line query by name; a single change updates every consumer. Parameter binding eliminates SQL injection as a class of bug. And keeping all queries in one catalog makes auditing, performance tuning, and onboarding new engineers dramatically easier.

OptiZeus implements named queries as a first-class feature with a visual editor, a test runner that previews the first 100 rows with typed parameter inputs, built-in SELECT/WITH-only validation, dialect-aware parameter binding for PostgreSQL, MySQL, SQL Server, and SQLite, and four consumer paths (Query Table screen component, Report Builder data source, Python script helper, and external REST API). The SQL library lives in the project directory as a single JSON file, ready for version control.