Stream SCADA Help

DataSets is a built-in feature of Stream Software that allows users to create and manage custom databases through REST API endpoints. It provides a complete set of operations — create tables, insert, query, update, delete, and execute advanced SQL queries — all through simple POST requests with JSON bodies.

Who it's for:

• System integrators building custom solutions on top of StreamSCADA
• Dashboard developers who need to store and retrieve application data
• 3rd-party applications that need structured data storage without managing their own database infrastructure
• Operations teams tracking equipment, work orders, inspections, spare parts, and other operational data

Key capabilities:

• Full CRUD operations on custom tables
• Three database engines: SQLite, Microsoft SQL Server, and PostgreSQL
• Advanced SQL queries with JOINs, aggregations, and subqueries via RawQuery
• Role-based access control per connection
• 3-layer data organization: Connections → Databases → Tables
• All operations via POST requests — simple to integrate from any platform

How it Works?

Stream DataSets organizes data in three layers:

Connection

  └── Database

        └── Table (DataSet)

• Connection — A named configuration pointing to a database engine. Configured by the administrator in the StreamSCADA project settings. Examples: "Local SQLite", "Plant SQL Server", "Corporate PostgreSQL".

• Database — A logical grouping of tables. For SQLite, each database is a separate .db file (e.g., Maintenance.db, Spares.db). For SQL Server and PostgreSQL, this maps to the database specified in the connection string.

• Table (DataSet) — A structured data table with defined columns. This is where data lives. Tables within the same database can be JOINed together using the RawQuery endpoint.

Request Flow

Every API call follows this sequence:

1. Client sends a POST request with a JSON body and a JWT token in the Authorization header
2. Authentication — the server validates the JWT token (handled globally, before any endpoint logic)
3. Authorization — the DataSets module checks if the user's roles are allowed for this operation on this connection (Read, Write, or Admin)
4. Connection resolution — the ConnectionName from the request is matched to a configured connection to determine which database engine and credentials to use
5. Execution — the operation is performed against the target database
6. Response — a JSON response is returned with Success, Message, and any result data

Discovery Flow

Clients can discover the full data structure using three endpoints in sequence:

1. List Connections — POST /api/DataSets/Connections — returns all available connections (e.g., "Local SQLite", "Plant SQL Server"). This is the starting point.

2. List Databases — POST /api/DataSets/Databases with the chosen ConnectionName — returns the databases within that connection (e.g., "Maintenance", "Spares"). For SQLite, these are the .db files. For SQL Server/PostgreSQL, this is the connected database.

3. List Tables — POST /api/DataSets/Tables with the chosen ConnectionName and DatabaseName — returns the tables within that database (e.g., "WorkOrders", "Equipment").

From there, the client can perform operations (Query, Insert, Update, Delete) on any discovered table.

Database Engines

All three engines support the same API — the client code is identical regardless of which engine is behind the connection. The only difference is the SQL dialect used in RawQuery (e.g., LIMIT for SQLite/PostgreSQL vs TOP for SQL Server).

Core Concepts

ConnectionName

Every API request requires a ConnectionName — the name of a configured database connection. Connections are set up by the administrator in the StreamSCADA project settings and define the database engine (SQLite, SQL Server, or PostgreSQL) and its connection details.

A default "Local SQLite" connection is created automatically for every new project.

DatabaseName

A logical grouping of tables within a connection. For SQLite, this corresponds to a .db file (e.g., DatabaseName: "Maintenance" uses Maintenance.db). For SQL Server and PostgreSQL, the database is determined by the connection string, so DatabaseName is not required.

Tables that need to be JOINed together must be in the same database.

DataSetName

The table name. This is where data is stored — rows and columns, like a spreadsheet or SQL table. Examples: "WorkOrders", "Equipment", "SpareParts".

ColumnDefinition

Used when creating a table. Each column has:

WhereClause

A SQL WHERE expression used in Query, Update, and Delete operations to filter which rows are affected. String values must be in single quotes.

Examples: "Id = 5", "Status = 'Open' AND Priority <= 2", "Name LIKE '%pump%'".

WhereClause is mandatory for Update and Delete to prevent accidental full-table modifications.