Using the Splice Machine ODBC Driver

This topic describes how to configure and use the Splice Machine ODBC driver, which you can use to connect with other databases and business tools that need to access your database.

You must use the Splice Machine ODBC driver; other drivers will not work correctly.

This topic describes how to install and configure the Splice Machine ODBC driver for these operating systems:

This topic also includes an example that illustrates using our ODBC driver with the C language.

Installing and Configuring the Driver on Windows

You can install the Windows version of the Splice Machine ODBC driver using the provided Windows installer (.msi file); we provide both 64-bit and 32-bit versions of the driver. Follow these steps to install the driver:

  1. Download the installer:

    You can download the driver installer from our ODBC download site: 

    The file you download will have a name similar to these:

    • splice_odbc_setup_64bit_1.0.28.0.msi
    • splice_odbc_setup_32bit_1.0.28.0.msi
  2. Start the installer

    Double-click the installers .msi file to start installation. You’ll see the Welcome screen:

    Image of the welcome screen for the Splice Machine ODBC driver
installer

    Click the Next button to proceed.

  3. Accept the license agreement.

  4. Select the destination folder for the driver

    The default destination is generally fine, but you can select a different location if you like:

    Splice Machine ODBC Driver destination
folder

    Click the Next button to continue to the Ready to Install screen.

  5. Click install

    Click the Install button on the Ready to install screen. Installation can take a minute or two to complete.

    The installer may notify you that you either need to stop certain software before continuing, or that you can continue and then reboot your computer after the installation completes.

  6. Finish the installation

    Click the Finish button, and you’re ready to use the Splice Machine ODBC driver.

  7. Start the Windows ODBC Data Source Administrator tool

    You need to add our ODBC driver to the set of Windows ODBC data sources, using the Windows ODBC Data Source Administrator tool; You can read about this tool here: https://msdn.microsoft.com/en-us/library/ms712362(v=vs.85).aspx.

    You can find and start the Windows ODBC Administrator tool using a Windows search for ODBC on your computer; here’s what it looks like on Windows 7:

    Image of using Windows search to find the ODBC Administrator
tool

  8. Add the Splice Machine driver as a data source

    Click the Add button the User DSN tab of the ODBC Data Source Administrator screen, and then select the Splice Machine driver you just installed:

    Adding the Splice Machine driver as an ODBC data source on
Windows

  9. Configure your new data source:

    When you click the Finish button in the Create New Data Source screen, the ODBC Administrator tool displays the data source configuration screen.

    Set the fields in the Data Source and Splice Machine Login sections similarly to the settings shown here:

    Configuring the Splice Machine ODBC data source on
Windows

    The default user name is splice, and the default password is admin.

    For Server: on a cluster, specify the IP address of an HBase RegionServer. If you’re running the standalone version of Splice Machine, specify localhost.

    If you have Splice Machine running, you can click the Test… button at the bottom of the Configuration dialog to verify that all is well.

  10. Configure logging (optional):

    You can optionally configure the ODBC driver to log activity. This can be handy for debugging connection issues; however, it adds overhead and will have a significant impact on performance. Click the Logging Options button in the ODBC Administrator Configuration screen to enable or disable logging:

    Configuring Splice Machine ODBC driver logging on
Windows

Installing the Driver on Linux

