The SingleStore ODBC Driver

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

Download the SingleStore ODBC connector binaries for each supported operating system from its GitHub repository.

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.

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

brew install openssl@1.1 libiodbc 

[ODBC Drivers]
SingleStore ODBC Unicode Driver = Installed

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

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:

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

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.

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.

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);
}

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);
}

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

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