ODBC

The ODBC Connector periodically queries an ODBC-compatible database for new data and forwards the results to ThingsBoard as device attributes and telemetry. It can also call SQL stored procedures and functions in response to RPC commands from ThingsBoard.

To enable this connector, add it to the connectors list in tb_gateway.json — see the General Configuration reference.

The connector reads its settings from a JSON file. Below is a full example:

{
  "connection": {
    "str": "Driver={PostgreSQL};Server=localhost;Port=5432;Database=thingsboard;Uid=postgres;Pwd=postgres;",
    "attributes": {
      "autocommit": true,
      "timeout": 0
    },
    "encoding": "utf-8",
    "decoding": {
      "char": "utf-8",
      "wchar": "utf-8",
      "metadata": "utf-16le"
    },
    "reconnect": true,
    "reconnectPeriod": 60
  },
  "pyodbc": {
    "pooling": false
  },
  "polling": {
    "query": "SELECT bool_v, str_v, dbl_v, long_v, entity_id, ts FROM ts_kv WHERE ts > ? ORDER BY ts ASC LIMIT 10",
    "period": 5,
    "iterator": {
      "column": "ts",
      "query": "SELECT MIN(ts) - 1 FROM ts_kv",
      "save": false
    }
  },
  "mapping": {
    "device": {
      "type": "postgres",
      "name": "'ODBC ' + entity_id"
    },
    "sendDataOnlyOnChange": false,
    "attributes": "*",
    "timeseries": [
      "ts",
      {
        "name": "value",
        "value": "[i for i in [str_v, long_v, dbl_v, bool_v] if i is not None][0]"
      }
    ]
  },
  "serverSideRpc": {
    "enableUnknownRpc": true,
    "methods": [
      "procedureOne",
      {
        "name": "procedureTwo",
        "args": [ "One", 2, 3.0 ]
      }
    ]
  }
}

Before using the ODBC Connector, complete the following installation steps:

1. Install the ODBC runtime:

   • Windows — install the Visual C++ Redistributable package.
   • Linux — install the unixODBC package.

2. Install the ODBC driver for the specific database (PostgreSQL, MySQL, etc.) the gateway needs to connect to.

3. Register the driver:

   • Windows — add a data source in ODBC Data Source Administrator.
   • Linux/Unix — add the driver name, library path, and other details to the odbcinst.ini configuration file.

Required. Configures how to connect to the ODBC database and how to handle reconnections.

Optional. Tunes low-level connection behaviour via pyodbc connection attributes (for example, autocommit or timeout).

Optional. Controls how string data and metadata read from the database are decoded.

Optional. Exposes pyodbc module-level attributes that control the underlying Python ODBC library.

"pyodbc": {
  "pooling": false,
  "native_uuid": true
}

Optional. Specifies a custom uplink data converter class name. When omitted, the built-in converter is used.

"converter": "CustomOdbcUplinkConverter"

Required. Controls how often the database is queried, what SQL statement to run, and which column is used to advance through the result set between polling iterations.

Requirements for the query statement:

1. Must be a valid SQL SELECT statement compatible with the target database dialect.
2. Must include the device column in the SELECT list.
3. Must include the iterator column in the SELECT list.
4. Must include all attributes and/or timeseries columns in the SELECT list.
5. The WHERE clause must filter rows using the iterator column (the ? placeholder receives the current iterator value).
6. The ORDER BY clause must sort by the iterator column.
7. A LIMIT clause is recommended to bound memory usage per iteration.

Example — reads 10 rows per iteration, sorted by ts (the iterator column):

SELECT bool_v, str_v, dbl_v, long_v, entity_id, ts
FROM ts_kv
WHERE ts > ?
ORDER BY ts ASC
LIMIT 10

After each iteration the connector records the ts value of the last row and uses it as the ? placeholder in the next WHERE ts > ? call.

Required. Defines which column drives iteration, what its initial value is, and whether to persist the iterator position between gateway restarts.

How persistence works:

When save is true, the connector saves the iterator state to a file in config/odbc/ after each polling iteration. The file name is derived from a hash of:

• ODBC driver name
• Database server name
• Database name
• Iterator column name

On startup the connector looks for this file. If found, it restores the saved position; otherwise it uses value or query from the configuration file.

Required. Defines how columns from the query result set are mapped to ThingsBoard device names, attributes, and time-series values.

Required. Maps each result-set row to a unique ThingsBoard device name.

All columns listed in the SELECT clause are available by their column name inside the eval() expression. For example:

"device": {
  "name": "'ODBC ' + entity_id"
}

This concatenates the string ODBC with the value of the entity_id column to form the device name (e.g. ODBC 1a2b3c).

Both subsections support four configuration modes that can be mixed in a single array (combining mode).

Column name list — treat the listed column values directly as attribute/telemetry values:

"timeseries": [ "str_v", "ts" ]

Configuration list — rename columns and/or compute values with Python eval() expressions:

"timeseries": [
  {
    "name": "boolValue",
    "column": "bool_v"
  },
  {
    "nameExpression": "key_name",
    "value": "[i for i in [str_v, long_v, dbl_v, bool_v] if i is not None][0]"
  },
  {
    "name": "value",
    "value": "[i for i in [str_v, long_v, dbl_v, bool_v] if i is not None][0]"
  }
]

All SELECT columns are available by name in eval() expressions.

Combining mode — mix plain column names and configuration objects in the same array:

"timeseries": [
  "ts",
  {
    "name": "value",
    "value": "[i for i in [str_v, long_v, dbl_v, bool_v] if i is not None][0]"
  }
]

Globbing — treat every column in the result set as a telemetry or attribute key:

"timeseries": "*"

The connector can call SQL stored procedures or functions when an RPC command is received from ThingsBoard. Parameters are taken from the connector configuration or from the RPC request payload.

The methods list supports three configuration modes:

Name-only list — calls procedures by name with no static arguments:

"methods": [ "procedureOne", "procedureTwo" ]

Configuration list — each entry specifies the RPC method name, optional static arguments, and an optional custom SQL query:

"methods": [
  {
    "name": "rpcProcOne",
    "args": [ "One", 2, 3.0 ],
    "query": "CALL procedureOne(?,?,?)"
  },
  {
    "name": "functionOne",
    "args": [ false ]
  }
]

Combining mode — mix plain names and configuration objects:

"methods": [
  "procedureOne",
  {
    "name": "procedureTwo",
    "args": [ "One", 2, 3.0 ]
  }
]

Dynamic RPC parameters:

When enableUnknownRpc is true, the RPC payload must include all required procedure parameters. When overrideRpcConfig is true, the payload may include any subset of parameters to override those in the configuration file. Example RPC payload:

{
  "device": "ODBC Device 1",
  "data": {
    "method": "procedureOne",
    "params": {
      "args": [ "OverridedValue", 123, 3.14 ]
    }
  }
}