Follow these steps to install the Splice Machine ODBC driver on a Linux computer:

  1. Make sure you have unixODBC installed.

    You must have version 2.2.12 or later of the unixODBC driver manager installed to run the Splice Machine ODBC driver.

    Some Linux distributions include unixODBC, while others do not. Our driver will not work without it. For more information about unixODBC, see: http://www.unixodbc.org.

  2. Download the installer:

    You can download the driver installer from our ODBC download site: https://www.splicemachine.com/get-started/odbc-driver-download/

    Download the installer to the Linux computer on which you want to install the driver. The file will have a name similar to this:

    splice_odbc_64-1.0.28.0.x86_64.tar.gz
    
  3. Unzip the installation package

    Use the following command to unpack the tarball you installed, substituting in the actual version number from the download:

    tar xzf splice_odbc_64-<version>.x86_64.tar.gz
    

    This creates a directory named splice_odbc_64-<version>.

  4. Install the driver:

    Navigate to the directory that was created when you unzipped the tarball, and run the install script:

    If you run the script as root, the default installation directory is /usr/local/splice:

    sudo ./install.sh
    

    If you run the script as a different user, the driver is installed to ~/splice.

    ./install.sh
    

    The script creates a splice directory in the install location; you’ll be prompted for that location, which defaults to /usr/local.

    You’ll also be prompted to enter the IP address of the Splice Machine server, which defaults to 127.0.0.1.

    The install directory, e.g. /usr/local/splice, will contain two subdirectories:

    Directory Contents
    lib64 The driver binary.
    errormessages The XML error message source for any error messages issued by the driver.
  5. Configure the driver:

    If you ran the installation script as root, the odbc.ini, odbcinst.ini, and splice.odbcdriver.ini configuration files were copied into the /etc folder, and any previous copies were renamed, e.g. odbc.ini.1.

    If you did not run the installation script as root, then hidden versions of the same files are located in your $HOME directory: .odbc.ini, .odbcinst.ini, and .splice.odbcdriver.ini.

    File Description
    odbc.ini

    Specifies the ODBC data sources (DSNs).

    odbcinst.ini Specifies the ODBC drivers.
    splice.odbcdriver.ini Configuration information specific to the Splice Machine ODBC driver.

    The default version of the odbc.ini file looks like this:

    [ODBC Data Sources]
    SpliceODBC64        = SpliceODBCDriver
    
    [SpliceODBC64]
    Description     = Splice Machine ODBC 64-bit
    Driver          = /usr/local/splice/lib64/libsplice_odbc.so
    UID             = splice
    PWD             = admin
    URL             = 127.0.0.1
    PORT            = 1527
    SSL             = peerAuthentication
    SSL_CERT        = /home/splice/client.pem
    SSL_PKEY        = /home/splice/client.key
    SSL_TRUST       = TRUE
    

    If you specified a different installation directory, you need to update the Driver location setting in your odbc.ini file. This is not typically required; however, if you do make this change, you should copy your modified file to the /etc directory.

    cp odbc.ini /etc/
    

    If you are connecting to a Kerberos-enabled cluster using ODBC, you must add this parameter:

    USE_KERBEROS    = 1
    

    For more information about connecting to a Kerberos-enabled cluster, see Connecting to Splice Machine Through HAProxy.

  6. Configure Driver Logging, if desired

    You can edit the splice.odbcdriver.ini file to configure driver logging, which is disabled by default:

    [Driver]
    DriverManagerEncoding=UTF-16
    DriverLocale=en-US
    ErrorMessagesPath=/usr/local/splice/errormessages/
    LogLevel=0
    LogNamespace=
    LogPath=
    ODBCInstLib=/usr/lib64/libodbcinst.so
    

    To configure logging, modify the LogLevel and LogPath values:

    LogLevel

    You can specify one of the following values:

    0 = OFF
    1 = LOG_FATAL
    2 = LOG_ERROR
    3 = LOG_WARNING
    4 = LOG_INFO
    5 = LOG_DEBUG
    6 = LOG_TRACE

    The larger the LogLevel value, the more verbose the logging.

    Logging does impact driver performance.

    LogPath

    The path to the directory in which you want the logging files stored. Two log files are written in this directory:

    splice_driver.log Contains driver interactions with the application and the driver manager.
    splice_derby.log Contains information about the drivers interaction with the Splice Machine cluster.

    After configuring logging, copy the file to /etc:

  7. Verify your installation

    You can test your installation by using the following command to run isql:

    isql SpliceODBC64 splice admin
    

Installing the Driver on MacOS

Follow these steps to install the Splice Machine ODBC driver on a MacOS computer:

Our MacOS ODBC driver is currently in Beta release.

  1. Make sure you have iODBC installed.

    You must have an ODBC administration driver to manage ODBC data sources on your Mac. We recommend installing the iODBC driver for the Mac, which you’ll find on the iODBC site: www.iodbc.org/

  2. Download the installer:

    You can download the driver installer from our ODBC download site: https://www.splicemachine.com/get-started/odbc-driver-download/

    Download the installer to the MacOS computer on which you want to install the driver. The file will have a name similar to this:

    splice_odbc_64_macosx64-2.5-45.0.tar.gz
    
  3. Unzip the installation package

    Use the following command to unpack the tarball you installed, substituting in the actual version number from the download:

    tar -xzf splice_odbc_64_macosx64-<version>.tar.gz
    

    This creates a directory named splice_odbc_macosx64.

  4. Install the driver:

    Navigate to the directory that was created when you unzipped the tarball, and run the install script:

    ./install.sh
    

    Follow the installer prompts. In most cases, you can simply accept the default values.

    The installer will create several files in the install directory, including these three files, which contain the configuration info that can be modified as required:

    File Description
    odbc.ini

    Specifies the ODBC data sources (DSNs).

    odbcinst.ini Specifies the ODBC drivers.
    splice.odbcdriver.ini Configuration information specific to the Splice Machine ODBC driver.

    If you have not previously installed our ODBC driver, the installer will also copy the files into $HOME/Library/ODBC for use with iODBC.

  5. Configure the driver:

    The installed driver is configured with settings that you specified when responding to the installer prompts. You can change values as follows:

    1. Edit the odbc.ini file to match your configuration.

      You’ll find the odbc.ini file in your $HOME/Library/ODBC directory; we also create a link to this file in $HOME/.odbc.ini. You can edit odbc.ini (or .odbc.ini) from either location.

      The URL field in the odbc.ini file is actually the IP address of the Splice Machine server.

      The default version of the odbc.ini file looks like this:

      [ODBC Data Sources]
      SpliceODBC64        = SpliceODBCDriver
      
      [SpliceODBC64]
      Description     = Splice Machine ODBC 64-bit
      Driver          = /usr/local/splice/lib64/libsplice_odbc.so
      UID             = splice
      PWD             = admin
      URL             = 0.0.0.0
      PORT            = 1527
      

      If you are connecting to a Kerberos-enabled cluster using ODBC, you must add this parameter:

      USE_KERBEROS    = 1
      

      For more information about connecting to a Kerberos-enabled cluster, see Connecting to Splice Machine Through HAProxy.

    2. Edit (if desired) and copy the splice.odbcdriver.ini file:

      The splice.odbcdriver.ini file contains information specific to the driver. You can edit this file to configure driver logging, which is disabled by default:

      [Driver]
      DriverManagerEncoding=UTF-16
      DriverLocale=en-US
      ErrorMessagesPath=/usr/local/splice/errormessages/
      LogLevel=0
      LogNamespace=
      LogPath=
      ODBCInstLib=/usr/lib64/libodbcinst.so
      

      A copy of the Splice Machine ODBC configuration file, splice.odbcdriver.ini, which contains the default values, was copy to /Library/ODBC/SpliceMachine during installation. You will need root access to modify this file:

      sudo vi /Library/ODBC/SpliceMachine/splice.odbcdriver.ini
      

      To configure logging, modify the LogLevel and LogPath values:

      LogLevel

      You can specify one of the following values:

      0 = OFF
      1 = LOG_FATAL
      2 = LOG_ERROR
      3 = LOG_WARNING
      4 = LOG_INFO
      5 = LOG_DEBUG
      6 = LOG_TRACE

      The larger the LogLevel value, the more verbose the logging.

      Logging does impact driver performance.

      LogPath

      The path to the directory in which you want the logging files stored. Two log files are written in this directory:

      splice_driver.log contains driver interactions with the application and the driver manager
      splice_derby.log contains information about the drivers interaction with the Splice Machine cluster
  6. Verify your installation

    You can test your installation by launching the 64-bit version of the iODBC Data Source Administrator for both configuring and testing your DSNs. Note that you can also perform your odbc.ini modifications with this tool instead of manually editing the file.

Using the ODBC Driver with C

This section contains a simple example of using the Splice Machine ODBC driver with the C programming language. This program simply displays information about the installed driver. You can compile and run it by following these steps:

  1. Copy the code

    You can copy and paste the code below:

    #include <stdio.h>
    #include <sql.h>
    #include <sqlext.h>
    
    main() {
       SQLHENV env;
       char driver[256];
       char attr[256];
       SQLSMALLINT driver_ret;
       SQLSMALLINT attr_ret;
       SQLUSMALLINT direction;
       SQLRETURN ret;
    
       SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
       SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    
       direction = SQL_FETCH_FIRST;
       while(SQL_SUCCEEDED(ret = SQLDrivers(env, direction,
             driver, sizeof(driver), &driver_ret,
             attr, sizeof(attr), &attr_ret))) {
             direction = SQL_FETCH_NEXT;
          printf("%s - %s\n", driver, attr);
          if (ret == SQL_SUCCESS_WITH_INFO) printf("\tdata truncation\n");
          }
    }
    
  2. Compile it

    #!/bin/bash
    # gcc -I /usr/local/splice/unixODBC/include listODBCdriver.c -o listODBCdriver -L/usr/local/splice/lib -lodbc -lodbcinst -lodbccr
    
  3. Run the program

    Run the compiled listODBCdriver:

    prompt:~$ ./listODBCdriver
    

    The command should display a result like the following:

    Splice Machine - Description=Splice Machine ODBC Driver