# ODBC

Open Database Connectivity (ODBC) provides a standardized interface for accessing various databases using ODBC drivers. This connector enables data extraction from databases that may not have a dedicated Sling connector, or when ODBC is the preferred access method. This is available starting *v1.5.4*.

{% hint style="success" %}
**CLI Pro Required**: ODBC connections require a [CLI Pro token](/sling-cli/cli-pro.md) or [Platform Plan](/sling-platform/platform.md).
{% endhint %}

## Setup

Before using ODBC connections, ensure you have the appropriate ODBC driver installed for your database:

**macOS (via Homebrew):**

```bash
# SQL Server
brew install microsoft/mssql-release/msodbcsql18

# PostgreSQL
brew install psqlodbc

# MySQL
brew install mysql-connector-odbc
```

**Linux (Debian/Ubuntu):**

```bash
# SQL Server
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list)"
sudo apt-get update
sudo apt-get install msodbcsql18

# PostgreSQL
sudo apt-get install odbc-postgresql

# MySQL
sudo apt-get install libmyodbc
```

**Windows:** Download and install the appropriate ODBC driver from the vendor's website.

## Connection Properties

* `conn_string` **(required)** -> The ODBC connection string (DSN or DSN-less format)
* `conn_template` (optional) -> Specify the SQL template name to use for query syntax. This should be the name of a template (e.g., `sqlserver`, `postgres`, `mysql`, `db2`, `sap_hana`). If not provided, Sling auto-detects from the connection string.

### Custom Templates for Unsupported Drivers

For ODBC drivers that connect to databases not natively supported by Sling (e.g., DB2, SAP HANA, Teradata), you can create your own custom template:

1. Create a template file at `~/.sling/templates/{template_name}.yaml` (e.g., `~/.sling/templates/db2.yaml`)
2. Define the SQL syntax and type mappings specific to your database (see [Template Overrides](/concepts/replication/templates.md) for the template structure)
3. Reference the template name in your connection's `conn_template` property

For example, to connect to DB2 via ODBC:

```yaml
# ~/.sling/templates/db2.yaml
core:
  limit: select {fields} from {table}{where_clause} fetch first {limit} rows only

metadata:
  schemas: |
    select schemaname as schema_name
    from syscat.schemata
    order by schemaname

  tables: |
    select tabschema as schema_name, tabname as table_name, 'false' as is_view
    from syscat.tables
    where type = 'T'
      {{if .schema -}} and tabschema = '{schema}' {{- end}}
    order by tabname

  columns: |
    select colname as column_name, typename as data_type
    from syscat.columns
    where tabschema = '{schema}' and tabname = '{table}'
    order by colno

general_type_map:
  string: varchar(255)
  integer: integer
  bigint: bigint
  decimal: decimal(38,10)
  float: double
  bool: smallint
  date: date
  datetime: timestamp
  text: clob
```

Then reference it in your connection:

```yaml
connections:
  MY_DB2:
    type: odbc
    conn_string: "Driver={IBM DB2 ODBC DRIVER};Database=mydb;Hostname=localhost;Port=50000;Protocol=TCPIP;Uid=myuser;Pwd=mypassword"
    conn_template: db2
```

## Using `sling conns`

{% code overflow="wrap" %}

```bash
# SQL Server via ODBC (DSN-less)
sling conns set MY_ODBC type=odbc conn_string='Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes'

# PostgreSQL via ODBC
sling conns set MY_ODBC type=odbc conn_string='Driver={PostgreSQL};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword'

# Using DSN
sling conns set MY_ODBC type=odbc conn_string='DSN=MyDataSource;UID=myuser;PWD=mypassword'

# With explicit template override
sling conns set MY_ODBC type=odbc conn_string='DSN=MyDSN' conn_template=sqlserver
```

{% endcode %}

## Environment Variable

{% code overflow="wrap" %}

```bash
# SQL Server via ODBC
export MY_ODBC='{ type: odbc, conn_string: "Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes" }'

# PostgreSQL via ODBC
export MY_ODBC='{ type: odbc, conn_string: "Driver={PostgreSQL};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword" }'

# With explicit template
export MY_ODBC='{ type: odbc, conn_string: "DSN=MyDSN", conn_template: sqlserver }'
```

{% endcode %}

## Sling Env File YAML

See [here](https://docs.slingdata.io/connections/database-connections/pages/eAdVs2BHCgdr6RS8GoJC#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
connections:
  # SQL Server via ODBC (DSN-less)
  MSSQL_ODBC:
    type: odbc
    conn_string: "Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes"

  # PostgreSQL via ODBC
  POSTGRES_ODBC:
    type: odbc
    conn_string: "Driver={PostgreSQL};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword"

  # Using DSN
  MY_DSN:
    type: odbc
    conn_string: "DSN=MyDataSource;UID=myuser;PWD=mypassword"

  # With built-in template for SQL dialect
  CUSTOM_DB:
    type: odbc
    conn_string: "DSN=MyCustomDSN"
    conn_template: sqlserver

  # With custom template (create ~/.sling/templates/db2.yaml first)
  MY_DB2:
    type: odbc
    conn_string: "Driver={IBM DB2 ODBC DRIVER};Database=mydb;Hostname=localhost;Port=50000;Protocol=TCPIP;Uid=myuser;Pwd=mypassword"
    conn_template: db2
```

## Replication Example

Here's an example replication configuration to extract data from an ODBC source to PostgreSQL:

```yaml
source: MSSQL_ODBC
target: MY_POSTGRES

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  departments:

  # Extract using SQL query
  orders:
    sql: SELECT * FROM dbo.orders
    object: public.orders

  # Extract specific columns
  customers:
    sql: SELECT id, name, email FROM dbo.customers
    object: public.customers
```

## Connection String Formats

### DSN (Data Source Name)

Use a pre-configured DSN from your ODBC configuration:

```
DSN=MyDataSource;UID=myuser;PWD=mypassword
```

### DSN-less (Driver-based)

Connect directly without a DSN:

**SQL Server:**

```
Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;TrustServerCertificate=yes
```

**PostgreSQL:**

```
Driver={PostgreSQL};Server=localhost;Port=5432;Database=mydb;UID=myuser;PWD=mypassword
```

**MySQL:**

```
Driver={MySQL ODBC 8.0 Driver};Server=localhost;Port=3306;Database=mydb;UID=myuser;PWD=mypassword
```

## Troubleshooting

### Driver Not Found

If you receive a "driver not found" error, verify that:

1. The ODBC driver is installed
2. The driver name in your connection string matches exactly (including brackets and version)
3. On Linux/macOS, check `/etc/odbcinst.ini` for installed drivers

### SQL Syntax Errors

If you encounter SQL syntax errors (e.g., `LIMIT` vs `TOP`), ensure:

1. The driver type is being correctly auto-detected from your connection string
2. Or explicitly set `conn_template` to match your database type

### Connection Timeout

For slow connections, you may need to add timeout parameters to your connection string:

```
Driver={ODBC Driver 18 for SQL Server};Server=localhost;Database=mydb;UID=myuser;PWD=mypassword;Connection Timeout=30
```

If you are facing issues connecting, please reach out to us at <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.slingdata.io/connections/database-connections/odbc.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.