# 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](https://docs.slingdata.io/sling-cli/cli-pro) or [Platform Plan](https://docs.slingdata.io/sling-platform/platform).
{% 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](https://docs.slingdata.io/concepts/replication/templates) 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/sling-cli/environment#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).