---
title: Configuring a SQL collector
slug: how-tos/JGCe-configuring-a-sql-collector
createdAt: 2025-09-23T09:59:23.938Z
---

The **SQL collector** in Factry Historian allows you to connect to a SQL database and collect data as time series. Once configured, the data is historized and made available for further processing in Factry Historian, and for trending, reporting, and analysis.

The collector periodically executes a configured SQL query. The query must return specific columns (measurement\_key, value, ts, updated, quality, and optionally tag\_...) to ensure that the data can be historized correctly.

## Prerequisites

- Permissions in Factry Historian to *manage* collectors. See [Privileges](docId\:K2NnYEcUxqQsiuCLmpfKH).
- An installed SQL collector. If you have not done so already, first [install the collector](docId\:Iv4_SD56TdxcqKRKCj4kv).
- A working SQL connection. If you are running into issues or setting this up for the first time, follow our guide to [Testing an SQL connection](docId\:Y9wuZ3PblWcTKVEWfiRfM).
- A configured [time-series database](docId\:F8UdoT0apxmjws-t-GcPV) to write the collected data to.

## Configure the collector

After installing the collector, you should see that the collector is in an <font color="#46abdd">**initial state**</font> and is actively running. This means that the collector has established a connection to Factry Historian and is awaiting further configuration.

