SDK - Call Level Interface

Exasol provides a Software Development Kit (SDK) to connect client application with Exasol. You can download the SDK from the Exasol download page.

Call Level Interface

Native C++ Interface

The native C++ interface (also known as Exasol CLI) implements most of the ODBC variation of the Call Level Interfaces Standard. The interface functions are similar to the ODBC functions. The only difference is a prefix EXA. The Exasol CLI is available for Windows and Linux. Some of the advantages of the Exasol CLI are:

  • Exasol CLI is faster than ODBC and JDBC.
  • Special Exasol features are supported (for example, batch execution, parallel reading, parallel insertion, and so on).
  • An ODBC programmer can become familiar with the Exasol CLI very quickly.

CLI for Windows

The CLI for Windows is available for the 32-bit and 64-bit versions of the Windows OS. The CLI is tested on the following OS:

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

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

Deliverables

The CLI subfolder contains the following:

  • Exasol CLI Libraries, such as, EXACLI.lib and all dynamic libraries (*.dll). The files compiled for 32-bit and 64-bit Windows are available in the directory lib32 and lib64 respectively.
  • The header files exaCInterface.h and exaDefs.h. The files contain declarations and constants of the Exasol CLI Interface.
  • A fully functional sample program illustrating integration of the CLI interface. The sample is available as source code (.cpp). Additionally, the package contains a project file for Visual Studio 10 (.vcproj).

Compiling and Running a Sample Program

Ensure that you have installed Visual C++/Visual Studio 10 or later version on your system.

Open the project file (.vcproj) from the CLI in the directory examples\sqlExec\ with Visual Studio.

The sample sources (.cpp) are already integrated in the project. The Lib and include paths point to the corresponding directories that were installed with the CLI. If files from the CLI package have been moved or changed, the project should be modified 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 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.

CLI for Linux/Unix

The CLI for Linux/Unix is tested on the following OS:

  • Linux
    • 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
    • FreeBSD 11.0 (64Bit)
    • FreeBSD 10.3 (64Bit)

    Ensure that you read the README.txt file included in the installation package.

Deliverables

The package includes the following:

  • Exasol libraries, such as, libexacli-uo2212.so and libexacli-uo2214.so. The dynamic libraries follow the unixODBC (versions 2.2.12 and 2.2.14), and are compiled using gcc 4.1.0.
  • The header files exaCInterface.h and exaDefs.h. The files contain declarations and constants of the Exasol CLI Interface.
  • A fully functional sample program illustrating integration of the CLI interface. The sample is available as source code (.cpp) with an appropriate Makefile.

Compiling and Running a Sample Program

Ensure that you have GCC 4.1.0 or higher version available on your system.

Compile the sample program using Makefile from the folder example

Example of a call to a sample program exaexec is given below.

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

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

Example

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

Objective Details

Reading big data volumes

Always use SQLBindCol and SQLFetch instead of SQLGetData.

For best performance, try to fetch 50 to 100 MB 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, ..." you should use the more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 50 MB and 100 MB.

Moreover you should insert the data by using the native data types. For example, you should use SQL_C_SLONG (Integer) instead of SQL_CHAR ("1234") for number 1234.