import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';
ODBC (Open Database Connectivity) is a low-level, high-performance interface that is designed specifically for relational data stores as a standard way to connect to, and interact with a database.
Spice supports ODBC clients through an ODBC driver implementation based on the Flight SQL protocol. This enables ODBC-compatible applications to connect to Spice, execute queries, and retrieve data.
:::warning[Limitations]
:::
- Ensure that `unixODBC` is installed. To verify whether `unixODBC` is installed, execute the following commands: ```shell which odbcinst which isql ``` - Copy the content of the `odbc.ini` and `odbcinst.ini` from the `/opt/arrow-flight-sql-odbc-driver/conf` and paste into your system `/etc/odbc.ini` and `/etc/odbcinst.ini` files - Edit `odbc.ini`: specify Spice.ai OSS runtime `HOST`, `PORT`, in the `UseEncryption` field, specify one of these values: - `true`, if [Spice is configured for encrypted communication (TLS)](https://docs.spiceai.org/api/tls) - `false`, otherwise - For descriptions of all the parameters, see [ODBC Connection Parameters](#odbc-connection-parameters) - Run this command to verify `unixODBC` configuration ```shell odbcinst -j ```
- Ensure that [ODBC Manager](http://www.odbcmanager.net/) is installed. - Launch **ODBC Manager** -> **System DSN** page, select **Arrow Flight SQL ODBC DSN** and click **Configure**. - Specify Spice.ai OSS runtime `HOST`, `PORT`, in the `UseEncryption` field, specify one of these values: - `true`, if [Spice is configured for encrypted communication (TLS)](https://docs.spiceai.org/api/tls) - `false`, otherwise <img width="600" alt="ODBC Data Source Administrator" src="/img/odbc/spice-odbc-macos-config.png" /> - For descriptions of all the parameters, see [ODBC Connection Parameters](#odbc-connection-parameters).
| Name | Type | Description |
|---|---|---|
| host | string | The IP address or hostname for the Spice runtime. |
| port | integer | The Spice runtime Arrow Flight endpoint port number |
| useEncryption | integer | Configures the driver to use an SSL-encrypted connection. Accepted values: true (default) - The client communicates with the Spice runtime only using SSL encryption and false - SSL encryption is disabled. |
| disableCertificateVerification | integer | Specifies whether the driver should verify the host certificate against the trust store. Default is false |
| useSystemTrustStore | integer | Controls whether to use a CA certificate from the system's trust store, or from a specified .pem file. If true - The driver verifies the connection using a certificate in the system trust store. IF false - The driver verifies the connection using the .pem file specified by the trustedCerts parameter. true on Windows and macOS, false on Linux by default |
| trustedCerts | string | The full path of the .pem file containing certificates trusted by a CA, for the purpose of verifying the server. If this option is not set, then the driver defaults to using the trusted CA certificates .pem file installed by the driver. |
:::note
The ODBC driver for Arrow Flight SQL does not support password-protected .pem/.crt files or multiple .crt certificates in a single .pem/.crt file.
:::
Ensure Spice runtime has started:
Configure the client app to use Arrow Flight SQL ODBC Driver.
Run a sample query, such as
Spice supports parameterized queries with ODBC. Parameterized queries help prevent SQL injection and improve code clarity by separating query logic from data values.
