ODBC

From sqlload 0.27.0, an odbc source loads tables, views and query results from any database that has an ODBC driver. SQLLoad reads the rows as Arrow through the driver and loads them into the source’s target.

From 0.39.0, the sqlload and sqlload-cli wheels include ODBC on every platform. Before that, and in a build from source (cargo install, or pip building an sdist where no wheel fits), ODBC is built only with the odbc feature (cargo install sqlload --features odbc); without it, plan refuses an odbc source.

[connections.erp]
kind = "odbc"
connection_string = "${ENV:ERP_ODBC}"

[sources.erp]
kind = "odbc"
connection = "erp"
description = "Tables of the ERP database."
include = ["sales.*"]
exclude = ["sales.tmp_*"]
max_text_size = 8000
write_strategy = "replace"

[sources.erp.tables."sales.items"]
description = "Items, one row per item."
cursor_column = "updated_at"
unique_key = ["id"]
write_strategy = "merge"

[sources.erp.tables.recent]
description = "Items added recently."
sql = "SELECT id, name FROM sales.items WHERE id > 2400"

$ sqlload plan
Plan ready  2 selected, 2 objects to create, 2 to load
to  DuckDB local

Objects (2)                                         sqlload apply
├── raw.erp__items                                  create table
│   ├── id         int             -> BIGINT
│   ├── name       text            -> VARCHAR
│   ├── price      decimal(10,2)   -> DECIMAL(10,2)
│   ├── created    date            -> DATE
│   ├── updated_at timestamp_ntz   -> TIMESTAMP
│   └── + _sqlload_load_id, _loaded_at
└── raw.erp__recent                                 create table
    ├── id   int    -> BIGINT
    ├── name text   -> VARCHAR
    └── + _sqlload_load_id, _loaded_at

