mirror of
https://gitee.com/pnoker/iot-dc3.git
synced 2026-11-01 02:31:46 +08:00
231 lines
17 KiB
Markdown
231 lines
17 KiB
Markdown
---
|
||
title: SQL Server Driver
|
||
---
|
||
|
||
# SQL Server Driver
|
||
|
||
`dc3-driver-sqlserver` onboards a Microsoft SQL Server database into IoT DC3 as a data source: acting as a database
|
||
client, it runs a `SELECT` on each polling cycle and uses the queried value as the reading, and supports writing values
|
||
into the database via the `UPDATE`/`INSERT` write query configured on the point. After reading this you can set the
|
||
connection parameters (including encryption options) on a [Device](../introduction/concepts/device), the read/write SQL
|
||
on each [Point](../introduction/concepts/point), and pinpoint common "can't connect / TLS handshake fails / no value /
|
||
write fails" problems.
|
||
|
||
> You are here: a driver that onboards an existing database as a data source. Not all data comes from a fieldbus
|
||
> device—much business data, historical data, and third-party results simply live in a SQL Server table.
|
||
|
||
## Protocol background
|
||
|
||
SQL Server is Microsoft's enterprise relational database, shipping since 1989, using T-SQL as its query language and
|
||
organizing data into tables/rows/columns; it is heavily used in Windows and enterprise IT environments. In IoT scenarios
|
||
it is often not a "field device" but a hub where data converges: MES/ERP, SCADA front-ends, third-party platforms, and
|
||
historical archives all tend to drop their results into a SQL Server table for downstream consumption. Onboard such a
|
||
table as a data source, and the platform can poll its columns into [PointValues](../introduction/concepts/point-value)
|
||
just like it polls a real device.
|
||
|
||
Seen through the four-layer IoT reference architecture, a database driver talks to the database over TDS on TCP/IP (
|
||
default port `1433`); it is the entry point for moving external data into the platform, and its transport sits at the *
|
||
*network layer**—see the [IoT network-layer chapter](../foundations/data-pipeline) for how data enters the platform
|
||
pipeline. This driver acts as a database client ([Driver](../introduction/concepts/driver) type `DRIVER_CLIENT`),
|
||
connecting to a SQL Server instance over JDBC (driver class `com.microsoft.sqlserver.jdbc.SQLServerDriver`) and
|
||
reading/writing values by the SQL configured on each [Point](../introduction/concepts/point). Its communication model is
|
||
classic **request-response**—the driver, as a client, actively issues queries; the database never pushes, so collection
|
||
is driven by cron polling. The shared logic for JDBC connections, connection pooling, and SQL execution lives in the
|
||
abstract base class `AbstractJdbcDriverCustomService` (`dc3-common-sql` module), reused by all four database drivers (
|
||
MySQL, PostgreSQL, Oracle, SQL Server), each of which only supplies JDBC URL construction and the driver class name.
|
||
|
||
Two driver-specific concepts that the configuration tables below rely on:
|
||
|
||
```mermaid
|
||
flowchart LR
|
||
Drv["dc3-driver-sqlserver<br/>(JDBC client)"] -->|"readQuery (SELECT)"| DB[("SQL Server instance<br/>business / history tables")]
|
||
Drv -.->|"writeQuery (UPDATE/INSERT, ? placeholder)"| DB
|
||
DB -->|"first row, first column"| PV["PointValue"]
|
||
```
|
||
|
||
- **Read Query**: a `SELECT` configured on the point; the driver runs it on each polling cycle and takes the **first
|
||
column of the first row** of the result as the point's value.
|
||
- **Write Query**: an `UPDATE`/`INSERT` configured on the point, using a single `?` placeholder for the value to
|
||
write—when a write command fires, the command parameter is bound via prepared-statement parameter binding.
|
||
|
||
## Attribute configuration
|
||
|
||
Onboarding a SQL Server database requires filling in [attributes](../introduction/concepts/attribute-config) at three
|
||
levels: device-level connection parameters (`driver-attribute`), each polled point's read/write SQL (`point-attribute`),
|
||
and one reserved attribute on the write command (`command-attribute`). The attributes, types, and defaults below are
|
||
taken from the driver's `application.yml` (`dc3-driver-sqlserver` module).
|
||
|
||
### Driver attributes (device-level `driver-attribute`)
|
||
|
||
Driver attributes answer "which database to connect to, which account to use, the query timeout, and whether the
|
||
connection is encrypted". Fill in one set per SQL Server database on the [Device](../introduction/concepts/device):
|
||
|
||
| Attribute | code | Type | Default | Remark |
|
||
|--------------------------|--------------------------|--------|-------------|-----------------------------------------------------------------|
|
||
| Host | `host` | STRING | `localhost` | SQL Server host IP or hostname |
|
||
| Port | `port` | INT | `1433` | SQL Server port (standard 1433) |
|
||
| Database | `database` | STRING | (empty) | SQL Server database name |
|
||
| Username | `username` | STRING | `root` | SQL Server username |
|
||
| Password | `password` | STRING | (empty) | SQL Server password |
|
||
| Query Timeout | `queryTimeout` | INT | `30` | SQL query timeout in seconds |
|
||
| Encrypt | `encrypt` | STRING | `false` | Whether to encrypt the connection (TLS) |
|
||
| Trust Server Certificate | `trustServerCertificate` | STRING | `true` | Whether to trust the server certificate (skip chain validation) |
|
||
|
||
The driver builds the JDBC URL from these attributes, in the form
|
||
`jdbc:sqlserver://host:port;databaseName=...;encrypt=...;trustServerCertificate=...;` (semicolon-delimited, unlike
|
||
MySQL's `?key=value` form). All five of `host`, `port`, `database`, `username`, and `password` are
|
||
required—configuration validation (`validate()`) checks each one, and any missing field fails. The driver caches one
|
||
HikariCP connection pool per device ID (one pool per device, max 5 connections), with the connection timeout set to
|
||
`queryTimeout × 1000` milliseconds.
|
||
|
||
::: tip queryTimeout applies to both connecting and the query pace
|
||
`queryTimeout` (default 30 seconds) is used as the pool's `connectionTimeout`: failing to acquire a connection, or a
|
||
connection that stalls beyond this duration, fails. It is separate from the polling interval—if a slow SQL consistently
|
||
approaches or exceeds it, optimize the SQL or add an index rather than just raising the timeout.
|
||
:::
|
||
|
||
::: warning encrypt and trustServerCertificate are STRING and must be set together
|
||
Both attributes are STRING type—fill the string `"true"`/`"false"`, not a boolean; they are spliced verbatim into the
|
||
JDBC URL. The SQL Server JDBC driver performs a TLS handshake and validates the server certificate when `encrypt=true`;
|
||
if the server uses a self-signed certificate, validation fails and the connection errors out. When enabling encryption
|
||
against a self-signed instance, you must also set `trustServerCertificate=true` to skip certificate-chain validation.
|
||
For plaintext testing on a trusted network, just keep the default `encrypt=false`.
|
||
:::
|
||
|
||
### Point attributes (`point-attribute`)
|
||
|
||
Point attributes answer "which value to query from this database, and where to write". Fill in the read/write SQL on
|
||
each polled [Point](../introduction/concepts/point):
|
||
|
||
| Attribute | code | Type | Default | Remark |
|
||
|-------------|--------------|--------|---------|-----------------------------------------------------------------------------------------------|
|
||
| Read Query | `readQuery` | STRING | (empty) | `SELECT` query for reading the point value |
|
||
| Write Query | `writeQuery` | STRING | (empty) | `UPDATE`/`INSERT` using a single `?` placeholder for the written value (bound as a parameter) |
|
||
|
||
::: tip Read Query takes the first column of the first row
|
||
`readQuery` is a plain `SELECT`, and the driver takes the **first column of the first row** of its result (
|
||
`rs.getObject(1)`) as the point's value—so a single-row, single-column query like
|
||
`SELECT temperature FROM sensor WHERE id = 1` is the safest form. An empty result set yields `null`. The point's data
|
||
type ([Point](../introduction/concepts/point) `pointTypeFlag`) decides how that value is parsed. `readQuery` is required
|
||
on a point (enforced by `validatePoint()`); without it, point validation fails. `writeQuery` is required only when that
|
||
point is to be written.
|
||
:::
|
||
|
||
### Write command attributes (`command-attribute`)
|
||
|
||
This attribute can be configured on the write command, but is not consumed by the implementation:
|
||
|
||
| Attribute | code | Type | Default | Remark |
|
||
|---------------|----------------|--------|---------|--------------------------------------|
|
||
| Execute Query | `executeQuery` | STRING | (empty) | SQL query to execute for the command |
|
||
|
||
::: warning executeQuery is currently not consumed by the implementation
|
||
Writing a value goes through the **point's `writeQuery`**: `write()` reads the `point-attribute` `writeQuery`, binds the
|
||
command parameter with `setString(1, value)` into the single `?` placeholder, and executes the `UPDATE`/`INSERT`. The
|
||
`command-attribute` `executeQuery` is kept only as a configuration item—nothing in the current driver code reads or
|
||
executes it. There is no separate "run a SQL statement directly by command" path; writing always goes through
|
||
`writeQuery`. Code is the source of truth.
|
||
:::
|
||
|
||
## Troubleshooting
|
||
|
||
SQL Server onboarding failures mostly cluster around connection, TLS handshake, account permissions, query targeting,
|
||
and field types. Work through them in order:
|
||
|
||
1. **Can't connect (device stays offline)**. First confirm `host:port` is reachable: `telnet <host> 1433` or
|
||
`nc -vz <host> 1433`. The health check decides online via `conn.isValid(5)` (whether a valid connection can be
|
||
obtained within 5 seconds); a failed connect or heartbeat reports offline. Common root causes: SQL Server's TCP/IP
|
||
protocol not enabled, the instance listening only on named pipes, a firewall blocking 1433, or a dynamic port not
|
||
pinned to 1433.
|
||
|
||
2. **Encryption / certificate handshake fails**. When `encrypt=true`, the driver performs a TLS handshake and validates
|
||
the server certificate; against a self-signed instance without `trustServerCertificate=true`, the connect phase
|
||
reports certificate-chain validation failure. Either set `trustServerCertificate=true` to skip validation, or install
|
||
a certificate issued by a trusted CA on the server. For plaintext testing on a trusted network, keep `encrypt=false`.
|
||
Note both are filled as the strings `"true"`/`"false"`.
|
||
|
||
3. **Connects but is rejected (account / permission)**. Confirm `username`/`password` are correct and the account has
|
||
read (or write) permission on the target tables. SQL Server supports both SQL authentication and Windows
|
||
authentication; this driver uses SQL authentication with `username`/`password`—if the instance only allows Windows (
|
||
integrated) authentication, the SQL account is rejected. A failed connect throws `ConnectorException` and invalidates
|
||
that device's pool, which is rebuilt on the next cycle.
|
||
|
||
4. **No value / wrong row returned**. The driver only takes the first column of the first row, so `readQuery` must
|
||
reliably pinpoint the target row. An empty result set yields `null`; when multiple rows return, only the first is
|
||
used and may not be the row you meant. Write the `WHERE` primary-key condition fully so you don't pick the wrong row
|
||
as the table grows.
|
||
|
||
5. **Value / type mismatch**. The point's `pointTypeFlag` decides how the returned string is parsed. Configuring a text
|
||
column as a `FLOAT` point, or treating a `datetime`/`bit` column as numeric, can fail parsing. Use `CAST`/`CONVERT`
|
||
in `readQuery`, or select only the target numeric column, so the returned value matches the point's type.
|
||
|
||
6. **Write command returns failure**. Writing requires exactly one `?` placeholder in `writeQuery` and a statement
|
||
targeting a writable table and row. `write()` treats "affected rows > 0" as success—if the `WHERE` condition matches
|
||
no rows, `executeUpdate()` returns 0 and the write is judged failed. Run the same `UPDATE` by hand in the database
|
||
first to confirm it hits a row. A failed write throws `WritePointException` and invalidates the pool.
|
||
|
||
::: warning Write Query uses a `?` placeholder, not `${value}`
|
||
When writing, `writeQuery` uses a **single** `?` placeholder for the value (e.g.
|
||
`UPDATE sensor SET temperature = ? WHERE id = 1`), bound by the driver via `PreparedStatement.setString(1, value)`—this
|
||
is prepared-statement parameter binding, not string concatenation, so a malicious value cannot alter the statement
|
||
structure (no SQL injection). Do not concatenate the value into the SQL by hand, and do not use template syntax like
|
||
`${value}`: it would neither be substituted nor give you injection protection.
|
||
:::
|
||
|
||
## How it lands in IoT DC3
|
||
|
||
- **`dc3.driver.code`**: `SqlserverDriver` (type `DRIVER_CLIENT`, actively connects to the database and issues queries).
|
||
This is a stable routing identifier—do not change it casually.
|
||
- **Read capability**: ✓ implemented. `read()` executes the point's `readQuery` and takes the first column of the first
|
||
row as the point value.
|
||
- **Write capability**: ✓ implemented. `write()` executes the point's `writeQuery`, binding the written value as a `?`
|
||
prepared-statement parameter; affected rows > 0 means success.
|
||
- **Subscribe/report**: — not supported. SQL Server is request-response; the driver only actively queries/writes and
|
||
never passively receives pushes. This matches the `✓ / ✓ / —` for SQL Server in
|
||
the [driver capability matrix](./matrix).
|
||
- **Polling interval**: default cron `0/30 * * * * ?` (read once every 30 seconds), configured under `schedule.read` in
|
||
the driver's `application.yml`; there is also a `custom` schedule with default cron `0/5 * * * * ?` (every 5 seconds),
|
||
but the base class `schedule()` is an empty implementation and database drivers do not use it.
|
||
- **Health/online**: device health check defaults to cron `0/15 * * * * ?` with a lease timeout of `45 seconds`; the
|
||
verdict relies on `conn.isValid(5)`. See [Device](../introduction/concepts/device) for the online-state mechanism.
|
||
|
||
::: info Implementation status: available
|
||
This driver is a **complete implementation** (not a skeleton). Reading, writing, the health check, the per-device cached
|
||
HikariCP pool, and pool invalidation-and-rebuild on failure are all in place, reusing the tested
|
||
`AbstractJdbcDriverCustomService` base class; the SQL Server subclass only customizes JDBC URL construction (including
|
||
`encrypt`/`trustServerCertificate`), the driver class name, and the default port. The only thing to note is that the
|
||
`command-attribute` `executeQuery` is reserved but not consumed by the code—writing always goes through the point's
|
||
`writeQuery` (see the warning above).
|
||
:::
|
||
|
||
### Minimal onboarding example
|
||
|
||
Onboard the `temperature` column of the `id=1` row in a `sensor` table as a temperature point:
|
||
|
||
1. Create a [Device](../introduction/concepts/device) with `SQL Server Driver`, and set the driver attributes
|
||
`host=192.168.1.10`, `port=1433`, `database=iot`, `username=sa`, `password=******` (keep the default `encrypt=false`
|
||
while testing over a trusted network).
|
||
2. Add a temperature [Point](../introduction/concepts/point) (`pointTypeFlag=FLOAT`, `READ_ONLY`) to
|
||
the [Profile](../introduction/concepts/profile) bound to the device, and set the point attribute
|
||
`readQuery=SELECT temperature FROM sensor WHERE id = 1`.
|
||
3. Start the driver, and within 30 seconds the queried temperature shows up in
|
||
the [PointValue](../introduction/concepts/point-value).
|
||
4. If the point should be writable, add `writeQuery=UPDATE sensor SET temperature = ? WHERE id = 1` to its point
|
||
attributes and configure a write [Command](../introduction/concepts/command) for it.
|
||
|
||
::: tip One driver instance can serve multiple databases
|
||
A single SQL Server driver process can serve multiple devices: each device connects to its own database per its driver
|
||
attributes and holds its own connection pool (cached by device ID). When device metadata is deleted/updated, the
|
||
corresponding pool is closed and rebuilt on demand.
|
||
:::
|
||
|
||
## Further reading
|
||
|
||
- [Drivers overview](./index) — entry point and categories for all drivers
|
||
- [Driver capability matrix](./matrix) — read/write/subscribe at a glance, including the SQL Server row
|
||
- [Device Onboarding](../operation/device-onboarding) — a complete onboarding walkthrough
|
||
- [Time-Series Data & Stream Processing](../foundations/data-pipeline) — how PointValues are stored, computed, and
|
||
queried after entering the platform
|
||
- [MySQL Driver](./mysql) — another database data source on the same JDBC base class, with the same configuration
|
||
structure
|