ODBC

DB-API (Python) · JDBC · ODBC

The potluckdb driver implements ODBC 3.8 Unicode entry points over the C SDK. Tools and languages using ODBC can read and write the synchronized database without their own potluckdb SDK.

Install the driver through the platform’s driver manager: Windows ODBC Data Source Administrator, or unixODBC or iODBC on macOS and Linux.

Requirements

Use a 64-bit process on Windows, macOS or Linux with glibc. Use Windows’ driver manager, or unixODBC or iODBC on macOS and Linux.

No application built on the driver is verified beyond the program below. Other tools that use ODBC 3.8 through these driver managers are expected to work, but this page makes no claim for them.

What ships

The C column of the compatibility matrix, reached through ODBC. Each release runs the program on the platforms in this table. Other platforms have no verification for this interface.

Platform	Targets	Package
macOS	macos-arm64, macos-x86_64	potluckdb-odbc
Linux (glibc)	linux-x86_64-gnu, linux-arm64-gnu	potluckdb-odbc
Windows	windows-x86_64, windows-arm64	potluckdb-odbc

Install

On Windows:

1. Download the bundle for your target.
2. Run its installer.

The installer registers the driver as potluckdb. Download the bundle from this URL:

https://downloads.potluckdb.com/releases/0.1.0/odbc/potluckdb-odbc-0.1.0-<target>.zip

The macOS and Linux archive contains the driver library for unixODBC, the width-matched driver variant for iODBC, the runtime and storage libraries, and one registration file for each manager.

1. Download the archive for your target.
2. Unpack it into /opt/potluckdb-odbc.
3. Register the driver with its full path.

curl -LO https://downloads.potluckdb.com/releases/0.1.0/odbc/potluckdb-odbc-0.1.0-<target>.tar.gz
sudo mkdir -p /opt/potluckdb-odbc
sudo tar -xzf potluckdb-odbc-0.1.0-<target>.tar.gz -C /opt/potluckdb-odbc
sed 's|^Driver=|Driver=/opt/potluckdb-odbc/|' /opt/potluckdb-odbc/potluckdb.odbcinst.ini > potluckdb.odbcinst.ini
sudo odbcinst -i -d -f potluckdb.odbcinst.ini

The archive has no top-level directory, and registration uses the driver’s full path, so keep the files in /opt/potluckdb-odbc.

odbcinst belongs to unixODBC. For iODBC:

1. Use the entry in potluckdb.odbcinst.ini.iodbc.
2. Set its Driver path to /opt/potluckdb-odbc/libpotluckdb_odbc_iodbc.so (.dylib on macOS).
3. Add the entry to iODBC’s odbcinst.ini.

The Windows installer writes the equivalent registry entry.

To configure a named source, add an entry to the manager’s data-source configuration. Use odbc.ini for unixODBC or iODBC, or Windows ODBC Data Source Administrator. Stored paths and values are literal Unicode text:

[notes]
Driver=potluckdb
Database=/absolute/path/notes.potluckdb
maximum_rows=1000

Select the source with SQLConnectW and notes, or SQLDriverConnectW and DSN=notes;maximum_rows=2000. Explicit options override stored values. Named-source rules define validation, credential refusal and completed-string behavior.

Connect

One connection string serves DB-API, JDBC and ODBC. potluckdb:<path> names the local database. Parameters after ? name the remote and options. Each parameter appears at most once, and no other parameters exist.

The string never contains secrets. Storage credentials come from the provider’s standard chain, as the installation pages describe. The encryption key comes from a file named in the string. Strings outside the grammar fail before anything opens.

ODBC uses the same grammar in its own form. Set Driver=potluckdb and Database for the path. Each parameter has its own case-insensitive name. This applies to a string passed to SQLDriverConnect and a string stored in a data source name.

Sync=manual is refused because ODBC has no synchronization operation. Connections use automatic synchronization.

Driver=potluckdb;Database=app.potluckdb;Remote=s3://my-bucket/notes;Region=eu-west-1

Parameter	Value	Sets
remote	dir:<path>, s3://<bucket>/<namespace>, gs://<bucket>/<namespace> or az://<account>/<container>/<namespace>	The remote. Without it, the database is local only.
region	a region name	The region of an s3:// remote. A string that sets it without an s3:// remote is refused.
endpoint	a URL	The endpoint of an S3-compatible storage service. A string that sets it without an s3:// remote is refused.
sync	automatic or manual	The synchronization policy, with the factory’s defaults.
prefetch	automatic or disabled	The prefetch policy, with the factory’s defaults.
checkpoint	automatic or disabled	The checkpoint policy, with the factory’s defaults.
key_file	a path	A file holding the encryption key as hexadecimal text. The encryption key never appears in the string.
license_key	a license key	The license_key open option. If absent, the environment and configuration directory apply.
analytical_engine	a path	The analytical_engine open option.
client_id	an identifier	The client_id open option.
local_budget_bytes	an integer	The open option of that name.
minimum_local_budget_bytes	an integer	The open option of that name.
local_compaction_bytes	an integer	The open option of that name.
timeout_ms	an integer	The connection option of that name.
maximum_rows	an integer	The connection option of that name.

