LogoLogo
Contact Uscybus.io
Connectware 1.9.0
Connectware 1.9.0
  • Getting Started
    • Introduction
    • Installing Connectware
      • System Requirements
      • Acquiring your License Key
      • Installing Connectware on Docker
      • Installing Connectware on Kubernetes
    • Connectware Admin UI
    • Basic Components of Connectware
    • Connecting your First Machine
      • Your First Service Commissioning File
  • Documentation
    • Services
      • Service Commissioning Files
        • Structure of Service Commissioning Files
          • description
          • metadata
          • parameters
          • definitions
          • resources
            • Cybus::Connection
            • Cybus::Endpoint
            • Cybus:Mapping
            • Cybus::Container
              • Docker problem with network changes
            • Cybus::Link
            • Cybus::IngressRoute
            • Cybus::User
            • Cybus::Role
            • Cybus::Volume
            • Cybus::File
            • Cybus::Server
            • Cybus::Node
        • Sample Service Commissioning Files
          • Modbus
            • “Bearbeitungszentrum BAZ” - Single File
            • “Bearbeitungszentrum BAZ” - Multiple Files
            • “Bearbeitungszentrum BAZ” - Single File and Custom Topics
            • “Bearbeitungszentrum BAZ” - Agent Mode
          • Machine Condition Monitoring : OPC UA + InfluxDB + Grafana Dashboard
            • “Machine Condition Monitoring Example” - Single File
          • Machine Utilization Example (Multi file service composition) : Modbus TCP + InfluxDB + Grafana + MSS
            • “Machine Utilization Example” - Machine Connectivity
            • “Machine Utilization Example” - Dashboards with role based access permission
            • “Machine Utilization Example” - Push data to MSSQL Database
      • Services View
      • Setting Up and Configuring Services
        • Installing Services
        • Enabling Services
        • Updating Services
        • Disabling Services
        • Deleting Services
      • Service Details View
      • FlowSync
        • Example 1 - Node with Transaction Mode (HTTP)
        • Example 2 - Node Responds (HTTP)
        • Example 3 - Node with Error (HTTP)
        • Example 4 - Node with Timeout Error Code and Error Message (HTTP)
        • Example 5 - Full Transactional Data Flow (HTTP)
        • Example 6 - Full Transactional Data Flow (OPC UA)
      • ServiceID
      • Inter-Service Referencing
      • Deviation
      • Service Logs
        • Logs of Individual Services
        • Logs of All Services
      • Rule Engine
        • Data Processing Rules
        • Rule Sandbox
      • Shared Subscriptions
        • Setting Up Shared Subscriptions
      • API Definition
    • Resources
      • Servers
      • Containers
      • Volumes
      • Connections
      • Endpoints
      • Mappings
      • Nodes
      • API Definition
    • User Management
      • Users and Roles View
      • Users
      • Roles
      • Permissions
      • Password Policy Rules
      • Default Admin User
      • MQTT Users
      • Adding a MQTT Publish Prefix for Users
      • Multi-Factor Authentication
      • Long lived JSON Web Tokens
      • Access Permissions for Admin-UI
        • UI Access
        • Minimum Access Role Pages
      • API Definition
    • Client Registry
      • Implicit Flow
      • Explicit Flow
      • Granting Access
      • API Definition
    • Certificates
    • Monitoring
      • Data Explorer
      • Live Data
    • Workbench
      • Flows in Git Repositories
    • System Status
      • Info
      • Metrics
      • Status
      • Retrieving More System Information
      • System Health
      • API Definition
    • Backup and Restore
      • Volumes
      • User Database
    • Configuration
      • Environment Variables
      • LDAP Configuration
      • MFA Configuration
    • Agents
      • Agents View
      • Installing Agents
        • Installing Agents via Docker
        • Installing Agents via Docker Compose
        • Installing Agents via Kubernetes
        • Using Mutual TLS for Agents
      • Registering Agents in Connectware
      • Using Agents
      • Monitoring Agents
      • Troubleshooting Agents
    • Industry Protocol Details
      • ADS
        • AdsConnection
        • AdsEndpoint
      • BACnet
        • BacnetConnection
        • BacnetEndpoint
      • EtherNet/IP
        • EthernetIpConnection
        • EthernetIpEndpoint
      • Focas
        • FocasConnection
        • FocasEndpoint
      • Generic VRPC
        • GenericVrpcConnection
        • GenericVrpcEndpoint
      • Hottinger Baldwin Messtechnik (HBM)
        • HbmdaqConnection
        • HbmdaqEndpoint
      • Heidenhain DNC
        • HeidenhainConnection
        • HeidenhainEndpoint
      • HTTP/REST
        • HttpConnection
        • HttpEndpoint
      • HTTP Server
        • HttpServer
        • HttpNode
      • InfluxDB
        • InfluxdbConnection
        • InfluxdbEndpoint
      • Kafka
        • KafkaConnection
        • KafkaEndpoint
      • Modbus/TCP
        • ModbusConnection
        • ModbusEndpoint
      • MQTT
        • MqttConnection
        • MqttEndpoint
      • MSSQL
        • MssqlConnection
        • MssqlEndpoint
      • OPC DA
        • OpcdaConnection
        • OpcdaEndpoint
      • OPC UA
        • OPC UA Client
          • OpcuaConnection
          • OpcuaEndpoint
        • OPC UA Server
          • OpcuaServer
          • OpcuaNode
        • OPC UA Object Types
        • OPC UA Server References
          • OpcuaReferenceNode
          • OpcuaObjectNode
      • Siemens SIMATIC S7
        • S7Connection
        • S7Endpoint
      • Shdr
        • ShdrConnection
        • ShdrEndpoint
      • Sinumerik
        • SinumerikConnection
        • SinumerikEndpoint
      • Sopas
        • SopasConnection
        • SopasEndpoint
      • SQL
        • SqlConnection
        • SqlEndpoint
      • Werma WIN Ethernet
        • WermaConnection
        • WermaEndpoint
      • Systemstate
        • SystemstateConnection
        • SystemstateEndpoint
      • API Definition
    • Connectware Licensing
    • Changelog
      • General changes from 0.x to 1.0
        • Upgrading from 0.x to 1.0
    • Upgrade Guide
      • Upgrading from 1.x to 1.7.0
      • Upgrading from 1.x to 1.5.0
