The SingleStore ODBC Driver
The SingleStore ODBC driver provides a simple and optimized way to connect to your SingleStoreDB 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 SingleStoreDB 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 connector binaries for each supported operating system from its GitHub 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 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
To verify that the SingleStore ODBC driver is installed and ready to use:
Run the following command in the terminal:
cat /Library/ODBC/odbcinst.ini
Verify that the output contains the following lines:
SingleStore ODBC Unicode Driver = Installed [SingleStore ODBC Unicode Driver] Driver = /Library/SingleStore/SingleStore-Connector-ODBC/libssodbcw.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 Linux
Install UnixODBC and OpenSSL.
Install the UnixODBC package:
CentOS:
% sudo yum update % sudo yum install unixODBC % sudo yum install openssl-devel
Debian:
% sudo apt-get update % sudo apt-get install unixodbc % sudo apt-get install libssl-dev
If you plan to compile an application from source against SingleStore ODBC Connector 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:
% sudo yum update % sudo yum install unixODBC-devel
Debian:
% sudo apt-get update % sudo apt-get install unixodbc-dev
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):
% tar xzvf <singlestore-odbc-connector-tarball-name>
Configure the SingleStore ODBC connector as a UnixODBC driver on Linux.
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
orANSI
) and corresponding driver (a
orw
) to use. For Unicode, use "SingleStore ODBC Unicode Driver" andlibssodbcw.so
. For ANSI use "SingleStore ODBC ANSI Driver" andlibssodbca.so
.Run the following command to install the template file to the system's global
/etc/odbcinst.ini
file:% sudo odbcinst -i -d -f SingleStore_odbc_driver_template.ini
Configure a DSN with UnixODBC on Linux.
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=<your server> USER=<your user> PASSWORD=<your password> DATABASE=<your database> PORT=<your port>
Specify
Unicode
orANSI
in the value forDriver=
.Run the following command to install the template file to the system's global
/etc/odbc.ini
file:% sudo odbcinst -i -s -l -f SingleStore_odbc_data_source_template.ini
Use the
isql
utility to verify that the DSN is properly configured with UnixODBC on Linux.% isql SingleStore-server
Authenticate via JWTs
Before using JWTs to authenticate your connection to the SingleStoreDB cluster using the SingleStore ODBC driver, you must install (or reinstall) the glib*
and libsecret*
libraries.
Run the following commands:
Debian:
sudo apt-get install glib* sudo apt-get install libsecret*
CentOS:
sudo yum install glib* sudo yum install libsecret*
Using JWTs
You may authenticate your connection to the SingleStoreDB 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.
Authenticate via Kerberos
You can authenticate your connection to a SingleStoreDB cluster via Kerberos-based authentication using the SingleStore ODBC driver. Refer to Configuring the KDC and Configuring SingleStoreDB for Kerberos Authentication for related information.
On Windows
To configure Kerberos-based authentication on Windows:
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.
Configure SingleStoreDB for Kerberos authentication. Refer to Kerberos Authentication for more information.
Log in to the Kerberos user.
Configure a data source to connect with SingleStoreDB.
On the Start menu, select Control Panel > Administrative Tools > Data Sources (ODBC).
In the Create new Data Source dialog, select the SingleStore ODBC Driver, and then select Finish.
In the Create new Data Source to SingleStoreDB dialog, enter a name for the data source in the Name box. Select Next.
In the Server Name box, enter the IP address or hostname for the server.
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:
Download and install the latest version of the SingleStore ODBC driver.
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 replaceserver-name
with SingleStore-server.Obtain a token for the user.
kinit <username>
Test the data source SingleStore-server configured in the odbc.ini file using the
isql
command as follows: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:
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.
General Connection Parameters
Parameter | Windows GUI Option | Default | Description |
---|---|---|---|
USER / UID | User name | The user name. | |
SERVER / SERVERNAME | Server name | The host name of the SingleStore server. | |
DATABASE / DB | Database | The default database. | |
OPTIONS / OPTION | -- | 0 | Options bitmask that specifies how Connector/ODBC works (see Option parameters 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 to SingleStore. | |
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 | |
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 | 0 | Read parameters from the |
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. |
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.
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 | 65536 (2^16) | Read parameters from the |
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 withtransactions, 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. |
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
.
// 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.
// 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.
// 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:
// 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);
Known Issues and Limitations
See the GitHub repository 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.