SQL Databases

Connection properties#

Taking SQL Server as representative:

The Query at connection level is a default; each measure can override it. Where hundreds of measures read the same table with only a tag name differing, put the query on the connection and use a model attribute for the part that varies.

DateTime Precision matters when a source stores timestamps more or less precisely than Infohub assumes. A mismatch shows up as boundary values landing in the wrong period — usually one row too many or too few at each period edge.

Where the query lives: tag or measure#

A query can be configured on a Tag-interval measure (returning raw timestamped rows) or directly on an interval measure (returning rows for the period being produced). Both are supported in 7.2.0 — the connector declares its query property against both targets.

Prefer the tag level as the default, for the same reasons as any retrieval: the raw rows are kept, the interval chain derives from one place, and the query runs once rather than per interval. A query directly on an interval measure remains legitimate where the source is itself already period-shaped — one row per day, say — and querying raw rows would be artificial.

If you are migrating content from an older version and find query configurations behaving oddly after upgrade, re-save the retrieval configuration — older measure-level query configurations occasionally need a re-save to settle into the current property model.

Writing the query#

The query must return values for the period Infohub asks for. Infohub substitutes the window:

SELECT Value, Quality
FROM   History
WHERE  TagName = 'Boiler1.Steam'
  AND  DateTime >  [PeriodStart]
  AND  DateTime <= [PeriodEnd]

Returning value and quality#

Return the value column, and a quality column where the source has one. Where it does not, returning only the value is fine — Infohub will treat the values as good.

Alias the columns clearly if the source names are cryptic. Do not return extra columns you do not need; they cost transfer time on every retrieval, for every measure.

Templatable queries#

[ModelAttribute(...)] is what makes one query serve many measures:

SELECT Reading AS Value, QualityCode AS Quality
FROM   ProcessData
WHERE  UnitNo   = '[ModelAttribute("Unit Number")]'
  AND  PointRef = '[ModelAttribute("Point Ref")]'
  AND  Stamp >  [PeriodStart]
  AND  Stamp <= [PeriodEnd]

One query on the connection, two attributes per instance folder, and a template that serves every unit. See Model Attributes.

Performance is your responsibility here#

With a historian connector, the vendor has optimised retrieval. With SQL you are writing the query, and a bad one will be executed once per measure per interval — potentially thousands of times an hour.

Index the columns you filter on. A query filtering on a tag identifier and a timestamp needs an index covering both. Without one, every retrieval scans the table. This single point accounts for most Flow-related database load complaints.

Filter in the database, not in Infohub. Return the rows you need, not the table.

Avoid functions on the filtered column. WHERE CAST(Stamp AS date) = ... cannot use an index on Stamp. Compare the column directly against the placeholders.

Test the query with realistic bounds first. Run it in a query tool with a real period substituted and look at the execution plan and the duration before you build a hundred measures on it.

Set a Timeout. A query with no limit can hold a connection open indefinitely and starve the rest of the collection.

Views are usually better than queries in Infohub#

Where a query becomes complicated — joins, unions, business logic — consider putting a view in the database and querying that.

The view can be indexed, tuned, and reviewed by whoever owns the database, and the query in Infohub becomes a simple SELECT ... FROM vFlowReadings WHERE .... It also means the logic is visible to the database's own tooling rather than buried in a connection property where nobody will find it.

The trade-off is that the logic now lives in two systems. Document which side owns what.

Integrated security#

Leaving username and password blank uses Windows integrated security — which authenticates as you in Flow Config and as the Flow Bootstrap service account once deployed.

For SQL Server with trusted connections, plan on running Bootstrap as a domain account with rights on the source database. Grant it read-only; Infohub never writes to a source.

Oracle, PostgreSQL, and MySQL#

The connectors are equivalent in shape. Differences are dialect, not concept:

• Oracle — date handling and identifier case differ; be explicit about both.
• PostgreSQL — case-sensitive quoted identifiers; if a query fails on a column name that plainly exists, quoting is the usual reason.
• MySQL — pay attention to time zone handling on the connection, which affects how the substituted period is interpreted.

In every case, write the query in the database's own tool first, confirm it returns what you expect for a fixed window, then move it into Infohub and replace the fixed bounds with the placeholders.

Triggers and event attributes#

The SQL connectors also expose query properties for event triggers and event attributes, with a Query Period on triggers. This is how a production run recorded in an MES table becomes an event scheme in Infohub, with its attributes read from the same database.

Trigger queries have a subtlety worth knowing: they must return timestamps that fall inside the window being examined. A query returning a time of day rather than an absolute timestamp will not match. See Event Triggers.

Diagnosing#

See also#

• How Infohub Retrieves Data
• Model Attributes
• Choosing a Data Source
• Connection Property Reference