Database Query

The Database Query tool runs SQL on a Database Connection and returns the result. It is used from page scripts with the PageDatabaseQuery function, through the runtime API (POST API/DatabaseQuery) and through MCP. The database types of the Database Connections page can be queried: SQL Server, MySQL, SQLite and PostgreSQL, including TimescaleDB, and InfluxDB 3, which is read only; see the section InfluxDB 3. An InfluxDB 2.x connection serves Data Forwarders.

A query names the Database Connection. The connection string and its credentials stay in the configuration and are never part of a page script.

Database Access

A Database Connection is available to the Database Query tool when Allow Database Query is set on it. Set it in the Database Query Access section of the Add or Edit Database Connection dialog, or in the Allow Database Query column of the Database Connections page.

• Access Right limits Database Query on the connection to the selected user groups. When Access Right is empty, every logged-in user can query the connection. Members of Administrators always have access.
• Guest users cannot use the Database Query tool.
• A user with access can run any SQL statement on the connection, not only the statements in the page scripts. Use a database login with only the rights the queries need, for example a login with read access.
• A SQLite connection to Configuration.db, Historian.db or StandbyHistorian.db is limited to read access. The Users table and stored credentials are not returned. Local.db cannot be queried.

Running a Query from a Page Script

Use the PageDatabaseQuery function to run a query and return the result to the page script. The function is asynchronous and is awaited. The Database Query Access section of the connection dialog shows the call with the name of the connection.

var rows = await PageDatabaseQuery("ProductionSQL", "SELECT TOP 1 BatchId, Status FROM dbo.Batches ORDER BY StartTime DESC");

if (rows.length > 0) {
    var columns = rows[0].split("|");
    PageWriteVariable("LastBatchStatus", columns[1]);
}

The result is a list of rows. Each row is one text with the column values separated by |. A statement that returns no rows returns the number of affected rows. A refused or failed query returns one message text.

Runtime API and MCP

The runtime API endpoint POST API/DatabaseQuery and the database_query MCP tool take the same two values in the request body. The same access rules apply.

{
  "Connection": "ProductionSQL",
  "SqlQuery": "SELECT TOP 10 BatchId, Status FROM dbo.Batches"
}

InfluxDB 3

An InfluxDB 3 connection, a connection whose Connection String has the key Database, is queried in InfluxDB SQL. Each measurement is a table with the time in the column time, in UTC, and its tags and fields as columns. The query below lists the measurements of the database. Allow Database Query is available for an InfluxDB 3 connection; for an InfluxDB 2.x connection the Database Query Access section is hidden.

SELECT table_name FROM information_schema.tables WHERE table_schema = 'iox'

The rows are returned as on the other databases: the column values separated by |, with an empty value for a field that the row does not hold. The queries read only: InfluxDB 3 SQL has no INSERT, UPDATE or DELETE, and the Token of the connection decides which data can be read.

var rows = await PageDatabaseQuery("PlantInflux", "SELECT time, variable, value FROM historian ORDER BY time DESC LIMIT 10");

Converting Page Scripts from Connection Strings

Earlier versions took a database type and a connection string: PageDatabaseQuery(database, connstring, sqlquery). The current version takes the name of a Database Connection, and a call with a connection string returns a message that names the new form. The runtime API and MCP request body changed the same way. The Database Query Access section of the Parameters page (Allow all databases, Allowed SQLite databases, Allowed SQL Servers) is replaced by Allow Database Query and Access Right on the Database Connection.

To convert a page script:

• Create a Database Connection under Configuration > Database Connections with the database type and connection string of the old call. A relative SQLite path resolves against the EnvisionSCADA installation folder.
• Set Allow Database Query on the connection and, when only some user groups may query it, set Access Right.
• Replace the first two arguments of the call with the name of the connection.

var rows = await PageDatabaseQuery("SQL Server", "Server=192.168.10.5;Database=Batches;User Id=svc;Password=***;", "SELECT TOP 10 BatchId, Status FROM dbo.Batches");

var rows = await PageDatabaseQuery("Batches", "SELECT TOP 10 BatchId, Status FROM dbo.Batches");

Showing query results on a page

To show rows of a database on a page, use the Data Grid object: it reads one table of a Database Connection with sorting, filters, search and paging, and can edit the rows. The Data Grid does not run free-text SQL and does not need Allow Database Query; see the Data Grid design article and the Database Connections configuration article.

Notes

Queries on the internal system databases are limited to read access. Use the dedicated configuration pages to change system data.

Renaming a Database Connection does not change the name in page scripts. Update the PageDatabaseQuery calls after a rename.