system.db

system.db runs SQL on the outside databases configured under Connections, Databases (see Databases and storage). Every function here needs the engineer role and is a gateway call, so it is available in cron, console and module scripts, and in a screen script when an engineer or administrator is the one using the screen. See The system API for how calls run.

Connections

connections

connections(): { name: string; engine: string; state: string }[]

The database connections the gateway has, with their engine and current state. Engineer role; error Listing databases needs the engineer role, and this script runs as operator.

Choosing a connection

query, scalar and update take an optional database argument naming a connection. Names match without regard to case. When it is left out, the first enabled connection is used (the first one at all, if none is enabled, which then fails as disabled). Errors:

• the gateway has no database connections; add one under Connections > Databases.
• there is no database connection named 'Reporting'.
• the database connection 'Reporting' is disabled.

Statements

Each statement opens the connection, runs with a 15 s command timeout, and counts as one query in the connection's statistics. A database error is reported in the connection's own words.

Parameters

Use placeholders rather than building the SQL text, so a value can never change the statement:

type ScadexDbParams =
  | ReadonlyArray<string | number | boolean | Date | null>            // for ? in order
  | Readonly<Record<string, string | number | boolean | Date | null>>; // for :name by name

• ? placeholders take values from an array, in order. Giving more or fewer values than there are ? is an error (the statement has more ? than the 2 values given., 3 values were given for 2 ?.).
• :name placeholders take values from an object by name; a name used twice is bound once. A missing one is an error: no value was given for :line.
• Placeholders inside quotes (', ", `) and after -- comments are left alone, and :: (a Postgres cast) is not a placeholder.
• A Date is sent as its ISO text and bound as a UTC time, so a time column receives a time. Whole numbers are bound as integers, other numbers as doubles, null as SQL NULL. Any other kind of value (an object, an array) is an error: a value for a statement must be text, a number, a boolean, a time or null.

query

query(sql: string, params?: ScadexDbParams, database?: string): ScadexRow[]

Parameter	Type	Meaning
sql	string	A SELECT. Required
params	array or object	Values for the placeholders
database	string	A connection's name; the first enabled one when left out

The rows, each an object of column name to value (Record<string, string | number | boolean | null>). A result of more than 10,000 rows is an error: the query returned more than 10,000 rows; narrow it with WHERE or LIMIT. Column values arrive as plain values: decimals as numbers, dates and times as ISO 8601 text, binary as base64, anything else as text.

var rows = system.db.query(
  'SELECT line, SUM(count) AS total FROM production WHERE day = :day GROUP BY line',
  { day: system.date.format(system.date.now(), 'yyyy-MM-dd') },
  'Reporting'
);
rows.forEach(function (r) { writeTag('Reports.' + r.line + '.Today', r.total); });

scalar

scalar(sql: string, params?: ScadexDbParams, database?: string): string | number | boolean | null

The first column of the first row, or null when there is none.

var open = system.db.scalar('SELECT COUNT(*) FROM work_orders WHERE closed_at IS NULL', [], 'MES');

update

update(sql: string, params?: ScadexDbParams, database?: string): number

Runs an INSERT, UPDATE or DELETE and returns how many rows it changed.

system.db.update(
  'INSERT INTO events (at, tag, value, who) VALUES (?, ?, ?, ?)',
  [system.date.now(), 'Line1.Setpoint', val, system.user.current().name]
);

Dry runs and the console

In a dry run (script console with Dry run on, or a cron job's Try it) every system.db statement runs inside a transaction that is rolled back when the call ends:

• query and scalar return their rows as usual.
• update runs, returns the number of rows it would have changed, and reports <connection>: <statement> (would change N rows) under Would change; the database is left as it was.

Since each call is its own transaction, an update followed by a query in the same dry run does not see the update's rows. In a real run there is no transaction around a call: each statement commits on its own.