Powered by GitBook
LogoLogo

Cybus

  • Terms and Condition
  • Imprint
  • Data Privacy

© Copyright 2025, Cybus GmbH

On this page
  • Usage
  • Connection
  • Reading Data
  • Output Format on Read
  • Writing Data
  • Output Format on Write
  • Service Commissioning File Example
  • Footnotes

Was this helpful?

  1. Documentation
  2. Industry Protocol Details

MSSQL

PreviousMqttEndpointNextMssqlConnection

Last updated 5 months ago

Was this helpful?

MSSQL is the protocol used to connect to Microsoft’s SQL Server.

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications, which may run either on the same computer or on another computer across a network.

The Connectware MSSQL protocol enables to read from or write to a database on the database server.

Usage

In general, the MSSQL implementation works by specifying a query or query template.

The following examples use a sample table called people that looks like this:

id
name
lastname
gender

1

Alice

Miller

female

2

Bob

Jones

male

Connection

To connect to a MS SQL Server, at least the usual server connection properties are needed when specifying the Cybus::Connection resource:

  • Host name

  • Port

  • User name

  • Password

  • Database to be used in this connection

It is recommended to define these properties as parameters in the commissioning file and reference them in the connection settings using !ref, so that the actual value can be edited at deployment time. See below.

Reading Data

To read data from an MSSQL database, an endpoint has to be defined with either read or subscribe properties, including the definition of the intended SQL query. Subscribe works by defining a polling interval, hence the query will be executed on a regular basis. Read is executed each time a MQTT message is sent to the respective endpoint topic with the /req (request) suffix, where the result is sent to the endpoint topic with the /res (result) suffix. The result of the query is provided in JSON format on the MQTT broker.

Example endpoint definition:

mssqlQuery1:
    type: Cybus::Endpoint
    properties:
        protocol: Mssql
        connection: !ref mssqlConnection
        subscribe:
            query: 'SELECT * FROM people'
            interval: 2000

This endpoint will execute the given query and return the data as MQTT messages like in the following example. If no rows are returned, you will receive an empty array ([]) as a value.

{
  "timestamp": 1231782312, // unix timestamp in ms
  "value": [
    {
      "id": 1,
      "name": "Alice",
      "lastname": "Miller"
      "gender": "female"
    },
    {
      "id": 2,
      "name": "Bob",
      "lastname": "Jones"
      "gender": "male"
    }
  ]
}

If the SQL query contains placeholder definitions, all their names must exist in the message payload, otherwise an error will be logged and the message will be ignored. The value of the placeholders must have the right data format matching the target schema of the database.

sqlQuery1:
    type: Cybus::Endpoint
    properties:
        protocol: Sql
        connection: !ref sqlConnection
        read:
            query: 'SELECT * FROM people WHERE lastname = $lastname'

Sending a message to the /req topic of this Endpoint with the following payload:

{ 'lastname': 'Miller' }

will return results filtered based on the where clause configured:

{
  "timestamp": 1231792312, // unix timestamp in ms
  "value": [
    {
      "id": 1,
      "name": "Alice",
      "lastname": "Miller"
      "gender": "female"
    }
  ]
}

Output Format on Read

When data is read from SQL results are published to the /res topic of the Endpoint. The output message is an object with two properties:

  • timestamp: is the unix timestamp, in milliseconds, of when the read was executed

  • value: is an array of results as returned by the SQL query

Writing Data

To write data to the database, an endpoint with write properties has to be defined. The endpoint definition includes the definition of the SQL query.

All specified placeholders must exist in the message payload, otherwise an error will be logged and the message will be ignored. The value of the placeholders must have the right data format matching the target schema of the database.

Example endpoint definition:

