The SingleStore ODBC Driver

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 and libiodbc libraries, which are not available natively. You can get these libraries with Homebrew.

Install Homebrew. For a basic installation, run the following command in the terminal:

/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:

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

   Copy

   cat /Library/ODBC/odbcinst.ini

2. Verify that the output contains the following lines:

   Copy

   [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

[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 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:

   Copy

   Library/ODBC/odbc.ini
   Library/ODBC/odbcinst.ini

2. Install the Homebrew (brew) package for x86_64.

   1. Start a shell in x86 mode:

      Shell

      Copy

      arch -x86_64 zsh

   2. Run the following commands in the x86 shell to install Homebrew:

      Shell

      Copy

      /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

      Copy

      (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

   Copy

   arch -x86_64 brew install openssl@1.1 libiodbc

4. After running the command, confirm that the following file exists:

   Copy

   /usr/local/opt/openssl@1.1/lib/libssl.1.1.dylib

5. Download the SingleStore ODBC Driver x86_64 package, for example singlestore-connector-odbc-1.1.4-osx-x86_64.pkg, and install it.

   Shell

   Copy

   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

   Copy

   cat /Library/ODBC/odbcinst.ini

2. Verify that the output contains the following lines:

   Copy

   [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

   Copy

   sudo yum update
   sudo yum install unixODBC

   Debian:

   Shell

   Copy

   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

      Copy

      sudo yum -y groupinstall "Development Tools"

   2. Download and extract the source code of OpenSSL 1.1:

      Shell

      Copy

      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

      Copy

      sudo ./config --prefix=/usr/local/openssl --openssldir=/usr/local/openssl

   4. Build OpenSSL:

      Shell

      Copy

      make -j $(nproc)

   5. Install OpenSSL:

      Shell

      Copy

      sudo make install

   6. Run the following command to verify that OpenSSL 1.1 is installed.

      Shell

      Copy

      openssl version

   Debian:

   Shell

   Copy

   sudo apt-get update
   wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/openssl_1.1.1f-1ubuntu2.20_amd64.deb
   sudo dpkg -i openssl_1.1.1f-1ubuntu2.20_amd64.deb
   wget http://archive.ubuntu.com/ubuntu/pool/main/o/openssl/libssl1.1_1.1.1f-1ubuntu2_amd64.deb
   sudo dpkg -i libssl1.1_1.1.1f-1ubuntu2_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

   Copy

   sudo yum update
   sudo yum install unixODBC-devel

   Debian:

   Shell

   Copy

   sudo apt-get update
   sudo apt-get install unixodbc-dev

4. Download the binary tarball 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

   Copy

   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).

      Copy

      [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

      Copy

      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).

      Copy

      [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

      Copy

      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

   Copy

   isql SingleStore-server

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 for more information.

On Windows

To configure Kerberos-based authentication on Windows:

1. Download 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 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 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:

   Copy

   [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

   Copy

   kinit <username>

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

   Shell

   Copy

   isql -v SingleStore-server

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:

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:

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.

#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:

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:

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.

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:

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()