README
¶
About
odbc is a database/sql driver for ODBC, written in pure Go. ODBC is a
standard way for a program to talk to a database. The driver loads the ODBC
driver manager of the system at run time with purego. The driver
manager is the system library that finds the database drivers and calls them.
The driver needs no cgo and no C compiler. One code base serves Windows, macOS
and Linux.
The driver works on Linux against PostgreSQL, MariaDB, MySQL, SQL Server and
SQLite. It has not run on macOS or Windows yet, and DuckDB is not tested.
docs/PROGRESS.md says where the work stands.
Installing
go get github.com/xo/odbc
The dependencies are purego and dbimp, which brings apd. You
also need an ODBC driver manager and the ODBC driver of your database installed
on the machine.
Using
import (
"database/sql"
_ "github.com/xo/odbc"
)
db, err := sql.Open("odbc", "odbc+PostgreSQL+Unicode://user:pass@localhost:5432/dbname")
The data source name is a URL with the scheme odbc+<driver>. Write the driver
name as the driver manager knows it, with a plus sign for each space. The name
can also be an ODBC connection string, such as DRIVER={SQLite3};Database=/tmp/a.db. The
query key driver names a library by its path, and manager names the driver
manager. See D16 for the rest.
Placeholders are ?. Values have the Go types of the dbimp kinds. A decimal
is a *apd.Decimal, a date is a dbimp.Date, and a timestamp with no zone is
a dbimp.LocalDateTime. D18 has the whole table.
A statement takes options as arguments or through the context. They are
odbc.WithTimeout, odbc.WithDatabase, odbc.WithReadonly and
odbc.WithParameter, as in the other dbimp drivers. A database driver that
cannot honor an option makes the statement fail with dbimp.ErrNotSupported.
D22 has the rules.
A tool can ask the driver about the database. odbc.Drivers and
odbc.DataSources list what the driver manager knows. odbc.Conn, which
sql.Conn.Raw gives, wraps SQLGetInfo. Config.OnWarning receives the
informational diagnostics of a statement, and Config.TraceFile turns on the
trace of the driver manager. errors.Is(err, odbc.ErrIntegrity) tests a class
of SQLSTATE. D23 has the rules.
odbc.WithFetchSize reads a large result in blocks, odbc.WithMaxRows limits
it, and Config.Location makes a timestamp a time.Time in a location.
odbc.Conn also has Tables, Columns and PrimaryKeys, which read the
metadata of any database. D24 has the rules.
FAQ
How do I turn on ANSI SQL mode for MariaDB and MySQL?
MariaDB and MySQL read double quotes as text, and || as a logical or, unless
the session is in ANSI SQL mode. Turn the mode on with the INITSTMT key, which
runs a statement each time a connection opens:
odbc+MariaDB://user:pass@host:3306/db?INITSTMT=SET+SESSION+sql_mode%3D%27ANSI%27
In a connection string, put the statement in braces:
DRIVER={MariaDB Unicode};SERVER=host;PORT=3306;UID=user;PWD=pass;DATABASE=db;INITSTMT={SET SESSION sql_mode='ANSI'}
usql takes the same URL:
usql 'odbc+MariaDB+Unicode://user:pass@host:3306/db?INITSTMT=SET+SESSION+sql_mode%3D%27ANSI%27'
To check it, run SELECT @@sql_mode. The answer holds ANSI and ANSI_QUOTES,
and SELECT "name" FROM "table" reads a column and a table. On MariaDB the
answer is REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI.
INITSTMTis a key of the ODBC driver of the database and not of this driver. It works the same for any Go ODBC driver that sends the connection string on.- It runs on every connection that a pool opens, so every session has the mode.
To change one session, run
SET SESSION sql_mode='ANSI'as a statement. - The word ANSI also names a kind of ODBC driver, as in "MariaDB ANSI" and "MariaDB Unicode". That is a different setting. This driver calls the wide functions, so it needs the Unicode driver.
TestInitStmtruns the setting against MariaDB and MySQL, with the MariaDB driver for both (D14). MySQL Connector/ODBC documents the same key, and the tests do not run it.
Why does my program crash when it links go-sqlite3 and DuckDB?
The first query of a program that links both github.com/mattn/go-sqlite3 and
the DuckDB bindings, and that uses the SQLite ODBC driver, ends in a
segmentation fault. The DuckDB bindings link with -rdynamic, which makes the
program export every global symbol of its own, including the 268 sqlite3_*
functions of the SQLite that go-sqlite3 carries. The SQLite ODBC driver is a
shared library that needs sqlite3_* functions too. The dynamic linker gives it
the copy in the program for some functions and the copy in libsqlite3.so for
the others, and the two copies do not match. The same thing can happen to any
ODBC driver that shares a library with a copy that your program carries and
exports.
This is not a fault of odbc, and the driver cannot prevent it. Use one of
these:
- Build
go-sqlite3against the SQLite of the system, with the build taglibsqlite3. The program then exports nosqlite3_*function, and both sides use the same library. - Leave
go-sqlite3or DuckDB out of the program. - Build with
CGO_ENABLED=0.
usql shows it. With the default tags and odbc, the first query
crashes. With -tags 'odbc libsqlite3' it works.
How do I pass another setting to an ODBC driver?
Every ODBC driver has its own keys. Add one to the query of the URL, or to the
connection string, and this driver passes it on. A value that holds a space, an
equals sign or a semicolon is put in braces for you when you use the URL form.
The keys driver, manager and wchar are the exceptions. They configure this
driver, and D16 and D20 describe them.
How do I find the name of an ODBC driver?
Call odbc.Drivers, which lists every driver that the driver manager knows, with
its name and its attributes. On Linux and macOS, odbcinst -q -d prints the same
names. A driver that is not registered can be named by the path of its library,
as in ?driver=/usr/lib/psqlodbcw.so, on Linux and macOS.
Which placeholder does a statement take?
Whatever the database takes, and for every database that the tests use it is ?.
The driver passes the text of the statement on as it is. psqlODBC rejects $1
(D16).
Why is a timestamp a dbimp.LocalDateTime?
An ODBC timestamp has no time zone, and a time.Time always has one. A
time.Time here claims a zone that the database did not state. D18 gives the Go type of each kind. Set Config.Location
to get a time.Time in a location instead (D24).
How do I see what the driver manager does?
Set Config.TraceFile. The driver manager then writes every ODBC call to that
file (D23).
Platforms
| System | Driver manager the driver loads |
|---|---|
| Windows | the one built into the system, odbc32.dll |
| macOS | unixODBC or iODBC, for example from Homebrew |
| Linux | unixODBC |
The driver tries the usual names of the library on each system. D2 lists them,
and the manager key of the data source name overrides them.
Testing
The driver is tested against these databases, with their own ODBC drivers. PostgreSQL is the baseline. Other databases are not tested.
| Database | ODBC driver |
|---|---|
| PostgreSQL | psqlODBC |
| SQLite | the SQLite ODBC driver |
| SQL Server | the Microsoft ODBC driver |
| MySQL and MariaDB | MariaDB Connector/ODBC, for both |
| DuckDB | the DuckDB ODBC driver |
SQL Server is tested on Linux only.
Documents
| To do this | Read |
|---|---|
| Send a change | CONTRIBUTING.md |
| Read the rules for a coding agent | AGENTS.md |
| Find out why something is the way it is | docs/decisions/README.md |
| Find work that is known and not done | docs/BACKLOG.md |
| See where the work stands | docs/PROGRESS.md |
Contributing
Read CONTRIBUTING.md. There are 24 decisions so far.
Documentation
¶
Overview ¶
Package odbc is a database/sql driver for ODBC, written in pure Go.
It loads the ODBC driver manager of the system at run time with purego, so it needs no cgo and no C compiler. It targets Windows, macOS and Linux with the same code.
Import it for its side effect and open a database by name:
import _ "github.com/xo/odbc"
db, err := sql.Open("odbc", "odbc+PostgreSQL+Unicode://user:pass@localhost/db")
The data source name is described by ParseDSN. A value has the Go type that dbimp gives its kind, so a decimal is a *apd.Decimal and a date is a dbimp.Date.
Index ¶
Constants ¶
const ErrTruncated = closedError("odbc: a value was cut short")
ErrTruncated is the error of a value that does not fit the buffer of its column when the rows are fetched in blocks (D24). The driver reports it and never returns a value that was cut short. Read the result without WithFetchSize.
const Name = "odbc"
Name is the name the driver registers under.
Variables ¶
This section is empty.
Functions ¶
Types ¶
type ClassError ¶
type ClassError string
ClassError is a class of SQLSTATE, which is the first two characters of the state, or three for a timeout. errors.Is(err, odbc.ErrIntegrity) is true for an Error whose state is in that class. A SQLSTATE class is the same in every database, so a caller can test for it without knowing the database (D23).
const ( // ErrConnection is a failure of the connection (08). ErrConnection ClassError = "08" // ErrData is a value the database cannot take, such as an overflow (22). ErrData ClassError = "22" // ErrIntegrity is a violation of a constraint, such as a duplicate key (23). ErrIntegrity ClassError = "23" // ErrRollback is a transaction that the database rolled back, such as a // deadlock or a serialization failure (40). ErrRollback ClassError = "40" // ErrSyntax is a syntax error or a refused access (42). ErrSyntax ClassError = "42" // ErrTimeout is a timeout, of a statement or of a login (HYT). ErrTimeout ClassError = "HYT" )
The classes that a caller tests for most often.
type ColumnInfo ¶
type ColumnInfo struct {
Catalog string
Schema string
Table string
Name string
// DataType is the SQL type code of ODBC, such as 12 for SQL_VARCHAR.
DataType int
// TypeName is the name that the database gives the type.
TypeName string
// Size is the length of a text or the precision of a number.
Size int
// Digits is the number of digits after the decimal point.
Digits int
// Nullable is 0 for a column that holds no NULL, 1 for one that does, and 2
// when the database driver does not know.
Nullable int
Remarks string
Default string
// Ordinal is the position of the column in the table, from 1.
Ordinal int
}
ColumnInfo is a column that SQLColumns lists.
type Config ¶
type Config struct {
// ConnString is the ODBC connection string handed to the driver manager.
ConnString string
// Manager is the path of the driver manager library. It is empty to use
// the default name of the system.
Manager string
// WChar is the size of SQLWCHAR in bytes, 2 or 4. It is 0 to detect it.
WChar int
// TraceFile is the file that the driver manager writes its trace to. An
// empty name leaves the trace off (D23).
TraceFile string
// OnWarning is called with each informational diagnostic that a connection
// or a statement returns with a success code, such as a message that the
// database printed. It runs on the goroutine of the call, so it must
// return quickly. Nil ignores them (D23).
OnWarning func(*Error)
// Location, when it is set, makes a timestamp a time.Time in that location,
// and sends a time.Time as its time in that location. When it is nil, a
// timestamp is a dbimp.LocalDateTime, as D18 says (D24).
Location *time.Location
}
Config is what a data source name holds once it is parsed.
func ParseDSN ¶
ParseDSN parses a data source name. Two forms are accepted.
A URL whose scheme is odbc+<driver>, where the driver is the name the driver manager knows, with a plus sign for each space:
odbc+PostgreSQL+Unicode://user:pass@host:5432/dbname?sslmode=disable odbc+ODBC+Driver+18+for+SQL+Server://sa:pass@host:1433/instance/dbname
The path is the database name, or the instance and then the database name. An instance becomes part of the server, as host\instance. The query keys become keys of the connection string. The key driver replaces the driver name, and it can be the path of a library. The key manager is the path of the driver manager, and the key wchar is the size of SQLWCHAR in bytes, 2 or 4, for a manager that the driver cannot probe. Neither is passed on.
Anything else is taken to be an ODBC connection string, such as the one dburl builds, and is passed on as it is.
type Conn ¶
type Conn interface {
GetInfoString(info InfoType) (string, error)
GetInfoUint16(info InfoType) (uint16, error)
GetInfoUint32(info InfoType) (uint32, error)
Tables(ctx context.Context, catalog, schema, table, tableTypes string) ([]TableInfo, error)
Columns(ctx context.Context, catalog, schema, table, column string) ([]ColumnInfo, error)
PrimaryKeys(ctx context.Context, catalog, schema, table string) ([]PrimaryKeyInfo, error)
}
Conn is the connection of the driver, which a program reaches through sql.Conn.Raw to ask the database about itself (D23).
conn.Raw(func(dc any) error {
name, err := dc.(odbc.Conn).GetInfoString(odbc.InfoDBMSName)
...
})
The type of the answer depends on the information type, and the ODBC reference says which. A text type reads with GetInfoString, a 16 bit number with GetInfoUint16 and a 32 bit number with GetInfoUint32.
Tables, Columns and PrimaryKeys wrap the catalog functions of ODBC, which read the metadata of any database in one way.
type Connector ¶
type Connector struct {
// contains filtered or unexported fields
}
Connector opens connections. It owns the ODBC environment, which the connections it opens belong to. Close it after every connection is closed, and database/sql does that when the DB is closed.
func NewConnector ¶
NewConnector loads the driver manager and allocates an environment.
type DataSource ¶
type DataSource struct {
// Name is the name that a connection string gives as DSN.
Name string
// Description is the name of the driver of the data source.
Description string
}
DataSource is a data source name that the driver manager knows.
func DataSources ¶
func DataSources(cfg Config) ([]DataSource, error)
DataSources lists the data source names that the driver manager knows. The zero Config uses the default driver manager of the system.
type Driver ¶
type Driver struct{}
Driver is the database/sql driver.
func (*Driver) Open ¶
Open refuses, because a connection needs a context. database/sql calls Driver.OpenConnector instead.
type DriverInfo ¶
type DriverInfo struct {
// Name is the name that a connection string gives as DRIVER.
Name string
// Attributes are the keys that the driver registered, such as its library.
Attributes map[string]string
}
DriverInfo is an ODBC driver that the driver manager knows.
func Drivers ¶
func Drivers(cfg Config) ([]DriverInfo, error)
Drivers lists the ODBC drivers that the driver manager knows. The zero Config uses the default driver manager of the system. Only its Manager and WChar apply (D23).
type Error ¶
type Error struct {
// Op names the call that failed.
Op string
// State is the five character SQLSTATE.
State string
// Native is the code of the database.
Native int32
// Message is the text of the database.
Message string
// Next holds the further records of the same call.
Next []Error
}
Error is a diagnostic record of the driver manager. It is the error that a failed call returns, so a caller finds the SQLSTATE with errors.As.
func (*Error) ConnectionError ¶
ConnectionError reports whether the SQLSTATE is in class 08, a failure of the connection.
type Option ¶
Option sets an option of one statement. An option comes from the context through WithOptions, then from an argument of the statement, and a later one wins. Every dbimp driver takes options in the same way (D22).
db.QueryContext(ctx, "SELECT ...", odbc.WithTimeout(5*time.Second), id)
func WithDatabase ¶
WithDatabase sets the catalog of one statement. A statement of a transaction runs in the catalog of the transaction, and another catalog fails with dbimp.ErrNotSupported.
func WithFetchSize ¶
WithFetchSize reads the rows of the result n at a time, in a block that the database driver fills with one call. It is faster for a large result. It is a hint about speed. A result with a column that cannot be bound, such as a long text, is read one row at a time all the same. A value that does not fit the buffer of its column fails the read and never comes back cut short (D24).
func WithMaxRows ¶
WithMaxRows limits the number of rows that the statement returns. A negative value is not valid, and zero asks for no limit (D24).
func WithNoScan ¶
WithNoScan turns off the escape sequences of ODBC, such as {fn now()}, so that the database gets the text of the statement as it is (D24).
func WithParameter ¶
WithParameter sets a statement attribute by its name. The names are query_timeout, max_rows, no_scan, max_length, cursor_type, concurrency and keyset_size, and the value is an integer or a bool. A parameter replaces what another option sets for the same attribute.
func WithReadonly ¶
WithReadonly asks the database to refuse a statement that writes. ODBC has no setting for it that every database driver enforces. So WithReadonly(true) always fails with dbimp.ErrNotSupported, and WithReadonly(false) asks for nothing. A database that needs it can use a read only account.
func WithTimeout ¶
WithTimeout sets how long the database gives the statement. The driver rounds a part of a second up, because ODBC counts whole seconds. A database driver that ignores the setting makes the statement fail with dbimp.ErrNotSupported.
type PrimaryKeyInfo ¶
type PrimaryKeyInfo struct {
Catalog string
Schema string
Table string
Column string
// Sequence is the position of the column in the key, from 1.
Sequence int
// Name is the name of the key, when the database has one.
Name string
}
PrimaryKeyInfo is a column of the primary key that SQLPrimaryKeys lists.
Source Files