MsSQL Select Documentation

Version: 5.0.0

Retrieved: 2025-10-09 15:16:06

Microsoft SQL Server Select

This module retrieves rows from a Microsoft SQL Server database table and returns them as an array in a flow message

Settings

Name	Requirements	Purpose	Default
Target Property	Length: 0-64	The property to write the result into. If empty result is written to the root	data
Table Name	Length: 1-128	The name of the database table
Offset	Number	The number of database rows to skip
Limit	Number	The maximum number of database rows to retrieve, use 0 to retrieve all rows
Order by	Length: 1-128	The column to order by
Descending	Bool	Sort in descending or ascending order
Columns	List of strings	The columns to retrieve, leave empty to retrieve all columns
Filters	List of objects	A list of statements that are translated into a SQL WHERE statement, if many filters are used each row must match every one of them (AND)

Filters

Column	The column to get data from
Operator	The operator to use
Data	The data to compare to
Behaviour	The behavior to use: if Static the data field is used as is, if Property the data field is used to match a property on the incoming message

Credential

This module needs a credential of type 'Connection string' to connect to the database server. A minimal connection string is shown below. Depending on the setup of your database server additional parameters may be needed. Please consult the documentation for your server.

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;

NOTE: From version 2.0.0 the default value of the Encrypt parameter has changed from false to true, if you rely on it being set to false then you need to specify it

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;Encrypt=false;

To set a custom command timeout (in seconds) for SQL operations:

Server=tcp:192.168.0.5;Database=crosser;User ID=demo;Password=crosser123;Encrypt=false;Command Timeout=60

Input

A query will be sent to the database each time a message is received

Output

An output message will be sent each time the module receives a message. The result of the query will be added to the incoming message on the configured [Target Property]. If the target property exists on the incoming message it will be overwritten

Name	Type	Description
[Target Property]	Object array	Each row in the database is represented as an object in the array

Example

Prerequisite: A database with one table test, the table has two columns name and reading

#Settings

Target Property: data
Table Name: test
Order By: reading
Offset: 3
Limit: 2

# Output

{
  "data": [
        {
            "name": "sensor3",
            "reading": 129
        },
        {
            "name": "sensor4",
            "reading": 489
        }
  ]
 }

Search Documentation

Data Sources

Databases