mssqlQuery1:
    type: Cybus::Endpoint
    properties:
        protocol: Mssql
        connection: !ref mssqlConnection
        write:
            query: 'INSERT INTO people (name, lastname, gender) VALUES (@name, @lastname, @gender)'

When using bulk insert you need to specify the endpoint like this:

sqlQuery2:
    type: Cybus::Endpoint
    properties:
        protocol: Sql
        connection: !ref sqlConnection
        write:
            query: 'INSERT INTO people (name, lastname, gender) VALUES'
            queryValues: '(@name, @lastname, @gender)'

To write data, you must send a MQTT message like the following to the /set topic of the Endpoint:

{ 'name': 'Alice', 'lastname': 'Miller', 'gender': 'female' }

Alternatively, you can also send multiple rows into a single message for performance reasons like this:

Important

When using this method of insertion make sure all rows have the same amount of columns

You will also need to specify the parameter queryValues in the endpoint definition.

[{ 'name': 'Alice', 'lastname': 'Miller', 'gender': 'female' }, { 'name': 'John', 'lastname': 'Clark', 'gender': 'male' }]

The MSSQL connection on the Connectware side does not perform any data validation against the database schema. The senders of the MQTT messages themselves must ensure to send the data in the correct format.

Output Format on Write

When data is written to an MSSQL Endpoint a message is published to the /res topic of the Endpoint. The output message is an object with two properties:

  • timestamp: is the unix timestamp, in milliseconds, of when the write was executed

  • value: is set to true when the write was successful

Service Commissioning File Example

mssql-example.yml
---
description: >
    Sample MSSQL service commissioning file

metadata:
    name: Sample MSSQL service
    icon: https://www.cybus.io/wp-content/uploads/2017/10/for-whom1.svg
    provider: cybus
    homepage: https://www.cybus.io
    version: 1.0.0

parameters:
    mssqlHost:
        type: string
        default: 127.0.0.1

    mssqlPort:
        type: integer
        default: 1433

    mssqlUser:
        type: string
        default: sa

    mssqlPassword:
        type: string
        default: SomeLongSecurePassw0rd

    mssqlDatabase:
        type: string
        default: tempdb

    initialReconnectDelay:
        type: integer
        default: 1000

    maxReconnectDelay:
        type: integer
        default: 30000

    factorReconnectDelay:
        type: integer
        default: 2

resources:
    mssqlConnection:
        type: Cybus::Connection
        properties:
            protocol: Mssql
            connection:
                host: !ref mssqlHost
                port: !ref mssqlPort
                username: !ref mssqlUser
                password: !ref mssqlPassword
                database: !ref mssqlDatabase
                connectionStrategy:
                    initialDelay: !ref initialReconnectDelay
                    maxDelay: !ref maxReconnectDelay
                    incrementFactor: !ref factorReconnectDelay

    mssqlQuery1:
        type: Cybus::Endpoint
        properties:
            protocol: Mssql
            connection: !ref mssqlConnection
            subscribe:
                query: 'SELECT name FROM sys.databases'
                # Be very careful with this setting. A low value might overload
                # the database (the unit is milliseconds!).
                interval: 2000

    mssqlQuery2:
        type: Cybus::Endpoint
        properties:
            protocol: Mssql
            connection: !ref mssqlConnection
            write:
                # Here we use the placeholder @someValue. The protocol driver will insert
                # the value from the input JSON message under the key someValue
                query: 'INSERT INTO test (some_column) VALUES (@someValue)'

    mapping:
        type: Cybus::Mapping
        properties:
            mappings:
                - subscribe:
                      endpoint: !ref mssqlQuery1
                  publish:
                      topic: 'system-tables'
                - subscribe:
                      topic: 'insert-test'
                  publish:
                      endpoint: !ref mssqlQuery2

Footnotes

[1]

The regular expression for the identifiers of a placeholder is @[a-zA-Z0-9_]+, i.e. one single at-character @ followed by an identifier consisting of ascii characters, numbers, and the underscore. If there is more than one at-character in a row, such as in @@VERSION, the at-character sequence will not be interpreted as the beginning of a placeholder but instead it will be passed on unchanged. If one single at-character is followed by any other non-matching character, such as a whitespace or some punctuation, the at-character sequence will also not be interpreted as the beginning of a placeholder and it will be passed on unchanged. (If you need one single @ that matches the above pattern in the resulting SQL query, currently this requires defining an extra placeholder and sending the desired string as a value for the replacement in the message payload.)

More details about the connection properties are described below:

The SQL query definition can be defined as a template string containing placeholders. In the template, the at-character @ followed by an identifier is used to denote such placeholders . The placeholders will be replaced by the values from the payload of the JSON message received via MQTT. If no placeholders are defined, the query will simple be executed as-is.

In the SQL query definition, the query syntax is used as a template string containing placeholders. In the template, the at-character @ followed by an identifier is used to denote such placeholders . The placeholders will be replaced by the values from the payload of the JSON message received via MQTT.

Connection Properties
Connection Properties
Endpoint Properties
Service Commissioning File Example
[1]
[1]
2KB
mssql-example.yml