SingleStore DB

The SingleStore ODBC Driver

The SingleStore ODBC 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.

Getting Started with the SingleStore ODBC Driver

To get started with the SingleStore ODBC driver, make sure you are using a supported operating system and then download the file to your computer and install it.

After that, see the <CONFIGURATION> options for using the driver.

Prerequisites

The SingleStore ODBC driver is available for the following 64-bit platforms:

  • macOS (tested on 10.14.4)

  • Windows 10

  • CentOS 7 and 8 / Debian 9 and 10

Download the SingleStore ODBC Driver

Links to download the binaries for each supported operating system can be found here: https://github.com/memsql/singlestore-odbc-connector/releases/latest

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.

Installing on Windows

SingleStore provides a 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.

Installing 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/) libraries, which are not available natively. You can get these libraries with Homebrew (https://brew.sh/).

Homebrew installation instructions can be found here https://docs.brew.sh/Installation. The basic installation is to run the following command in the terminal:

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

The command to install OpenSSL is: brew install openssl@1.1

Installing on Linux
  1. Install UnixODBC and OpenSSL

    1. 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
    2. 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
  2. Download the Binary Tarball and Extract the Files

    The files can be found here: https://github.com/memsql/singlestore-odbc-connector/releases/tag/v1.0.0

    Extract the files with a command similar to this:

    % tar xzvf singlestore-connector-odbc-1.0.0-debian10-amd64.tar.gz
  3. Configure SingleStore ODBC Connector as a UnixODBC Driver on Linux

    1. Create a template file similar to the following, and name it something like SingleStore_odbc_driver_template.ini.

      [SingleStore ODBC Driver]
      Description = SingleStore ODBC Driver
      Driver = <your path>/libssodbc[a|w].so

      Be sure to specify the correct path to the driver and specify which driver (a or w) to use.

    2. Install it to the system's global /etc/odbcinst.ini file with the following command:

      % sudo odbcinst -i -d -f SingleStore_odbc_driver_template.ini
  4. Configure a DSN with UnixODBC on Linux

    1. Create a template file similar to the following, and name it something like SingleStore_odbc_data_source_template.ini.

      [SingleStore-server]
      Description=SingleStore server
      Driver=SingleStore ODBC Driver
      SERVER=<your server>
      USER=<your user>
      PASSWORD=<your password>
      DATABASE=<your database>
      PORT=<your port>
    2. Install it to the system's global /etc/odbc.ini file with the following command:

      % sudo odbcinst -i -s -l -f SingleStore_odbc_data_source_template.ini
  5. Verifying a DSN Configuration with UnixODBC on Linux

    1. Verify that a DSN is properly configured with UnixODBC on Linux by using the isql utility:

      % isql SingleStore-server
Configuring the SingleStore ODBC Driver

Use the parameters from the following tables to configure DSN.

On Windows, you can use the ODBC Data Source Administrator to set these 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);
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 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.

Option Parameters

These parameters should be passed in via the OPTIONS general connection parameter. Here's an example of how to use the bitmask to specify multiple option parameters.

Parameter

Windows GUI Option

Constant Value

Description

FOUND_ROWS

Enable dynamic cursor

32 (2^5)

Enable or disable the dynamic cursor support.

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.

FORWARD_CURSOR

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

Some sample code to 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 broke 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

An example of how to pass in 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 repo 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 &amp; 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.