Loads (2)                                           sqlload load
├── raw.erp__items                                  merge (unique_key: id)
│   ├── source  odbc  erp  sales.items
│   ├── route   read as Arrow -> DuckDB takes Arrow
│   ├── reads   updated_at: first run, everything
│   └── commits once, at the end of the read (updated_at isn't read in order)
└── raw.erp__recent                                 replace
    ├── source  odbc  erp  query recent
    └── route   read as Arrow -> DuckDB takes Arrow

✓ Plan complete  2 selected, 2 objects to create, 2 to load

Connection

Key	Meaning
connection_string	The driver’s connection string, such as Driver={PostgreSQL Unicode};Server=db.example.com;Database=erp;Uid=loader;Pwd=.... Use ${ENV:NAME}, since it usually holds a password.
dsn	A data source name from odbc.ini, instead of connection_string. It and user can’t contain ;, { or }.
user, password	With dsn, when the DSN doesn’t hold them.

Set connection_string or dsn, not both. Install the database’s ODBC driver on the machine SQLLoad runs on; SQLLoad reaches it through a driver manager:

• Windows: the driver manager built into Windows; register drivers and DSNs as usual (ODBC Data Sources).
• Linux and macOS wheels (from 0.39.0): the unixODBC 2.3.14 driver manager, bundled as a separate shared library, so no system unixODBC is needed. It reads drivers and system DSNs from odbcinst.ini and odbc.ini in /etc on Linux, where distribution driver packages register, and in Homebrew’s etc on macOS (/opt/homebrew/etc on Apple silicon, /usr/local/etc on Intel), or in the folder ODBCSYSINI names; user DSNs from ~/.odbc.ini. A driver registered by file name alone, as Debian and Ubuntu packages register theirs (Driver=psqlodbcw.so), is looked for in /usr/lib/<arch>-linux-gnu/odbc and /usr/lib/<arch>-linux-gnu (then /usr/lib64/odbc and /usr/lib64) on Linux, and Homebrew’s lib on macOS. unixODBC is LGPL-2.1: its notice and licence are in each wheel’s .dist-info/licenses, and its source is in the package’s sdist on PyPI.
• cargo install --features odbc: the system’s unixODBC, which it links against: unixodbc-dev on Debian and Ubuntu, unixodbc with Homebrew.

Drivers

SQLLoad writes names in ANSI double quotes ("sales"."items"). A database that doesn’t read them as names is refused when the source is read; switch it to ANSI quoting. For MySQL, add INITSTMT=SET SESSION sql_mode=CONCAT(@@SESSION.sql_mode,',ANSI_QUOTES'); to the connection string.

Driver	Tested
psqlODBC (PostgreSQL Unicode)	Yes, against Postgres 17 in containers on Linux and on Windows. Add BoolsAsChar=0 to the connection string so boolean columns load as bool rather than text.
Microsoft ODBC Driver 18 for SQL Server	Yes, against SQL Server 2022 in containers on Linux and against SQL Server LocalDB on Windows, datetime2 and legacy datetime cursors included.
MySQL Connector/ODBC (MySQL ODBC Unicode Driver)	Yes, against MySQL 8.4 in an ANSI_QUOTES session (above): the driver tests run the sqlload executable on Ubuntu 24.04 with MySQL’s Ubuntu 24.04 package of Connector/ODBC 26.10, and on Windows with MySQL’s Windows installer. From 0.43.0, each release’s Linux x86_64 wheels also load a MySQL 8.4 table on Debian 13 through MySQL’s Debian 13 package of Connector/ODBC 9.7, the sqlload-cli wheel as is and the sqlload wheel’s sqlload with nothing set; see MySQL Connector/ODBC on Linux. On Linux, install MySQL’s package for your distribution rather than the generic glibc tarball: the tarball’s own OpenSSL fails to load once another driver, such as SQL Server’s or psqlODBC, has loaded the system one in the same run.
MariaDB Connector/ODBC 3.1	Against MySQL 8.4 by hand, not in the driver tests: it reports no primary keys there, so name each merge table’s unique_key columns instead of "{primary_key}"; see MariaDB Connector/ODBC 3.1.
OpenEdge (Progress)	Tested for timestamp handling (parsing {ts} values, binding timestamp bounds and writing them as {ts} escapes), not against a live server. Run sqlload plan first: it checks that the driver takes the cursor’s bounds, and names cursor_bounds = "literals" when the driver only takes escapes; see Cursor bounds.
Others	Not yet. A driver needs to describe prepared statements and list tables; run sqlload plan to see the types it reports.

MySQL Connector/ODBC on Linux

MySQL’s Debian and Ubuntu packages of Connector/ODBC 9.7 and 26.10 are built for static thread-local storage that reaches into libstdc++’s (the driver’s dynamic section sets STATIC_TLS; readelf -d shows it). The dynamic loader can’t give it that once a process that loaded libstdc++ after it started has used libstdc++’s thread-locals, as a Python process with a C++ extension, SQLLoad’s included, has. unixODBC then reports the driver as missing, even though it exists:

[unixODBC][Driver Manager]Can't open lib '/usr/lib/x86_64-linux-gnu/odbc/libmyodbc9w.so' : file not found

From 0.51.0, on Linux, the sqlload command of the sqlload package starts itself again once with libstdc++ in LD_PRELOAD, so the driver loads with nothing set. Processes it starts, such as a Python source’s subprocesses, inherit that LD_PRELOAD. When libstdc++ isn’t installed, it runs as it is. SQLLOAD_NO_PRELOAD=1 turns the preload off. The sqlload-cli package’s sqlload loads the driver as is.

A Python program that imports SQLLoad and loads in its own process needs libstdc++ preloaded from its start, as does any ODBC program in such a process, pyodbc included:

LD_PRELOAD=libstdc++.so.6 python load.py

From 0.43.0, SQLLoad adds what to check under unixODBC’s message: that the driver library doesn’t exist, that it is built for static thread-local storage and needs the preload above, or else that a library it needs doesn’t load (ldd on the driver lists them).

MySQL’s Debian 13 package of Connector/ODBC 9.7 also registers its drivers under /usr/lib//odbc/, where they aren’t, so every driver manager, isql included, reports file not found. From 0.51.0, SQLLoad’s message names the installed file and the fix. Point the registration at the installed files in /etc/odbcinst.ini:

sudo sed -i 's#/usr/lib//odbc/#/usr/lib/x86_64-linux-gnu/odbc/#' /etc/odbcinst.ini

From 0.39.0, a timestamp column is read in at least microseconds whatever precision the driver describes: MySQL Connector/ODBC describes every DATETIME(n) with none, and its fractional seconds were dropped before.

MariaDB Connector/ODBC 3.1

MariaDB Connector/ODBC 3.1, the odbc-mariadb package of Debian 13 (3.1.15) among others, reports no primary key for MySQL 8.4 tables: its SQLPrimaryKeys looks for COLUMN_KEY = 'pri' in INFORMATION_SCHEMA.COLUMNS, and MySQL 8.4 compares that column case-sensitively with its value PRI (checked with 3.1.15 against MySQL 8.4.13). Before 0.44.0 a table with a cursor_column then took delete_insert, so a row updated after it was loaded was loaded again beside its old version.

From 0.46.0 SQLLoad never takes a key the driver reports unless the table asks for it with unique_key = "{primary_key}". A table that does, through this driver, fails plan, and when the database’s INFORMATION_SCHEMA lists a key the driver didn’t report, the error names it:

$ sqlload plan
…
Errors (1)
└── raw.erp__items                                  no key to merge on
    ├── reason  items (kind = "odbc"): unique_key = "{primary_key}", but the source reports no
    │           primary key for the table. The ODBC driver reported no primary key, but the
    │           database's INFORMATION_SCHEMA lists PRIMARY KEY (id), so the driver's key lookup is
    │           wrong (MariaDB Connector/ODBC 3.1 finds none in MySQL 8.4)
    └── fix     name the key columns, such as unique_key = ["id"]

✗ Plan failed with errors

Name the key columns on each such table, or use MySQL Connector/ODBC, which reports MySQL’s keys.

Cursor bounds

From 0.39.0, a cursor filter compares the cursor column with bound parameters, such as "updated_at" > ?, each passed as the cursor’s own type: a timestamp without a time zone as an ODBC TIMESTAMP; a date as a DATE; text as text. No database’s literal rules can change a bound, and a millisecond maximum such as 23:59:59.997 resumes exactly. A cursor column pinned to a type (columns = { ... }) compares with a quoted literal, as the pinned type may not be the column’s own, such as a varchar read as a timestamp. A numeric cursor compares with a bare number, such as "id" > 41. Any other cursor, a timestamp with a time zone for one, still compares with a quoted literal, which the database converts to the column’s type; check that yours does before relying on it. For a timestamp or date cursor, --start-cursor, --end-cursor and cursor_start also take ODBC escape values, such as {ts '2026-01-31 23:59:59.997'} or {d '2026-01-31'}.

From 0.47.0, a timestamp bound has the fractional digits the driver describes for its column: three for milliseconds (1 to 3 digits), six for microseconds (4 to 6). Before, it always had seven, which a driver may refuse for a column with fewer. A bound with more digits than that, such as a --start-cursor of 10:00:00.1235 on a millisecond column, or a maximum from a driver that describes fewer digits than its values hold, keeps every digit and is never rounded, so it selects exactly the rows it did before. When the driver describes the column with no digits, as MySQL Connector/ODBC describes every DATETIME(n), or with seven or more, the bound keeps every digit, as before. SQL Server keeps its own handling: a timestamp binds with seven digits, compared as datetime2(7), except against a legacy datetime, which stores 1/300 s ticks that its driver reads rounded to milliseconds, so its bounds go as ISO 8601 text (2026-01-31T23:59:59.997) in the column’s own precision, which SQL Server converts to the column’s type alike under every login language.

Literal bounds

Some drivers refuse a bound value; OpenEdge’s, by one report, takes no timestamp parameter there. From 0.47.0, cursor_bounds = "literals" on an ODBC source, or on one of its table entries, writes the bounds into the query as ODBC escapes instead (the default is "parameters"):

[sources.erp]
kind = "odbc"
connection = "erp"
cursor_bounds = "literals"

Cursor	Bound
Timestamp without a time zone	{ts '2026-01-31 23:59:59.997'}, with the column’s fractional digits when the bound has no more (as above), else the fewest of 0, 3, 6 or 9 that keep every digit
Date	{d '2026-01-31'}
Number	41, as with parameters
Text	'o''brien', in single quotes with each quote doubled

It covers every bound: after the destination’s maximum, --start-cursor, --end-cursor, cursor_start, lookback and each batch_size window. Keep the default on SQL Server: its {ts} escape is a datetime, which rounds milliseconds to 1/300 s. A text bound holding a backslash is refused under literals, as a database that treats a backslash in a string as an escape, MySQL by default, would read it as another value; bind it as a parameter instead.

Checked at plan

From 0.47.0, plan (and load, which plans first) checks each ODBC table whose text, date or timestamp cursor binds: it runs the table’s query once with bounds that select no rows, "updated_at" > v AND "updated_at" < v AND 1 = 0, with v the planned lower bound (else the upper bound, else a fixed value of the cursor’s type), and fetches nothing. When the driver refuses the bounds but takes them written the other way, and refuses them again its own way, the table fails and the error names the setting:

Errors (1)
└── items
    └── reason  the ODBC driver refuses the cursor updated_at's bounds as parameters ([HY000] …) but takes them as ODBC escapes; set cursor_bounds = "literals" on source erp or its table entry

When both ways fail, the error shows both of the driver’s errors.

Choosing tables

include and exclude are schema.table patterns, matched without regard to case (* matches any text). SQLLoad lists the tables and views the driver reports. For a database without schemas, the patterns match the table name alone. A [sources.erp.tables."schema.table"] entry sets options for one table; on its own, it doesn’t add the table.

A source needs at least one include pattern or one query table. Every table needs a write_strategy, from its entry or the source, and a merge table a unique_key: its columns, or "{primary_key}" for the primary key the driver reports (SQLPrimaryKeys); see Write strategies. A query table reports no key, so name its columns. When the driver reports no primary key for a table set to "{primary_key}", SQLLoad also asks the database’s INFORMATION_SCHEMA for the table’s primary key and unique constraints, so that the error can name them; a database without INFORMATION_SCHEMA gets the error without them.

Query tables

A table entry with sql is a query table: its rows are the query’s result, and the entry’s key is its name. SQLLoad prepares the query at plan to read its columns, without running it.

cursor_column names a column of the query’s result. SQLLoad wraps the query and filters on the cursor outside it, so the query needs no placeholder. Write the query in the database’s own SQL.

Value sizes

Drivers read values into buffers of a fixed size per column, from the length the driver reports. max_text_size (bytes) caps the buffer of every text column, and max_binary_size that of every binary column, so set them when the driver reports a huge length for unbounded columns such as TEXT or VARCHAR(MAX), or none at all, which the read then refuses, naming the setting. A value longer than its buffer, even in a VARCHAR(10000) column capped at 8000 bytes, fails the read, naming the setting to raise; it’s never cut short.

From 0.47.0, the driver’s own notes on the buffer size it chose for each column are no longer logged; set RUST_LOG=info to see them again.

Types

SQLLoad reads each column’s type as the driver describes it:

ODBC type	Loads as
TINYINT, SMALLINT	smallint
INTEGER, BIGINT	int
NUMERIC(p,s), DECIMAL(p,s), p up to 38	decimal(p,s), read as text so no digit is lost
REAL, FLOAT, DOUBLE	float
BIT	bool
DATE, TIME	date, time
TIMESTAMP	timestamp_ntz; a timestamp with a time zone, in the session’s time zone
CHAR, VARCHAR and their wide and long forms	text
BINARY, VARBINARY, LONGVARBINARY	binary
Anything else, and NUMERIC above 38 digits	text, as the driver writes it

A decimal value with more places than its column’s scale fails the read rather than being rounded. psqlODBC describes an unbounded numeric as NUMERIC(28,6), so pin such a column, for example columns = { amount = "decimal(38,10)" } or "text".

Pin a type to load a column another way, such as columns = { total = "decimal(18,2)" }. A value that doesn’t convert to the pinned type fails the read, naming the column. See Types.

Permissions

The database user needs to read the catalog (to list tables and their keys) and SELECT on the tables and views. INFORMATION_SCHEMA lists only the constraints the user may see, so the key a driver missed is named only where it can: Postgres, for one, lists a table’s constraints only to its owner or a user with a privilege on it other than SELECT.