MSSQL¶
Overview¶
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
Example Commissioning File below.
More details about the connection properties are described below: Connection Properties
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"
}
]
}
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 1. 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.
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.
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 1. The placeholders will
be replaced by the values from the payload of the JSON message received via
MQTT.
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"
}
]
Important
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
Connection Properties¶
username
(string, required)¶
The username to connect to the MSSQL server
password
(string, required)¶
The password to connect to the MSSQL server
domain
(string)¶
The domain to use
database
(string, required)¶
The database to use
protocol
(string, enum)¶
The protocol version to use for the connection to the MSSQL server
This element must be one of the following enum values:
7_1
7_2
7_3_A
7_3_B
7_4
Default: "7_4"
connectTimeout
(integer)¶
The number of milliseconds before the attempt to connect is considered failed
Default: 15000
requestTimeout
(integer)¶
The number of milliseconds before a request is considered failed, or 0 for no timeout
Default: 15000
cancelTimeout
(integer)¶
The number of milliseconds before the cancel (abort) of a request is considered failed
Default: 5000
connectionStrategy
(object)¶
If a connection attempt fails, retries will be performed with increasing delay (waiting time) in between. The following parameters control how these delays behave.
Properties of the connectionStrategy
object:
initialDelay
(integer)¶
Delay (waiting time) of the first connection retry (in milliseconds). For subsequent retries, the delay will be increased according to the parameter incrementFactor which has a default value of 2.
Default: 1000
Additional restrictions:
Minimum:
1000
maxDelay
(integer)¶
Maximum delay (waiting time) to wait until the next retry (in milliseconds). The delay (waiting time) for any subsequent connection retry will not be larger than this value. Must be strictly greater than initialDelay.
Default: 30000
incrementFactor
(integer)¶
The factor used to increment initialDelay up to maxDelay. For example if initialDelay is set to 1000 and maxDelay to 5000 the values for the delay would be 1000, 2000, 4000, 5000.
Default: 2
Additional restrictions:
Minimum:
2
Endpoint Properties¶
query
(string, required)¶
The SQL query used to write or to subscribe
Example: "INSERT INTO test (some_column) VALUES (@some_column)"
interval
(integer)¶
The amount of milliseconds between queries
cronExpression
(string)¶
The Cron expression used to poll the endpoint. (For examples, see: https://github.com/node-cron/node-cron)
Examples: "1,2,4,5 * * * *"
, "1-5 * * * *"
, "*/2 * * * *"
,
"* * * January,September Sunday"
validateQuery
(boolean)¶
Deprecated: This property has no effect anymore, please remove it from your files (Previous description: Validate the syntax of the SQL query)
Example Commissioning File¶
Download: mssql-example.yml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | --- 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(1,2)
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.)