JDBC Driver

This section describes the JDBC driver provided by Exasol to connect third party application to Exasol.

System Requirements

JRE Requirement

The JDBC driver requires a Java runtime environment (version 5.0 or later). You can use the driver on any platform with a supported JRE version.

If you are using Exasol JDBC version 6.2.3 or later, the driver needs at least JRE version 7.

Tested Systems

The driver has been tested on the following systems using the specified Java version with the latest available updates:

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

    For Windows systems, Microsoft .NET Framework 4.0 Client Profile™ should be installed on your system. There is an automatic installation wizard for it.

  • Linux
    • Red Hat / CentOS 7, OpenJDK JVM 1.8.0 (x64)
    • Red Hat / CentOS 6, OpenJDK JVM 1.8.0 (x86/x64)
    • Debian 8, OpenJDK JVM 1.7.0 (x86/x64)
    • Ubuntu 16.04 LTS, OpenJDK JVM 1.8.0 (x86/64)
    • Ubuntu 14.04 LTS, OpenJDK JVM 1.7.0 (x86/64)
    • SUSE Linux Enterprise Server 12, IBM's JVM 1.7.0 (x64)
    • SUSE Linux Enterprise Desktop 12, OpenJDK JVM 1.7.0 (x64)
    • SUSE Linux Enterprise Server 11 Service Pack 3, IBM's JVM 1.7.0 (x86/x64)
    • openSUSE Leap 42.2, OpenJDK JVM 1.8.0 (x64)
  • macOS
    • macOS Sierra, JVM 1.8.0 (64Bit)
    • OS X 10.11, JVM 1.8.0 (64Bit)
  • Free BSD
    • FreeBSD 11.0, OpenJDK 1.8.0 (64Bit)
    • FreeBSD 10.3, OpenJDK 1.8.0 (64Bit)

Installing JDBC

On Windows

The driver is installed as a .jar archive in the installation directory.

On Linux/Unix

The driver is included in the delivered .tgz file. Depending on the application this archive must be added to the search path for the Java classes (CLASSPATH).

All classes of the JDBC driver belong to the Java com.exasol.jdbc package. The main class of the driver is com.exasol.jdbc.EXADriver

Integrating JDBC by using Maven