![](https://api.archbee.com/api/optimize/SMPo4IS9FkillUAMsur2c/GFCfLvxca7VX1LGjX3OJw_image.png)

To configure the collector, select it from the Collectors overview in Factry Historian and click the **Edit** button. Configure the following fields:

### ConnectionString

The **connection string** defines how the collector connects to your SQL database. It includes the database type, host, port, database name, and credentials.

Examples:

- **Postgres**: `postgres://$USER:$PASSWORD@host:5432/database`
- **Oracle**: `oracle://$USER:$PASSWORD@server/service_name`
- **SQL Server**: `sqlserver://$USER:$PASSWORD@host/instance?param=value`
- **MySQL/MariaDB**: `$USER:$PASSWORD@tcp(host:3306)/dbname?parseTime=true`
- **SQLite**: `file:test.db?cache=shared&mode=memory`
- **ODBC**: `DSN=my-odbc-datasource-name;UID=$USER;PWD=$PASSWORD`

For MariaDB, always add `parseTime=true` and handle NULL values with `COALESCE(...)` to prevent parsing issues.

### User and Password

Optional fields if not already embedded in the connection string. Provide credentials with appropriate database permissions.

### Driver

The SQL driver to use. Options:
` postgres | oracle | sqlserver | mysql | sqlite3 | hdb | ase | odbc`

### QueryInterval

How often (in seconds) the query should be executed.

- Default: 5
- Minimum: 1

### QueryTimeout

Maximum time (in seconds) before the query is aborted.

- Default: 5
- Minimum: 1

### QueryString

The actual SQL query that the collector executes.

It must return at least these columns:

- **measurement\_key**: maps each row to a measurement.
- **value**: the numeric or string value to historize.
- **ts**: the timestamp of the datapoint.
- **updated**: used to ensure each row is only collected once.
- **quality**: the data quality (empty = “Good”).
- Any column prefixed with **tag\_** is stored as a tag (e.g. **tag\_prodnr**).

Examples:

- **Postgres**: SELECT \* FROM table\_name WHERE updated > $1;
- **Oracle**: SELECT \* FROM table\_name WHERE updated > :1;
- **SQL Server:** SELECT \* FROM table\_name WHERE updated > @p1;
- **MySQL/MariaDB**: SELECT \* FROM table\_name WHERE updated > ?;

Always filter by updated > ? to avoid re-reading old data. The ? placeholder is automatically replaced with the collector’s internal **State**.

:::hint{type="info"}
You can leave the **QueryString** empty if you only want to test whether the collector can connect to your database.
:::

### Example query

Considering we have the following tables:

**sensors**

| pk | id             | value | timestamp              | updated                |
| -- | -------------- | ----- | ---------------------- | ---------------------- |
| 1  | sensor\_temp   | 14    | 2025-09-10 00:00:30+00 | 2025-09-10 02:00:42+00 |
| 2  | sensor\_volume | 100   | 2025-09-10 00:13:45+00 | 2025-09-10 00:15:30+00 |
| 3  | sensor\_weight | 66    | 2025-09-10 00:21:40+00 | 2025-09-10 00:33:30+00 |

**tags**

| pk | line\_nr | sensor\_pk |
| -- | -------- | ---------- |
| 1  | 2        | 3          |
| 2  | 45       | 1          |
| 3  | 6        | 2          |

Using the following query in the collector’s **QueryString** field:

```sql
SELECT
  s.id AS measurement_key,
  s.value AS value,
  s.timestamp AS ts,
  s.updated AS updated,
  'Good' AS quality,
  t.line_nr AS tag_line_nr
FROM sensors s
INNER JOIN tags t ON s.pk = t.sensor_pk
WHERE updated > ?;
```

**The collector will return:**

| measurement\_key | value | ts                   | updated              | quality | tag\_line\_nr |
| ---------------- | ----- | -------------------- | -------------------- | ------- | ------------- |
| sensor\_temp     | 14    | 2025-09-10 00:00:30Z | 2025-09-10 02:00:42Z | Good    | 45            |
| sensor\_volume   | 100   | 2025-09-10 00:13:45Z | 2025-09-10 00:15:30Z | Good    | 6             |
| sensor\_weight   | 66    | 2025-09-10 00:21:40Z | 2025-09-10 00:33:30Z | Good    | 2             |

### State

The collector automatically tracks the updated column as its **State**. This ensures only new rows are collected each cycle.

![](https://api.archbee.com/api/optimize/SMPo4IS9FkillUAMsur2c/RsUmgjy9C8kUo4Sab6Yqd_screenshot-2025-09-17-at-135219.png)

:::hint{type="info"}
The State can be reset to re-collect historic data. Always **pause** the collector before editing its state.
:::

### Expected outcome

After saving, a green **Start** button will appear in the top right corner.

1. Click **Start** to launch the collector.

If the connection is successful, the collector status changes to **Collecting**.

## Adding measurements

Follow the guide on [Adding measurements to an SQL collector](docId\:aIGN3hnpDkRFVNjuQjyz2)

## Best practices

- Always handle **NULL** values in queries with **COALESCE(...)**.
- Use date filters `(updated > ?)` to limit rows.
- Keep queries **efficient** to avoid overloading the source database.

## Limits

- Support varies by driver. See the [SQL collector](docId\:ARYaQtYz4Bk_KoyZry6Ui) reference documentation for a complete list.
- Performance is usually limited by the SQL database itself (indexes, query complexity, network speed).

## Troubleshooting

### Cannot connect to the database

- Check the host and port used in the SQL collector settings are correct.
- If a user is used for connecting to the database,
  - Check if user credentials are correct
  - Check if the user has correct privileges to connect to the database

### Query times out

- If a user is used, check if the user has correct privileges to read data.
- Try increasing the `QueryTimeout` setting in the [SQL collector](docId\:ARYaQtYz4Bk_KoyZry6Ui) settings.

### No placeholder for last\_update\_time

Possible error messages:

- got 1 parameters but the statement requires 0
- wrong number of arguments 1, 0 expected

Cause:

- The Factry collector always adds 1 argument to the [query](docId\:JGCek7sjj-9dntSxr9Zqt): the last\_update\_time, which is the timestamp of the most recent datapoint it received.
- If your query has no placeholder for an argument, you might see this error as the database cannot process it.

Solution:

- Add the placeholder, for example in a where clause to filter on only records newer than the last\_update\_time.
- The placeholder varies depending on the database driver, you can find examples for all supported drivers here: [SQL Collector - Reference](docId\:QvjV59EHCfYV7ZoT483J0)