Call Level Interface SDK

This article describes the Exasol Call Level Interface SDK.

Exasol provides a software development kit (SDK) to connect client applications to Exasol. You can download SDK versions for Windows and Linux/FreeBSD from the Exasol Downloads Portal .

Exasol CLI is a native C++ interface that implements most of the ODBC variation of the Call Level Interface standard. The interface functions are similar to the ODBC functions but use the prefix EXA.

Some of the advantages of the Exasol CLI are:

  • The Exasol CLI is faster than ODBC and JDBC.
  • Special Exasol features such as batch execution, parallel reading, and parallel insertion are supported.
  • An ODBC programmer can quickly become familiar with the Exasol CLI.

Exasol CLI for Windows

Exasol CLI for Windows is tested on the following Windows versions:

  • Windows 10 (x86/x64)
  • Windows 8.1 (x86/x64)
  • Windows 7, Service Pack 1 (x86/x64)
  • Windows Server 2012 R2 (x86/x64)
  • Windows Server 2012 (x86/x64)
  • Windows Server 2008 R2, Service Pack 1 (x86/x64)
  • Windows Server 2008, Service Pack 2 (x86/x64)

Prerequisites

Microsoft .NET Framework 4.0 Client Profile must be installed on your system.

Installation

Download the SDK installer for Windows from the Exasol Downloads Portal and run it. The installer will install the SDK in the location that you select, including all necessary libraries, sample code, and documentation.

Deliverables

The CLI subfolder in the SDK installation folder contains the following:

  • Exasol libraries supporting unixODBC Driver Manager, such as libexacli.so and libexaodbc.so.

  • The header files exaCInterface.h and exaDefs.h. These files contain declarations and constants of the Exasol CLI Interface.

  • A fully functional sample program illustrating integration of the CLI interface. The sample is provided as source code (exaexec.c) and includes a project file for Visual Studio 10 (sqlExec.vcxproj).

  • Documentation in HTML format.

Compile and run a sample program

Visual Studio 10 or later must be installed on your system.

Open the project file sqlExec.vcxproj in the folder examples\sqlExec\ with Visual Studio.

The sample source file exaexec.c is already integrated in the project. The lib and include paths should point to the corresponding folders that were installed with the CLI. If you have moved or changed the files from the CLI package you must update the project accordingly.

You can compile the sample program for 32-bit and 64-bit as a debug and release version. The generated .exe file is a standard Windows console application. The source code of the sample program contains comments that describe the parts of the application.

Example of a call to a sample program sqlExec.exe 
echo select * from exa_syscat | sqlExec.exe -execDirect -u sys -P exasol
-c 192.168.6.11..14:8563

This example submits the SQL string select * from exa_syscat through standard-in to the sample program and through the CLI function. EXAExecDirect() is then executed in Exasol.

Exasol CLI for Linux/FreeBSD

Exasol CLI for Linux/FreeBSD is tested on the following OS versions:

  • Red Hat / CentOS 7 (x64)
  • Red Hat / CentOS 6 (x86/x64)
  • Debian 8 (x86/x64)
  • Ubuntu 16.04 LTS (x86/64)
  • Ubuntu 14.04 LTS (x86/64)
  • SUSE Linux Enterprise Server 12 (x64)
  • SUSE Linux Enterprise Desktop 12 (x64)
  • SUSE Linux Enterprise Server 11 (x86/x64)
  • openSUSE Leap 42.2 (x64)
  • FreeBSD 11.0 (64Bit)
  • FreeBSD 10.3 (64Bit)

Installation

Download the SDK package for your Linux/FreeBSD version from Exasol Downloads Portal and extract the contents.

Deliverables

The package includes the following:

  • Exasol libraries supporting unixODBC Driver Manager, such as libexacli.so and libexaodbc.so.

  • The header files exaCInterface.h and exaDefs.h. These files contain declarations and constants of the Exasol CLI Interface.

  • A sample program illustrating integration of the CLI interface. The sample is provided as source code exaexec.c with makefiles for Linux and FreeBSD.

  • Documentation in HTML format.

Compile and run a sample program

Compile the sample program using the appropriate makefile for Linux or FreeBSD.

Examples

The following example executes the SQL statement select * from exa_syscat and returns the number of columns and rows of the result.

echo "select * from exa_syscat" | ./exaexec -execDirect -u sys -P exasol -c 192.168.6.11..14:8563
Results
RESULTCOLS=3
ROWS=83
PARAMCOUNT=0

The following example illustrates how to send SQL queries to the database. The return values are not checked to simplify the code.

// Like in the ODBC interface, handles are created before the connection
// is established (for environment and connection) 
SQLHENV henv;
SQLHDBC hdbc;
EXAAllocHandle(SQL_HANDLE_ENV, NULL, &henv); 
EXAAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);

// Connect to a cluster with 3 nodes
EXAServerConnect(hdbc, "192.168.6.11..14:8563", SQL_NTS, NULL,
0, "sys", SQL_NTS, "exasol", SQL_NTS);

// Create a SQL statement handle 
SQLHSTMT hstmt;
EXAAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

// Create schema "TEST"
EXAExecDirect(hstmt, (SQLCHAR*)"CREATE SCHEMA test", SQL_NTS);

// Free the resources and close the connection 
EXAFreeHandle(SQL_HANDLE_STMT, hstmt); 
EXADisconnect(hdbc); 
EXAFreeHandle(SQL_HANDLE_DBC, hdbc); 
EXAFreeHandle(SQL_HANDLE_ENV, henv);

Best practices

Reading big data volumes

  • Always use SQLBindCol and SQLFetch instead of SQLGetData.

  • For best performance, try to fetch 50 to 100 MiB of data by choosing the number of rows per SQLFetch.

Inserting data into the database

  • Instead of using single insert statements like INSERT INTO t VALUES 1, 2, ..., use the more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 50 MiB and 100 MiB.

  • Insert the data using native data types. For example, for the number 1234, use the integer data type SQL_C_SLONG (1234) instead of SQL_CHAR ("1234").