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_*"]
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. From 0.52.0, a text or binary column whose buffer would be over long_column_threshold (4 KiB when omitted), or that the driver gives no length for, such as TEXT, LONGTEXT or VARCHAR(MAX), is a long column. A table with a long column is read value by value: each value at its own size, in pieces for a long one, and never cut. A table without one is read in bulk, many rows per fetch. See Memory.

max_text_size (bytes) caps every text column, and max_binary_size every binary column. They are optional. A capped column counts as long only when the cap is over the threshold. A value longer than its cap, even in a VARCHAR(10000) column capped at 8000 bytes, fails the read, naming the setting to raise; it’s never cut short. In a long column, the read stops as soon as the driver reports the value’s length or the pieces read pass the cap, so a 2 GB value over the cap isn’t read first. Text is compared as UTF-8 bytes, on Windows too, where drivers send it as UTF-16.

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.

Memory

From 0.52.0, each ODBC table has a fetch buffer: the most the rows of one fetch hold. It’s 512 MiB per table by default. On a smaller machine, it’s half the memory shared between the tables loading at once (concurrency), when that is less. The memory is the container’s limit (the cgroup’s memory.max) when SQLLoad runs in one, and otherwise the machine’s memory. The fetch buffer is never raised above 512 MiB unless the source sets max_batch_memory:

[sources.shop]
kind = "odbc"
connection = "shop"
max_batch_memory = "2GB"

The fetch buffer bounds the source’s fetches only, not the load’s whole memory: the batches made from them, the destination and the commits take memory besides. To measure what a load really takes, read peak_memory_bytes in the JSON report, or run once with --memory-per-table on Linux; see Finding a source that uses too much memory.

plan shows how each ODBC table fetches (fetch) and its fetch buffer, and why (buffer):

$ sqlload plan
…
Loads (2)                                           sqlload load
├── raw.shop__contacts                              replace
│   ├── source  odbc  shop  contacts
│   ├── route   read as Arrow -> DuckDB takes Arrow
│   ├── fetch   up to 512 MiB or 65,536 rows per fetch; 265 long columns (c001, c002, c003, …), so all 266 columns are read value by value
│   └── buffer  512 MiB fetch buffer per table (default; 6 GiB container limit, concurrency 4)
└── raw.shop__orders                                replace
    ├── source  odbc  shop  orders
    ├── route   read as Arrow -> DuckDB takes Arrow
    ├── fetch   ~13.6 MiB per fetch, 65,536 rows
    └── buffer  512 MiB fetch buffer per table (default; 6 GiB container limit, concurrency 4)

• In bulk (orders): a fetch reads as many rows as its buffers hold within the fetch buffer, at most 65,536. The fetch line shows the buffers of one fetch.

• Value by value (contacts): the driver returns one row at a time, and SQLLoad reads each of its values. A fetch ends at 65,536 rows, or once the values it has read reach the fetch buffer. A value larger than the whole fetch buffer still loads; its fetch ends after its row, and SQLLoad logs it:

  … WARN sqlload::odbc_source::_helpers::values: a value is larger than the table's fetch buffer (max_batch_memory); it loads, and its fetch ends after its row table="notes" column=body bytes=40000000 budget=16000000

A table with a long column fetches one row at a time because reading values with SQLGetData from a block of rows breaks on common drivers: on MariaDB Connector/ODBC 3.1, every row after the first returns SQL_NO_DATA, and on Microsoft ODBC Driver 18 for SQL Server, SQLSetPos fails on the forward-only cursor. Tables without a long column keep fetching in bulk.

The fetch buffer sets only how many rows a fetch reads. Commits stay as batch_size sets them, so a smaller fetch buffer means more fetches, not different data.

When the fetch buffers of the tables that may load at once together pass half the memory, plan warns, and load checks again when it starts, since it may run on another machine. The warning counts the fetch buffers only. Here the source sets max_batch_memory = "4GB" on a machine with a 6 GiB container limit:

$ sqlload plan --concurrency 2
…
Warnings (1)
└── the ODBC fetch buffers of 2 tables loading at once may hold 3.7 GiB together, more than half of this machine's 6 GiB memory limit; this counts only those buffers, not the memory the batches, the destination and the commits take besides; lower max_batch_memory on source shop, or lower concurrency (now 2)

Key	Default	Meaning
max_batch_memory	512 MiB, or less on a small machine	The fetch buffer of each table: the most the rows of one fetch hold, such as "2GB" or "256MiB". It doesn’t bound the load’s whole memory.
long_column_threshold	"4KiB"	The buffer per value over which a text or binary column is read value by value.

Value by value is slower than bulk: SQLLoad asks the driver for each value with its own call (SQLGetData), as pyodbc reads every value (pyodbc 5.2.0, getdata.cpp). It saves memory when most values are much shorter than their column allows, as in wide tables of short notes. When the values really are long, they take the memory either way. A table with one long column is read value by value in all its columns; raise long_column_threshold above the column’s size to read it in bulk, with buffers of that size.

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.