The options themselves are described on the options page.

Behavior

SQLExecDirect and SQLExecute distinguish reads from writes before execution, as the SDK’s query and execute do. Auto-commit defaults to on, making each write a transaction. With SQL_ATTR_AUTOCOMMIT off, the first write starts a transaction, and SQLEndTran ends it.

A statement failure can make SQLite roll back the whole transaction, such as a storage error or INSERT OR ROLLBACK. The error reports this. Until SQLEndTran with SQL_ROLLBACK acknowledges it, these calls fail with invalid_state:

• Any later statement
• SQLEndTran with SQL_COMMIT
• Setting SQL_ATTR_AUTOCOMMIT on

1. Call SQLEndTran with SQL_ROLLBACK to acknowledge the rollback.
2. If that call fails, call it again until it succeeds before any other call on that connection.
3. Run the whole transaction again.

A failed first write needs no acknowledgement because no earlier statements were lost.

Operation	Result
SQLDescribeCol	Column names
SQLRowCount	Rows affected by a write
SQLTables, SQLColumns, SQLPrimaryKeys	Catalog information from SQLite’s schema

SQLGetDiagRec reports errors with SQLSTATE HY000. Its message begins with the error code and ends with the action. A refused connection string reports 08001.

If SQLEndTran with SQL_ROLLBACK fails, call it again until it succeeds. Make no other call on that connection before then. The call can fail, for example, with the temporary database_busy while a synchronization holds the database. In this release, these calls after a failed rollback continue the transaction and can commit work that rollback should have discarded:

• SQLEndTran with SQL_COMMIT
• Setting SQL_ATTR_AUTOCOMMIT on
• A statement

Quickstart

The program uses pyodbc to reach the driver. It opens a database with a directory remote, writes with auto-commit and reads the row. It also asks the catalog for the table and demonstrates a refused statement. See Concepts for the remote model.

1. Install pyodbc with pip install pyodbc.
2. Run python quickstart.py.

It prints quickstart: ok.

On macOS and Linux with glibc, this Python program requires the validated unixODBC/pyodbc profile. iODBC registration and direct C ABI conformance are separate.

The iODBC 3.52.16/pyodbc 5.3.0 Python profile is unavailable. The manager’s wide loader-error diagnostics can make that client read unwritten message-buffer contents. Successful queries do not establish safe failure handling.

With pyodbc 5.3.0 and Homebrew unixODBC 2.3.14 on macOS, non-BMP input patterns in tables() and primaryKeys() can select the wrong table. The manager’s ANSI conversion changes them. Use ASCII patterns on that route. The driver’s Unicode W entry points preserve these names. pyodbc’s columns() and result-column names use that W route.

This limitation is specific to that client and driver manager profile. This page makes no claim about other unixODBC builds.

quickstart.py

# One database, one remote, through ODBC. pyodbc talks to the platform's driver
# manager, which loads the potluckdb driver named in the connection string.
# On macOS and Linux use the validated unixODBC/pyodbc profile on the ODBC page.

import pyodbc

connection = pyodbc.connect(
    "Driver=potluckdb;Database=app.potluckdb;Remote=dir:shared-remote",
    autocommit=True,
)
cursor = connection.cursor()

# Auto-commit is on: each write is its own transaction.
cursor.execute(
    "CREATE TABLE IF NOT EXISTS note(id INTEGER PRIMARY KEY, body TEXT NOT NULL)"
)
cursor.execute("INSERT INTO note(body) VALUES(?)", "buy milk")
assert cursor.rowcount == 1

# Reads return rows; the cursor description carries the column names.
cursor.execute("SELECT id, body FROM note ORDER BY id")
assert [column[0] for column in cursor.description] == ["id", "body"]
rows = cursor.fetchall()
assert rows[-1].body == "buy milk"

# The catalog answers from SQLite's schema, which is what a desktop tool asks first.
assert any(table.table_name == "note" for table in cursor.tables(tableType="TABLE"))

# pyodbc frames the driver's SQLSTATE, code and action in its exception.
try:
    cursor.execute("BEGIN")
except pyodbc.Error as error:
    assert error.args[0] == "HY000", error.args
    assert "transaction_control_sql:" in error.args[1], error.args
    assert (
        "Use transaction() so potluckdb can manage commit and rollback."
        in error.args[1]
    ), error.args
else:
    raise AssertionError("BEGIN unexpectedly succeeded")

cursor.close()
connection.close()
print("quickstart: ok")

Verified by the release

Each release installs the driver through the platform’s driver manager on macOS, Linux (glibc) and Windows and runs this program through pyodbc. It checks connection strings, statements, transactions, catalogs, and diagnostics.