The JDBC driver is also available in the Exasol Maven repository (https://maven.exasol.com). Add the following repository and dependency to the build configuration of your project (for example, pom.xml for Maven)

<repositories>
	<repository>
		<id>maven.exasol.com</id>
		<url>https://maven.exasol.com/artifactory/exasol-releases</url>
		<snapshots>
			<enabled>false</enabled>
		</snapshots>
	</repository>
</repositories>
<dependencies>
	<dependency>
		<groupId>com.exasol</groupId>
		<artifactId>exasol-jdbc</artifactId>
		<version>6.0.0</version>
	</dependency>
</dependencies>

Exasol URL

The JDBC Driver uses the following URL structure URL for Exasol:

jdbc:exa:<host>:<port>[;<prop_1>=<value_1>]...[;<prop_n>=<value_n>]

The following table explains the elements of the URL structure.

Element Details
jdbc:exa This prefix is necessary for the driver manager.
<host>:<port>

Defines the servers and the port of the Exasol cluster (for example, 192.168.6.11..14:8563). If host names are specified there, they can have DNS entries with multiple IP addresses.

When opening a connection, the driver will randomly choose an address from the specified address range. If the connection fails, the driver will continue to try all other possible addresses. You can also specify an IP range with a comma-separated list. For example:

  • myhost:8563: Single server with name myhost and port 8563.
  • myhost1,myhost2:8563: Two servers with port 8563.
  • myhost1..4:8563: Four servers (myhost1, myhost2, myhost3, myhost4) and port 8563.
  • 192.168.6.11..14:8563: Four servers from 192.168.6.11 to 192.168.6.14 and port 8563.
<prop_i=value_i>

Instead of a list you can also specify a file that contains a list (for example, //c:\mycluster.txt). The two slashes ("/") indicate that a filename is specified. An optional list of properties separated by a ";" follows the port, the values of which should be set when logging-in.

These properties correspond with the supported Driver Properties and are described in the following section.

The values of properties within the URL can only consist of alphanumeric characters.

Example from a Java program

import java.sql.*;
import com.exasol.jdbc.*;

public class jdbcsample
{
	public static void main(String[] args)
	{
		try { Class.forName("com.exasol.jdbc.EXADriver");
	} catch (ClassNotFoundException e) { 
            e.printStackTrace();
	}
	Connection con=null; 
	Statement stmt=null; 
	try {
		con = DriverManager.getConnection( 
			"jdbc:exa:192.168.6.11..14:8563;schema=SYS",
			"sys", 
			"exasol"
			);
		stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM CAT"); 
		System.out.println("Schema SYS contains:"); 
		while(rs.next())
		{
			String str1 = rs.getString("TABLE_NAME"); 
			String str2 = rs.getString("TABLE_TYPE"); 
			System.out.println(str1 + ", " + str2);
		}
	} catch (SQLException e) { 
		e.printStackTrace();
	} finally {
		try {stmt.close();} catch (Exception e) {e.printStackTrace();} 
		try {con.close();} catch (Exception e) {e.printStackTrace();}
		}
	}
}

The above code sample builds a JDBC connection to Exasol, that runs on servers 192.168.6.11 up to 192.168.6.14 on port 8563. User sys with the password exasol is logged-in and the schema sys is opened. After that, all tables in this schema are shown.

Schema SYS contains: 
EXA_SQL_KEYWORDS, TABLE 
DUAL, TABLE 
EXA_PARAMETERS, TABLE
...

Supported Standards

The driver supports JDBC 3.0 Core API until Exasol JDBC 6.2.2 release. However, from Exasol JDBC 6.2.3 release, the driver supports JDBC 4.1 API.

The standard includes the following:

  • Access to the driver through the DriverManager-API and configuration of the driver using the DriverPropertyInfo interface.
  • Direct execution of SQL statements as a prepared statement and in batch mode.
  • Support of more than one open ResultSet
  • Support of the DatabaseMetaData and ResultSetMetaData metadata APIs.

The following features are not supported:

  • Savepoints
  • User-defined data types and the types Blob, Clob, Array, and Ref.
  • Stored procedures
  • Read-only connections
  • The API ParameterMetaData
  • If the rowcount of a query exceeds 2147483647 (231-1), the JDBC driver will return the value 2147483647. This is because of the 32-bit limitation for this return value defined by the JDBC standard.

    Detailed information about the supported interfaces are provided in the API Reference which you can find in the start menu (Windows) or in the folder html of the installation directory (Linux/Unix).

Supported Driver Properties

You can transfer the following properties to the JDBC driver through the URL.

Property Value Description
schema

String

Name of the schema that should be opened after login. If the schema cannot be opened, the login fails with a java.sql.SQLException.

Default: no schema to open

autocommit

0=off, 1=on

Switches autocommit on or off.

Default: 1

encryption

0=off, 1=on

Switches automatic encryption on or off.

Default: 1

kerberosservicename

String

Principal name of the Kerberos service. If nothing is specified, the name "exasol" will be used as default.
kerberoshostname

String

Host name of the Kerberos service. If nothing is specified, the host name of the connections string will be used as default
fetchsize

numeric, >0

Amount of data in kB which should be obtained by Exasol during a fetch. The JVM can run out of memory if the value is too high.

Default: 2000

The same can be achieved by using the function setFetchSize().

debug

0=off, 1=on

Switches on the driver's log function. The driver then writes a log file named jdbc_timestamp.log for each established connection

These files contain information on the called driver methods and the progress of the JDBC connection. It can assist the Exasol Support in the diagnosis of problems.

Due to performance reasons the logging should not be used in a productive system

Default: 0

logdir

String

Defines the directory where the JDBC debug log files should be written to (in debug mode).

Example: jdbc:exa:192.168.6.11..14:8563;debug=1;logdir=/tmp/my folder/;schema=sys

Default is the application's current directory, which is not always transparent. That is why you should always set the log directory in debug mode.

clientname

String

Tells the server what the application is called.

Default: "Generic JDBC client"

clientversion

String

Tells the server the version of the application.

Default: empty ("")

logintimeout

numeric, >=0

Maximum time (in seconds) the driver waits for the database for a connect or disconnect request.

Default is 0 (unlimited)

connecttimeout

numeric, >=0

Maximum time (in milliseconds) the driver waits to establish a TPC connection to a server. This timeout is used to limit the login time especially in case of a large cluster with multiple reserve nodes.

Default: 2000

querytimeout

numeric, >=0

Defines the time (in seconds) for a statement to run before it is automatically aborted.

Default is 0 (unlimited)

superconnection

0=off, 1=on

Enables the user to execute queries even if the limit for active sessions (executing a query) has been reached.

Note:

  • Only the SYS user can set the parameter.
  • superconnection should only be used in case of significant performance problems where it is impossible to log in and execute queries within a reasonable time. By that parameter you can analyze the system and kill certain processes which cause the problem

Default: 0

slave

0=off, 1=on

The sub-connections for parallel read and insert have this flag switched on.

Details and examples are available in an Exasol Solution Article.

Default: 0

slavetoken

numeric, >=0

Is necessary to establish parallel sub-connections.

Default: 0

Best Practices

Objective Details

Memory of JVM

When operating on big tables, you can get problems if the specified memory for the JVM is not big enough.

Reading big data volumes

Through the parameter fetchsize, you can determine the data volume which should be fetched from the database per communication round. If this value is too low, the data transfer may take longer to complete. If this value is too high, the JVM may run out of memory. The recommended fetch size is 1000-2000.

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 500 kB and 20 MB. Moreover you should insert the data by using the native data types.

Unused resources

Unused resources should be freed immediately. For example, Prepared Statements through "close()".

Connection servers

Don't specify a unnecessary wide IP address range. Since those addresses are randomly tried until a successful connection, the "connect" could take much time.