# The SingleStore ODBC Driver

The SingleStore ODBC driver ("the driver") provides a simple and optimized way to connect to your SingleStore databases with ODBC-based programs, like BI & analytics tools, ETL & data integration tools, and bespoke applications. It supports both Unicode and ANSI modes.

The SingleStore ODBC driver is compatible with SingleStore version 7.3+.

## Getting Started with the SingleStore ODBC Driver

The SingleStore ODBC driver supports both Unicode and ANSI modes. To get started with the SingleStore ODBC driver, make sure you are using a supported operating system.

## Prerequisites

The SingleStore ODBC driver is compatible with the following 64-bit platforms:

* macOS (tested on 10.14.4)
* Windows 10
* CentOS 7 and 8 / Debian 10 and 11

## Download the SingleStore ODBC Driver

Download the SingleStore ODBC driver binaries for each supported operating system from its [GitHub](https://github.com/memsql/singlestore-odbc-connector/releases/latest) repository.

## Install the SingleStore ODBC Driver

The SingleStore ODBC driver has two versions: Unicode-enabled or ANSI. Handle data using any character set through the Unicode-enabled driver, or opt for maximum raw speed for a more limited range of character sets through the ANSI driver.

Both versions of drivers are provided in each download package, and are both installed onto your system by the installation program or script that comes with the download package. When you install the SingleStore ODBC driver and register it to the ODBC manager manually, you can choose to install and register either one or both of the drivers. The different drivers are identified by a `w` (for “wide characters”) for the Unicode driver and `a` for the ANSI driver, at the end of the library names. For example, **ssodbcw\.dll** versus **ssodbca.dll**, or **libssodbcw\.so** versus **libssodbca.so**.

> **📝 Note**: You must specify `Unicode` or `ANSI` in the driver name in the configuration. Use `SingleStore ODBC Unicode Driver` for Unicode and `SingleStore ODBC ANSI Driver` for ANSI.

## Install on Windows

SingleStore provides an MSI package to install the SingleStore ODBC driver on Windows. Double-click the file and follow the prompts.

The SingleStore ODBC driver supports the built-in ODBC Driver Manager on Windows, so nothing else needs to be installed. The MSI installation process will even take care of registering the driver with the ODBC Driver Manager, so that it is ready to use immediately.

## Install on macOS

SingleStore provides a PKG package to install SingleStore ODBC Driver on Mac OS X.

The driver library requires the latest versions of [OpenSSL](https://www.openssl.org/) and [libiodbc](http://www.iodbc.org/) libraries, which are not available natively. You can get these libraries with [Homebrew](https://brew.sh/).

[Install](https://docs.brew.sh/Installation) Homebrew. For a basic installation, run the following command in the terminal:

```shell
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
```

To install the OpenSSL and `libiodbc` libraries, run the following command in the terminal:

```shell
brew install openssl@1.1 libiodbc 
```

Configure the data source in the `/Library/ODBC/odbc.ini` file in the following format:

```
[SingleStore-server]
Description=SingleStore server
Driver=SingleStore ODBC [Unicode|ANSI] Driver
SERVER=<ip_address_or_hostname_of_SingleStore_cluster>
USER=<username>
PASSWORD=<password>
DATABASE=<database>
PORT=<port>
```

Specify `Unicode` or `ANSI` in the value for `Driver` option.

To verify that the SingleStore ODBC driver is installed and ready to use:

1. Run the following command in the terminal:
   ```shell
   cat /Library/ODBC/odbcinst.ini
   ```

2. Verify that the output contains the following lines:
   ```
   [ODBC]
   [ODBC Drivers]
   SingleStore ODBC Unicode Driver = Installed
   SingleStore ODBC ANSI Driver = Installed

   [SingleStore ODBC Unicode Driver]
   Driver = /Library/SingleStore/SingleStore-Connector-ODBC/libssodbcw.dylib

   [SingleStore ODBC ANSI Driver]
   Driver = /Library/SingleStore/SingleStore-Connector-ODBC/libssodbca.dylib
   ```

> **⚠️ Warning**: If there is an error while registering the driver during the installation process, add the following lines to the `/Library/ODBC/odbcinst.ini` file:```
> [ODBC Drivers]
> SingleStore ODBC Unicode Driver = Installed
>
> [SingleStore ODBC Unicode Driver]
> Driver = /Library/SingleStore/SingleStore-Connector-ODBC/libssodbcw.dylib
> ```

## Install on macOS - Apple Silicon

To install the SingleStore ODBC Driver on an [Apple silicon](https://support.apple.com/en-us/116943) Mac running macOS:

1. (Optional) To clean up a previous installation, remove all the sections related to the SingleStore ODBC Driver from the following files:
   ```
   Library/ODBC/odbc.ini
   Library/ODBC/odbcinst.ini
   ```

2. Install the [Homebrew](https://brew.sh/) (`brew`) package for `x86_64`.

   1. Start a shell in `x86` mode:
      ```shell
      arch -x86_64 zsh
      ```

   2. Run the following commands in the x86 shell to install Homebrew:
      ```shell
      /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
      ```

   3. Add Homebrew (`brew`) to your `PATH`. In the output of the commands in the previous step, find the **==> Next Steps** section, and run the specified commands. For example (replace the `<username>` with your username):
      ```shell
      (echo; echo 'eval "$(/usr/local/bin/brew shellenv)"') >> /Users/<username>/.zprofile
      eval "$(/usr/local/bin/brew shellenv)"
      ```

3. Install the OpenSSL and libiodbc libraries.
   ```shell
   arch -x86_64 brew install openssl@1.1 libiodbc
   ```

4. After running the command, confirm that the following file exists:
   ```
   /usr/local/opt/openssl@1.1/lib/libssl.1.1.dylib
   ```

5. [Download](https://github.com/memsql/singlestore-odbc-connector/releases/latest) the SingleStore ODBC Driver `x86_64` package, for example `singlestore-connector-odbc-1.1.4-osx-x86_64.pkg`, and install it.
   ```shell
   sudo installer -pkg /path/to/package/singlestore-connector-odbc-1.1.4-osx-x86_64.pkg -target /
   ```

To verify that the SingleStore ODBC Driver is now installed, perform the following tasks:

1. Run the following command on the terminal:
   ```shell
   cat /Library/ODBC/odbcinst.ini
   ```

2. Verify that the output contains the following lines:
   ```
   [ODBC]
   [ODBC Drivers]
   SingleStore ODBC Unicode Driver = Installed
   SingleStore ODBC ANSI Driver = Installed

   [SingleStore ODBC Unicode Driver]
   Driver = /Library/SingleStore/SingleStore-Connector-ODBC/libssodbcw.dylib

   [SingleStore ODBC ANSI Driver]
   Driver = /Library/SingleStore/SingleStore-Connector-ODBC/libssodbca.dylib
   ```

## Install on Linux

1. Install UnixODBC.

   CentOS:
   ```shell
   sudo yum update
   sudo yum install unixODBC
   ```
   Debian:
   ```shell
   sudo apt-get update
   sudo apt-get install unixodbc
   ```

2. Install the `libssl.so.1.1` and `libcrypto.so.1.1` libraries.

   CentOS:

   1. Install the required dependencies:
      ```shell
      sudo yum -y groupinstall "Development Tools"
      ```

   2. [Download](https://www.openssl.org/source/old/1.1.1/index.html) and extract the source code of OpenSSL 1.1:
      ```shell
      wget https://www.openssl.org/source/openssl-1.1.1w.tar.gz
      tar xvf openssl-1.1.1w.tar.gz
      ```

   3. Navigate to the extracted directory, and run the following command:
      ```shell
      sudo ./config --prefix=/usr/local/openssl --openssldir=/usr/local/openssl
      ```

   4. Build OpenSSL:
      ```shell
      make -j $(nproc)
      ```

   5. Install OpenSSL:
      ```shell
      sudo make install
      ```

   6. Run the following command to verify that OpenSSL 1.1 is installed.
      ```shell
      openssl version
      ```

   Debian:

   1. Download the `libssl1.1_1.1.1f-1ubuntu<package_version>_amd64.deb` and `openssl_1.1.1f-1ubuntu<package_version>_amd64.deb` packages from <https://security.ubuntu.com/ubuntu/pool/main/o/openssl/>.

   2. Install the downloaded packages using the `dpkg` tool. Run the following commands from the directory where the packages are downloaded.

      **Note**: Update the `<package_version>` in the package name before running the commands.
      ```shell
      sudo dpkg -i libssl1.1_1.1.1f-1ubuntu<package_version>_amd64.deb
      sudo dpkg -i openssl_1.1.1f-1ubuntu<package_version>_amd64.deb
      ```

3. If you plan to compile an application from source against SingleStore ODBC driver and UnixODBC, then you also need the development header files that define the ODBC API function prototypes, ODBC data types, etc. To install UnixODBC development files you can use the following command:

   CentOS:
   ```shell
   sudo yum update
   sudo yum install unixODBC-devel
   ```
   Debian:
   ```shell
   sudo apt-get update
   sudo apt-get install unixodbc-dev

   ```

4. [Download the binary tarball](https://github.com/memsql/singlestore-odbc-connector/releases/latest) and extract the files. Run the following command to extract the files (update the name of the tarball with extension before executing the command):
   ```shell
   tar xzvf <singlestore-odbc-connector-tarball-name>
   ```

5. Configure the SingleStore ODBC driver as a UnixODBC driver on Linux.

   1. Create a template file similar to the following, and name it (for example, **SingleStore\_odbc\_driver\_template.ini**).
      ```
      [SingleStore ODBC [Unicode|ANSI] Driver]
      Description = SingleStore ODBC [Unicode|ANSI] Driver
      Driver = <your path>/libssodbc[a|w].so
      ```
      Be sure to specify the correct path to the driver and specify the corresponding name (`Unicode` or `ANSI`) and corresponding driver (`a` or `w`) to use. For Unicode, use "SingleStore ODBC Unicode Driver" and `libssodbcw.so`. For ANSI use "SingleStore ODBC ANSI Driver" and `libssodbca.so`.

   2. Run the following command to install the template file to the system's global `/etc/odbcinst.ini` file:
      ```shell
      sudo odbcinst -i -d -f SingleStore_odbc_driver_template.ini
      ```

6. Configure a DSN with UnixODBC on Linux.

   1. Create a template file similar to the following, and name it (for example, **SingleStore\_odbc\_data\_source\_template.ini**).
      ```
      [SingleStore-server]
      Description=SingleStore server
      Driver=SingleStore ODBC [Unicode|ANSI] Driver
      SERVER=<ip_address_or_hostname_of_SingleStore_cluster>
      USER=<username>
      PASSWORD=<password>
      DATABASE=<database>
      PORT=<port>
      ```
      Specify `Unicode` or `ANSI` in the value for `Driver=`.

   2. Run the following command to install the template file to the system's global `/etc/odbc.ini` file:
      ```shell
      sudo odbcinst -i -s -l -f SingleStore_odbc_data_source_template.ini
      ```

7. Use the `isql` utility to verify that the DSN is properly configured with UnixODBC on Linux.
   ```shell
   isql SingleStore-server
   ```

## Authenticate via JWTs

Before using JWTs to authenticate your connection to the SingleStore cluster using the SingleStore ODBC driver, you must install (or reinstall) the `glib*` and `libsecret*` libraries.

Run the following commands:

* Debian:
  ```shell
  sudo apt-get install glib*
  sudo apt-get install libsecret*
  ```
* CentOS:
  ```shell
  sudo yum install glib*
  sudo yum install libsecret*
  ```

## Using JWTs

You may authenticate your connection to the SingleStore cluster using the SingleStore ODBC driver with a JWT. To use JWT-based authentication, specify the JWT using the `JWT` parameter.

Here’s a sample connection string that authenticates using JWT:

```
"DRIVER=SingleStore ODBC Unicode Driver;SERVER=<cluster_endpoint>;USER=root;JWT=<jwt_token>;SSLCA=<path_to>/singlestore_bundle.pem"
```

See [Authenticate via JWTs](https://docs.singlestore.com/db/v9.1/security/authentication/authenticate-via-jwt.md) for more information.

## Authenticate via Kerberos

You can authenticate your connection to a SingleStore cluster via Kerberos-based authentication using the SingleStore ODBC driver. Refer to [Configuring the KDC](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication/configuring-the-kdc.md) and [Configuring SingleStore for Kerberos Authentication](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication/configuring-singlestore-for-kerberos-authentication.md) for related information.

## On Windows

To configure Kerberos-based authentication on Windows:

1. [Download](https://github.com/memsql/singlestore-odbc-connector/releases/latest) and install the SingleStore ODBC driver.

   Note that the installation directory (default "C:\Program Files (x86)\SingleStore\SingleStore ODBC Driver\plugin") contains the **auth\_gssapi\_client.dll** file.

2. Configure SingleStore for Kerberos authentication. Refer to [Kerberos Authentication](https://docs.singlestore.com/db/v9.1/security/authentication/kerberos-authentication.md) for more information.

3. Log in to the Kerberos user.

4. Configure a data source to connect with SingleStore.

   1. On the Start menu, select **Control Panel > Administrative Tools > Data Sources (ODBC)**.

   2. In the **Create new Data Source** dialog, select the **SingleStore ODBC Driver**, and then select **Finish**.

   3. In the **Create new Data Source to SingleStore** dialog, enter a name for the data source in the **Name** box. Select **Next**.

   4. In the **Server Name** box, enter the IP address or hostname for the server.

   5. Select **Test DSN** to test the connectivity. Use defaults for other configuration items.

      **Note**: Do not specify a username in the **User name**/**Password** field while configuring the data source.

## On Linux

To configure Kerberos-based authentication on Linux:

1. [Download](https://github.com/memsql/singlestore-odbc-connector/releases/latest) and install the latest version of the SingleStore ODBC driver.

2. Create an **odbc.ini** file for the current user to configure the data source, and add the following configuration:
   ```
   [server-name]
   Description = SingleStore Server
   Driver = SingleStore ODBC [Unicode|ANSI] Driver
   Database = <database-name>
   PLUGIN_DIR = <path_to_your_auth_gssapi_client.so_file>
   SERVER = <server-ip-address-or-hostname>
   USER = <username>
   PORT = 3306
   ```
   Update the values in the file with the relevant data source parameters. Once the `singlestore-client` package is installed, the **auth\_gssapi\_client.so** file is present in the **/var/lib/memsql/memsql-client/lib/plugin** directory. In this use case, we'll replace `server-name` with **SingleStore-server**.

3. Obtain a token for the user.
   ```shell
   kinit <username>
   ```

4. Test the data source **SingleStore-server** configured in the **odbc.ini** file using the `isql` command as follows:
   ```shell
   isql -v SingleStore-server
   ```

## Configuring the SingleStore ODBC Driver

On Windows, you can use the ODBC Data Source Administrator to set the configuration parameters.

On Unix and macOS, use the parameter name and value as the keyword/value pair in the DSN configuration. Alternatively, you can set these parameters within the `InConnectionString` argument in the `SQLDriverConnect()` call:

```c
SQLCHAR conn[512];

sprintf((char *)conn, "DRIVER=%s;SERVER=%s;DATABASE=%s;UID=%s;PASSWORD=%s;PORT=%d;NO_SSPS=%d;%s",
my_drivername, my_servername, my_schema, my_uid, my_pwd, my_port, NoSsps, add_connstr);

SQLRETURN result = SQLDriverConnect(hdbc1, NULL, conn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
```

The SingleStore ODBC driver supports the following parameters.

## DSN-Related Parameters

| Parameter     | Description                            |
| ------------- | -------------------------------------- |
| `DESCRIPTION` | Description of the data source.        |
| `DRIVER`      | The name of theSingleStoreODBC Driver. |
| `DSN`         | Name of the DSN.                       |

## General Connection Parameters

| Parameter             | Windows GUI Option                          | Default | Description                                                                                                                                                                                                                                                                            |
| --------------------- | ------------------------------------------- | ------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `USER`/`UID`          | User name                                   |         | The user name.                                                                                                                                                                                                                                                                         |
| `SERVER`/`SERVERNAME` | Server name                                 |         | The host name of theSingleStoreserver.                                                                                                                                                                                                                                                 |
| `DATABASE`/`DB`       | Database                                    |         | The default database.                                                                                                                                                                                                                                                                  |
| `OPTIONS`/`OPTION`    | --                                          | 0       | Options bitmask that specifies how driver/ODBC works (see[Option parameters](https://docs.singlestore.com/#section-idm4601953544251232534582330687.md)table below).                                                                                                                    |
| `PORT`                | Port                                        | 0       | The TCP/IP port to use. This is the server IP and is not localhost.                                                                                                                                                                                                                    |
| `INITSTMT`            | Statement(s)                                |         | A SQL statement to execute when connecting toSingleStore.                                                                                                                                                                                                                              |
| `PASSWORD`/`PWD`      | Password                                    |         | The password for the user account on server.                                                                                                                                                                                                                                           |
| `CHARSET`             | Connection Character Set                    |         | The character set to use for the connection. Valid values are`UTF8`and`binary`.                                                                                                                                                                                                        |
| `NO_SSPS`             | Prepare statements on the client side       | 1       | When this option is set to a non-zero value, prepared statements are emulated on the client side.                                                                                                                                                                                      |
| `NO_PROMPT`           | Don’t prompt when connecting                | 0       | Do not prompt for questions even if the driver would like to prompt.                                                                                                                                                                                                                   |
| `USE_MYCNF`           | Read ODBC section from`my.cnf`              | 0       | Read parameters from the`[client]`and`[odbc]`groups from`my.cnf`.                                                                                                                                                                                                                      |
| `AUTO_RECONNECT`      | Enable automatic\_reconnect                 | 0       | Enables the auto-reconnection functionality. Do not use this option with transactions, since an auto-reconnection during an incomplete transaction may cause corruption. An auto-reconnected connection will not inherit the same settings and environment as the original connection. |
| `FORWARDONLY`         | Force use of forward-only cursors           | 0       | Force the use of the Forward-only cursor type. When the application is setting the default static/dynamic cursor type and you wan the driver to use uncached result sets, this option ensures the forward-only cursor behavior.                                                        |
| `COMPAT_MODE`         | --                                          | 0       | Force the use of the Forward-only cursor type. In cases of applications setting the default static/dynamic cursor type and one wants the driver to use uncached result sets, this option ensures the forward-only cursor behavior.                                                     |
| `NO_CACHE`            | Don't cache results of forward-only cursors | 1       | Do not cache the results locally in the driver, instead read from the server. This works only for forward-only cursors. This option is very important in dealing with large tables when you do not want the driver to cache the entire result set.                                     |
| `APP`                 | --                                          |         | Name of the application that uses the driver.                                                                                                                                                                                                                                          |

## TLS-Related Connection Parameters

| Parameter                   | Windows GUI Option    | Default | Description                                                                                                                                                                                                                                                                                                        |
| --------------------------- | --------------------- | ------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `SSLCA`                     | SSL Certificate       |         | The path to a file with a list of trusted SSL CAs.                                                                                                                                                                                                                                                                 |
| `SSLCAPATH`                 | CA Path               |         | The path to a directory that contains trusted SSL CA certificates in PEM format.                                                                                                                                                                                                                                   |
| `SSLCERT`                   | Certificate           |         | The name of the SSL certificate file to use for establishing a secure connection.                                                                                                                                                                                                                                  |
| `SSLCIPHER`                 | Cipher                |         | The list of permissible ciphers for SSL encryption. The cipher list has the same format as the`openssl ciphers`command.                                                                                                                                                                                            |
| `SSLKEY`                    | Key                   |         | The name of the SSL key file to use for establishing a secure connection.                                                                                                                                                                                                                                          |
| `SSLVERIFY`                 | Verify Certificate    | 0       | If set to 1, the SSL certificate will be verified when used with theSingleStoreconnection. If not set, then the default behavior is to ignore SSL certificate verification.                                                                                                                                        |
| `TLSPEERFP`/`SSLFP`         | TLS Peer Fingerprint  |         | Specify the SHA1 fingerprint of a server certificate for validation during the TLS handshake.                                                                                                                                                                                                                      |
| `TLSPEERFPLIST`/`SSLFPLIST` | Fingerprint list file |         | Specify a file which contains one or more SHA1 fingerprints of server certificates for validation during the TLS handshake.                                                                                                                                                                                        |
| `SSLCRL`                    | CRL File              |         | Defines a path to a PEM file that should contain one or more revoked X509 certificates to use for TLS. This parameter requires that you use the absolute path, not a relative path.                                                                                                                                |
| `SSLCRLPATH`                | --                    |         | Defines a path to a directory that contains one or more PEM files that should each contain one revoked X509 certificate to use for TLS. This parameter requires that you use the absolute path, not a relative path. The directory specified by this parameter needs to be run through the`openssl rehash`command. |
| `FORCETLS`                  | Force TLS Use         | 0       | Whether to force TLS.                                                                                                                                                                                                                                                                                              |
| `TLSKEYPWD`                 | --                    |         | Specify a passphrase for a passphrase-protected private key, as configured by the SSLKEY option. (Not supported by Windows version of theSingleStoreODBC driver.)                                                                                                                                                  |

## Option Parameters

You can pass these parameters via the `OPTIONS` general connection parameter. Here's an example of [how to use the bitmask to specify multiple option parameters](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-odbc/the-singlestore-odbc-driver/#example-code-2.md).

> **📝 Note**: If the `OPTIONS` parameter is specified after a general parameter in the connection string, the value specified using the `OPTIONS` parameter overrides the general parameter value. For example, `DSN=mu_dsh;NO_CACHE=1;OPTIONS=16` results in a connection that has `NO_CACHE` disabled.

| Parameter          | Windows GUI Option                        | Constant Value  | Description                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| ------------------ | ----------------------------------------- | --------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `FOUND_ROWS`       | Found instead of affected rows            | 2 (2^1)         | If this parameter is set, the server returns the number of matched rows to be updated instead of the number of actually changed rows.                                                                                                                                                                                                                                                                                                                                                                                        |
| `NO_PROMPT`        | Don’t prompt when connecting              | 16 (2^4)        | Do not prompt for questions even if the driver would like to prompt.                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `DYNAMIC_CURSOR`   | Enable dynamic cursor                     | 32 (2^5)        | Enable or disable the dynamic cursor support.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| `USE_MYCNF`        | Read ODBC section from`my.cnf`            | 65536 (2^16)    | Read parameters from the`[client]`and`[odbc]`groups from`my.cnf`.                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `NO_CACHE`         | Don’t cache result of forward only cursor | 1048576 (2^20)  | Do not cache the results locally in the driver, instead read from the server. This works only for forward-only cursors. This option is very important in dealing with large tables when you do not want the driver to cache the entire result set.                                                                                                                                                                                                                                                                           |
| `FORWARDONLY`      | Force use of forward-only cursor          | 2097152 (2^21)  | Force the use of the Forward-only cursor type. In cases of applications setting the default static/dynamic cursor type and one wants the driver to use uncached result sets, this option ensures the forward-only cursor behavior.                                                                                                                                                                                                                                                                                           |
| `AUTO_RECONNECT`   | Enable automatic\_reconnect               | 4194304 (2^22)  | Enables auto-reconnection functionality. Do not use this option with transactions, since an auto-reconnection during an incomplete transaction may cause corruption. An auto-reconnected connection will not inherit the same settings and environment as the original connection.                                                                                                                                                                                                                                           |
| `MULTI_STATEMENTS` | Allow multiple statements                 | 67108864 (2^26) | Enables support for batched statements.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `CONN_ATTRS`       |                                           |                 | Specifies a comma separated list of connection attributes in the`key:value`pair format to identify drivers and applications while monitoring yourSingleStoreclusters.This parameter has different identifiers depending on the application development tool/language that it is used with. Refer to[Specify Connection Attributes](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-odbc/the-singlestore-odbc-driver/#example-code-3.md)for more information |

## Example Code

The following examples show how to use the SingleStore ODBC driver.

## Read a Result Set

To read a large result set, use the `NO_CACHE` option together with `Forward-Only cursor`. With these options, rows are fetched one-by-one from the server. Without them, all rows will be fetched after calling `SQLExecute` or `SQLExecuteDirect`.

```c
// Enable UTF8
setlocale(LC_CTYPE, "");

// Allocate environment handle
SQLHANDLE Henv;
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &Henv);

// Set odbc version
SQLSetEnvAttr(Henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

// Allocate connection handle
SQLHANDLE Hdbc;
SQLAllocHandle(SQL_HANDLE_DBC, Henv, &Hdbc);
// Connect to the data source
// NO_CACHE option and Forward-Only cursor are enabled by default
SQLDriverConnect(Hdbc,
                NULL,
                (SQLCHAR *)"DSN=my_dsn_name",
                SQL_NTS,
                NULL,
                0,
                NULL,
                SQL_DRIVER_NOPROMPT);

// Allocate statement handle
SQLHANDLE Hstmt;
SQLAllocHandle(SQL_HANDLE_STMT, Hdbc, &Hstmt);

// Create big table
SQLExecDirect(Hstmt, (SQLCHAR *)"CREATE TABLE big_table(a INT)", SQL_NTS);
SQLExecDirect(Hstmt, (SQLCHAR *)"INSERT INTO big_table VALUES(1)", SQL_NTS);
int i;
for (i = 0; i < 20; i++)
{
 SQLExecDirect(Hstmt, (SQLCHAR *)"INSERT INTO big_table (SELECT (a+1) FROM big_table)", SQL_NTS);
}

// Select all data from big table
SQLExecDirect(Hstmt, (SQLCHAR *)"SELECT * FROM big_table", SQL_NTS);

// Bind column
SQLINTEGER a;
SQLBindCol(Hstmt, 1, SQL_C_LONG, &a, 0, NULL);

// Fetch everything from the resultset
// Data is not cached on the driver side after calling SQLExecDirect
// and each row is retrieved during SQLFetch
while(SQLFetch(Hstmt) != SQL_NO_DATA)
{
 printf("Fetched value: %d\n", a);
}
```

## Executing Multi-statement Queries

To execute multi-statement queries, the `MULTI_STATEMENTS` option should be enabled. You can see that specified in the code via the mask: `OPTION=67108864`

Also, execution of multi-statement queries is not supported with `NO_CACHE` and `Forward-Only cursor`. Multi-statement execution is not fully supported with `NO_SSPS` enabled. This means that if you try to prepare a multi-statement query where one of the statements depends on the execution result of one of the previous statements, then it may fail.

```c
// Allocate environment handle
SQLHANDLE Henv;
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &Henv);

// Set odbc version
SQLSetEnvAttr(Henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

// Allocate connection handle
SQLHANDLE Hdbc;
SQLAllocHandle(SQL_HANDLE_DBC, Henv, &Hdbc);
// Connect to the data source
// Disable NO_CACHE and enable MULTI_STATEMENTS option
// Disable NO_SSPS to be able to run queries that depends on each other
SQLDriverConnect(Hdbc,
                NULL,
                (SQLCHAR *)"DSN=my_dsn_name;NO_CACHE=0;OPTION=67108864;NO_SSPS=0",
                SQL_NTS,
                NULL,
                0,
                NULL,
                SQL_DRIVER_NOPROMPT);

// Allocate statement handle
SQLHANDLE Hstmt;
SQLAllocHandle(SQL_HANDLE_STMT, Hdbc, &Hstmt);

// Execute multi-statement
SQLExecDirect(Hstmt, (SQLCHAR *)"CREATE TABLE multi_statement(a INT);"
                                "INSERT INTO multi_statement VALUES (1), (2), (3), (4);"
                                "SELECT a FROM multi_statement;"
                                "SELECT a+1 FROM multi_statement;", SQL_NTS);

// Process the result of the second statement (INSERT INTO multi_statement VALUES(1))
SQLMoreResults(Hstmt);
printf("Processing the result of the second statement\n");

// Get the number of inserted rows
SQLLEN rowCount;
SQLRowCount(Hstmt, &rowCount);
printf("Inserted %ld rows\n", rowCount);

// Process the result of the third statement (SELECT a FROM multi_statement)
SQLMoreResults(Hstmt);
printf("Processing the result of the third statement\n");

// Bind column
SQLINTEGER a;
SQLBindCol(Hstmt, 1, SQL_C_LONG, &a, 0, NULL);

// Fetch everything from the resultset
while(SQLFetch(Hstmt) != SQL_NO_DATA)
{
 printf("Fetched value: %d\n", a);
}

// Process the result of the fourth statement (SELECT a+1 FROM multi_statement)
SQLMoreResults(Hstmt);
printf("Processing the result of the fourth statement\n");

// Fetch everything from the resultset
while(SQLFetch(Hstmt) != SQL_NO_DATA)
{
 printf("Fetched value: %d\n", a);
}
```

## Retrieve Information about the Result Set's Schema

To retrieve information about the schema of the result set after the SQL statement is prepared and before it is executed, `NO_SSPS` should be disabled. Otherwise, the driver returns an incorrect schema, which can break some applications.

```c
// Enable UTF8
setlocale(LC_CTYPE, "");


// Allocate environment handle
SQLHANDLE Henv;
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &Henv);

// Set odbc version
SQLSetEnvAttr(Henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

// Allocate connection handle
SQLHANDLE Hdbc;
SQLAllocHandle(SQL_HANDLE_DBC, Henv, &Hdbc);
// Connect to the data source
// Disable NO_SSPS to be able to get number of columns before executing the statement
SQLDriverConnect(Hdbc,
                NULL,
                (SQLCHAR *)"DSN=my_dsn_name;NO_SSPS=0",
                SQL_NTS,
                NULL,
                0,
                NULL,
                SQL_DRIVER_NOPROMPT);

// Allocate statement handle
SQLHANDLE Hstmt;
SQLAllocHandle(SQL_HANDLE_STMT, Hdbc, &Hstmt);

// Prepare statement
SQLPrepare(Hstmt, (SQLCHAR *)"SELECT 1, 2", SQL_NTS);

// Get number of columns
SQLSMALLINT colNum;
SQLNumResultCols(Hstmt, &colNum);
printf("Number of columns: %d\n", colNum);

// Execute statement
SQLExecute(Hstmt);
```

## Using the Option Parameter

The following example shows how to pass multiple option parameters via a bitmask:

```c
// Allocate environment handle
SQLHANDLE Henv;
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &Henv);

// Set odbc version
SQLSetEnvAttr(Henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

// Allocate connection handle
SQLHANDLE Hdbc;
SQLAllocHandle(SQL_HANDLE_DBC, Henv, &Hdbc);
// Connect to the data source
// The following options are enabled
// FOUND_ROWS - 2
// NO_PROMPT - 16
// DYNAMIC_CURSOR - 32
// AUTO_RECONNECT - 4194304
// 2 + 16 + 32 + 4194304 = 4194354
SQLDriverConnect(Hdbc,
                NULL,
                (SQLCHAR *)"DSN=my_dsn_name;OPTIONS=4194354",
                SQL_NTS,
                NULL,
                0,
                NULL,
                SQL_DRIVER_NOPROMPT);
```

## Specify Connection Attributes

Here are some examples that specify connection attribute identifiers and their usage in respective tools/languages.

## C (MySQL API)

To specify the connection attributes, call the `mysql_options4` function before the `mysql_real_connect` function. In the `mysql_options4` function call, specify `MYSQL_OPT_CONNECT_ATTR_ADD` as the second argument and pass the attribute key and value as the third and fourth arguments, respectively. Here's an example:

```C
MYSQL *con;
con = mysql_init(NULL);

mysql_options4(con, MYSQL_OPT_CONNECT_ATTR_ADD, "program_name", "Test C app");
mysql_options4(con, MYSQL_OPT_CONNECT_ATTR_ADD, "program_version", "1.2.3");

mysql_real_connect(con, "<hostname>", "<username>", "pa55w0rd", "<database>", 3306, NULL, 0);
```

## C (ODBC)

You can specify a comma-separated list of key-value pairs using the `CONN_ATTRS` parameter in the connection string as follows:

```C
SQLHENV env;
SQLHDBC dbc;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN */
SQLCHAR conn[1024];
const char *attrs = "CONN_ATTRS=program_name:My ODBC App,program_version:1.2.3";

sprintf((char *)conn, "DRIVER=SingleStore ODBC Unicode Driver;SERVER=<hostname>;UID=<username>;PASSWORD=pa55w0rd;PORT=3306;%s", attrs);

SQLRETURN result = SQLDriverConnect(dbc, NULL, conn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
```

## C++

Use the `OPT_CONNECT_ATTR_ADD` option to pass the connection attributes.

```C++
#include <mysql_driver.h>
#include <mysql_connection.h>
#include <mysql_error.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>


int main()
{
   sql::mysql::MySQL_Driver *driver;
   sql::Connection *con;
   sql::ConnectOptionsMap connection_properties;

   connection_properties["hostName"] = "<hostname>";
   connection_properties["userName"] = "<username>";
   connection_properties["password"] = "pa55w0rd";
   connection_properties["port"] = 3306;

   std::map<sql::SQLString, sql::SQLString> connect_attrs;
   connect_attrs["program_name"] = "My C++ App";
   connect_attrs["program_version"] = "1.2.3";
   connection_properties["OPT_CONNECT_ATTR_ADD"] = connect_attrs;
   driver = sql::mysql::get_mysql_driver_instance();

   con = driver->connect(connection_properties);
   // Create a statement object
   auto stmt = con->createStatement();

   // Execute a simple query
   auto res = stmt->executeQuery("SELECT 1");
}
```

## C#/.NET

You can specify the connection attributes using the `connectionAttributes` parameter as follows:

```C#
using SingleStoreConnector;

public static class Program
{
   public static void Main()
   {
       string HOST = "<hostname>";
       int PORT = 3306;
       string USER = "<username>";
       string PASSWORD = "paw0rd";
       string DATABASE = "db";
       string CONN_ATTRS = "program_name:My .NET App,program_version:1.2.3";
       var connectionString = $"Server={HOST};Port={PORT};Uid={USER};Pwd={PASSWORD};Database={DATABASE};ConnectionAttributes={CONN_ATTRS}";
       var conn = new SingleStoreConnection(connectionString);
       conn.Open();
   }
}
```

## Python

You can specify a comma-separated list of key-value pairs using the `conn_attrs` parameter in the connection string as follows:

```Python
import singlestoredb as s2

host = '<hostname>'
user = '<username>'
password = 'pa55w0rd'

conn = s2.connect(
  user=user,
  host=host,
  password=password,
  conn_attrs={"program_name": "My Python App", "program_version": "1.2.3"}
)
```

## Go

You can pass the connection attributes in key-value pairs using the `connectionAttributes` parameter. Specify the values in a map, if used in a connection `Config` struct.

```Go
package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
)

func main() {
   user := "<username>"
   host := "<hostname>"
   password := "pa55w0rd"
   port := 3306
   connParams := "connectionAttributes=program_name:My Go App,program_version:1.2.3"
   dbName := "information_schema"

   connString := fmt.Sprintf(
       "%s:%s@tcp(%s:%d)/%s?%s",
       user,
       password,
       host,
       port,
       dbName,
       connParams,
   )
   db, err := sql.Open("mysql", connString)
   fmt.Printf("Created connection to db, got error %v\n", err)
   fmt.Printf("Tested connection to db, got error %v\n", db.Ping())
}
```

## Node.js

You can specify the connection attributes in the `createConnection` and `createPool` functions using the `connectAttributes` argument. In the `connectAttributes` argument, the properties and their values represent the connection attributes and their values, respectively. Here's an example:

```javascript
import mysql from 'mysql2/promise';

const HOST = '<hostname>';
const USER = '<username>';
const PASSWORD = 'pa55w0rd';
const DATABASE = 'testdb';


async function main() {
 let singleStoreConnection = await mysql.createConnection({
     host: HOST,
     user: USER,
     password: PASSWORD,
     database: DATABASE,
     connectAttributes: {
       program_name: 'My NodeJS App',
       program_version: '1.2.3'},
   });
 singleStoreConnection.execute("SELECT 1");
 singleStoreConnection.end();
}

main()
```

## Known Issues and Limitations

Refer to the [GitHub repository](https://github.com/memsql/singlestore-odbc-connector) for more detailed information.

## License

SINGLESTORE, INC. ("SINGLESTORE") AGREES TO GRANT YOU AND YOUR COMPANY ACCESS TO THIS OPEN SOURCE SOFTWARE CONNECTOR AND PROVIDE SUPPORT & MAINTENANCE OR OTHER SERVICES ONLY IF (A) YOU AND YOUR COMPANY REPRESENT AND WARRANT THAT YOU, ON BEHALF OF YOUR COMPANY, HAVE THE AUTHORITY TO LEGALLY BIND YOUR COMPANY AND (B) YOU, ON BEHALF OF YOUR COMPANY ACCEPT AND AGREE TO BE BOUND BY ALL OF THE OPEN SOURCE TERMS AND CONDITIONS APPLICABLE TO THIS OPEN SOURCE CONNECTOR AS SET FORTH BELOW (THIS “AGREEMENT”), WHICH SHALL BE DEFINITIVELY EVIDENCED BY ANY ONE OF THE FOLLOWING MEANS: YOU, ON BEHALF OF YOUR COMPANY, CLICKING THE “DOWNLOAD, “ACCEPTANCE” OR “CONTINUE” BUTTON, AS APPLICABLE OR COMPANY’S INSTALLATION, ACCESS OR USE OF THE OPEN SOURCE CONNECTOR AND SHALL BE EFFECTIVE ON THE EARLIER OF THE DATE ON WHICH THE DOWNLOAD, ACCESS, COPY OR INSTALL OF THE CONNECTOR OR USE ANY SERVICES (INCLUDING ANY UPDATES OR UPGRADES) PROVIDED BY SINGLESTORE.

APPLICABLE OPEN SOURCE LICENSE: GNU LESSER GENERAL PUBLIC LICENSE Version 2.1

IF YOU OR YOUR COMPANY DO NOT AGREE TO THESE TERMS AND CONDITIONS, DO NOT CHECK THE ACCEPTANCE BOX, AND DO NOT DOWNLOAD, ACCESS, COPY, INSTALL OR USE THE SOFTWARE OR THE SERVICES.

***

Modified at: January 30, 2025

Source: [/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-odbc/the-singlestore-odbc-driver/](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-odbc/the-singlestore-odbc-driver/)

(An index of the documentation is available at /llms.txt)