Using the ODBC Driver

This article explains how to use the ODBC driver with Exasol.

Establishing connection in an ODBC application

To establish a connection to Exasol through an ODBC driver, use either of the following ODBC functions:

  • SQLConnect()
  • SQLDriverConnect()

SQLConnect()

In this method, you can choose a DSN entry and define user and password.

Example: 
SQLConnect(connection_handle,
          (SQLCHAR*)"exa_test", SQL_NTS,
          (SQLCHAR*)"sys", SQL_NTS, 
          (SQLCHAR*)"exasol", SQL_NTS);

SQLDriverConnect()

In this method, you have the following two options:

  • Choose a DSN entry of the odbc.ini file.
  • Choose a driver from the odbcinst.ini file.

In both cases a connection string is used to define further options. For more information, see Connection strings.

Example:
SQLDriverConnect(connection_handle, NULL,
                (SQLCHAR*)"DSN=exa_test;UID=sys;PWD=exasol", SQL_NTS, 
                 NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
                 
SQLDriverConnect(connection_handle, NULL, 
                 (SQLCHAR*)
                 "DRIVER={EXASolution Driver};EXAHOST=192.168.6.11..14:8563;UID=sys;PWD=exasol" 
                 SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);

Connection strings

A connection function has multiple key-value pairs. The driver manager then loads the correct driver and hands over the necessary parameters.

The data of the connection string has higher priority than the values of the odbc.ini file on Linux systems and the configuration of the data source on Windows systems.

Examples of valid DSN and DSN-less connection strings

DSN=exa_test;UID=sys;PWD=exasol;EXASCHEMA=MY_SCHEMA

DRIVER={Exasol Driver};EXAHOST=192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563;UID=sys;PWD=exasol

Supported keys in connection strings

The following table describes all keys that are supported in this version of Exasol. Keys that are not listed in the table are not supported.

The connection string key names are not case sensitive.

Key Value type Description
ALWAYSSEARCHPATTERNSINCAT

boolean

[Y | N]

Enables the use of ODBC search patterns in the catalog function.

Y = enabled, N = disabled

Default: disabled
ANSIARGENCODING string

Sets the encoding of the arguments of ANSI functions to the given value.

For example: ANSIARGENCODING=UTF-8.

See also ENCODING.
ANSIDATAENCODING string

Sets the encoding of ANSI string data (SQL_C_CHAR) to the given value. For example, the data of prepared parameter values.

See also ENCODING.
AUTHMETHOD string

Specifies the authentication method for OpenID.

  • If the value for the parameter is accesstoken, the connection string will be "EXAHOST=<host>:<port>;AUTHMETHOD=accesstoken;EXAPWD=<access_token>" and the password will be your OpenID Access Token.

  • If the value for the parameter is refreshtoken, the connection string will be "EXAHOST=<host>:<port>;AUTHMETHOD=accesstoken;EXAPWD=<refresh_token>" and the password will be your OpenID Refresh Token.

For more information, see Authentication using OpenID.
AUTOCOMMIT

boolean

[Y | N]

Enables/disables autocommit mode.

Y = enabled, N = disabled

Default: enabled
COGNOSSUPPORT

boolean

[Y | N]

We recommend that you enable this parameter if you want to use the Exasol ODBC driver in combination with Cognos.

Y = enabled, N = disabled

Default: disabled
CONNECTIONLCCTYPE string

Sets LC_CTYPE to the given value during the connection. For example:

  • Windows: deu or eng.
  • Linux/Unix: de_DE or en_US. You can also set an encoding, for example, en_US.UTF-8
CONNECTIONLCNUMERIC string

Sets LC_NUMERIC to the given value during the connection. For example:

  • Windows: deu or eng.
  • Linux/Unix: de_DE or en_US.
DEFAULTPARAMSIZE

integer

>0

Default size for VARCHAR parameters in prepared statements whose type cannot be determined at prepare time.

Default: 2000
DRIVERUNICODETYPE [1 | 2]

If the client uses the DataDirect driver manager, this parameter specifies the type of Unicode to be used in W-functions.

1 = UTF-16, 2 = UTF-8
ENCODING string

Sets ANSIARGENCODING, ANSIDATAENCODING, UNICODEARGENCODING, and UNICODEDATAENCODING to the given value.

ENCRYPTION

boolean

[Y | N]

Enables/disables automatic encryption.

Y (enabled), N (disabled)

Default: enabled
EXAHOST string

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.

Instead of an IP range you can define a comma-separated list of host:port pairs. If host names are specified in the list they can have DNS entries with multiple IP addresses.

For example:

  • myhost/<fingerprint>:8563: Single server with name myhost and port 8563. Or single host myhost with multiple IP addresses mapped to it, fingerprint /<fingerprint>, and port 8563.
  • myhost1,myhost2/<fingerprint>:8563: Two servers with fingerprint /<fingerprint> and port 8563.
  • myhost1..4/<fingerprint>:8563: Four servers (myhost1, myhost2, myhost3, myhost4), fingerprint /<fingerprint>, and port 8563.
  • 192.168.6.11..14/<fingerprint>:8563: Four servers from 192.168.6.11 to 192.168.6.14, fingerprint /<fingerprint>, and port 8563.
  • 192.168.6.11..14/<fingerprint>:8563: Four servers from 192.168.6.11 to 192.168.6.14, fingerprint /<fingerprint>, and port 8563.

You can also specify the path to a file that contains a host list, for example, //c:\mycluster.txt. The two slashes ("//") indicate that a file path is specified.

To disable TLS encryption you can use the NOCERTCHECK option. NOCERTCHECK is case insensitive. For example:

EXAHOST=exadb1.example.com/NOCERTCHECK:8563

EXAHOST=exadb1.example.com:8563/nocertcheck

EXAHOST=exadb1.example.com:8563;FINGERPRINT=NoCertCheck;

EXALOGFILE string

Log file for the driver.

Default: empty (logging disabled)

Depending on the log mode, the size of the log file can become very large. Because of this, we do not recommend enabling logging in a production environment.

Be aware that log files can contain sensitive data (SQL commands and data).
EXAPWD or PWD string

Password of the user.

PWD is automatically removed from the connection string by some applications.

EXASCHEMA string

Schema that is opened directly after the connection.
EXAUID or UID string

Username for the login.

UID is automatically removed from the connection string by some applications.
FEEDBACKINTERVAL

integer

≥0

The interval in seconds between the feedback messages that the server sends to the client during a query. This feedback does the following:

  • Signals that the server is still there and executing the client command

  • Prevents the connection from being cut by the networks for inactivity

  • Checks whether a command is canceled

Default: 1 (second)

Setting a very high feedback interval value may cause a long lag time before a command is canceled.
FINGERPRINT string

If TLS validation fails and you trust the server, use this parameter to include the server fingerprint in the connection string. For example:

FINGERPRINT=BA7816BF8F01CFEA414140DE5DAE2223B00361A396177A9CB410FF61F20015AD

To bypass TLS certificate checking, use the NOCERTCHECK option. NOCERTCHECK is case insensitive and can be placed after the host or port, or in a FINGERPRINT argument. For example:

  • EXAHOST=exadb1.example.com/NOCERTCHECK:8563
  • EXAHOST=exadb1.example.com:8563/nocertcheck
  • EXAHOST=exadb1.example.com:8563;FINGERPRINT=NoCertCheck;

A connection string must only contain a single fingerprint, specified either after the host or port or in a FINGERPRINT argument. If you provide multiple fingerprints, or if you specify the same fingerprint more than once in a connection string, the connection will fail.
HOSTTIMEOUT

integer

≥0

Specifies the connection timeout in milliseconds for each TCP connection attempt. If multiple hosts are specified in the connection string, each host will wait for the specified time for a successful connection.

Default: 2000 (ms)
IGNOREPARAMS string

Optional comma-separated list of string parameters to be ignored by the driver’s spell checker.

Example: IGNOREPARAMS=someparam,abcxyz
IGNOREUNKNOWNATTR

boolean

[Y | N]

When enabled, the driver will not return the SQL_ERROR if the client uses an unknown environment, connection, or statement.

Y = enabled, N = disabled

Default: disabled
INTTYPESINRESULTSIFPOSSIBLE

boolean

[Y | N]

When enabled, DECIMAL types without scale will be returned as SQL_INTEGER (9 digits) or SQL_BIGINT (18 digits) instead of SQL_DECIMAL.

Y = enabled, N = disabled

Default: disabled
KERBEROSHOSTNAME string

Host name of the Kerberos service.

Default: the value of the EXAHOST parameter
KERBEROSSERVICENAME string

Principal name of the Kerberos service.

Default: exasol
LEGACYCATALOG

boolean

[Y | N]

When enabled, the ODBC catalog functions will use SQL statements to generate their results.

Y = enabled, N = disabled

Default: disabled
LEGACYENCRYPTION

boolean

[Y | N]

When enabled, the connection uses the legacy ChaCha encryption instead of SSL.

Y = enabled, N = disabled

Default: disabled (SSLencryption enabled)
LOGINTIMEOUT

integer

≥0

The maximum time in milliseconds that the driver will wait to establish a connection.

This timeout is required to limit the overall login time, especially in the case of a large cluster with several reserve nodes.

Default: no timeout (driver will wait indefinitely for the connection to be established)
LOGMODE string

Specifies the mode for the log file. The following options are available:

  • DEFAULT: Logs the most important function calls and SQL commands.
  • VERBOSE: Logs additional data about internal process steps and result data.
  • ON ERROR ONLY: Logs only in case of an error.
  • DEBUGCOMM: Extended logs for analyzing the client/server communication (similar to VERBOSE, but without the data and parameter tables).
  • None: No logging of data.
MAXHANDLES

integer

≥0

Changes the maximum number of handles (environment, connection, statement, and descriptor handles) that can be held by an instance of the driver. The maximum value is 20000.

Default: 2000
MAXPARAMSIZE

integer

≥0

Sets a maximum size for VARCHAR parameters in prepared statements, even if the determined data type was bigger.

0 = no maximum size (function disabled)

Default: no maximum size
PREPAREAS

boolean

[Y | N]

When enabled, SQLPrepare and SQLPrepareW always return SQL_SUCCESS.

Y(enabled), N (disabled)

Default: disabled
QUERYTIMEOUT

integer

≥0

Defines a query timeout in seconds for a connection.

0 = no timeout (function disabled)

Default: no timeout
SHOWONLYCURRENTSCHEMA

boolean

[Y | N]

Defines whether the ODBC driver considers all the schemas or just the current schema for metadata like the list of columns or tables.

Y = enabled, N = disabled

Default: disabled
SILENT

boolean

[Y | N]

When enabled, the Exasol ODBC configuration dialog is not shown when an Exasol ODBC DSN is configured using odbcconf.exe.

Y = enabled, N = disabled

Default: disabled
SNAPSHOTTRANSACTIONS

boolean

[Y | N]

Changes the snapshottransaction setting in the session.

Y = enabled, N = disabled

Default: session default
SQLSTATEMAPPINGACTIVE

boolean

[Y | N]

Enables mapping for ODBC SQL states received for errors.

Y = enabled, N = disabled

Default: disabled

Default mapping string when enabled:

08:42000,22:22003,27:23000,400:08003,420:42000,425:42S02,R00:08003

SQLSTATEMAPPINGS string

Specifies a mapping state for SQL states if SQLSTATEMAPPINGACTIVE is enabled.

If the value is set, the new values are used first, then the default values.
SSLCERTIFICATE string

Specifies how the driver will treat the server certificate for the connection. You can also specify a self signed certificate.

Possible values:

  • SSL_VERIFY_SERVER = The driver verifies the certificate in the SSL connection (default).
  • SSL_VERIFY_NONE = The driver establishes an SSL connection, but does not verify the server certificate.
  • <certificate_file_name> Specifies the path to a self signed certificate.

If the certificate is not verified or a client does not have the certificate, the connection fails. To overcome this problem, you can use a fingerprint in the connection string to bypass certificate verification. For example:

EXAHOST=exadb1.example.com/<fingerprint>:8563

The fingerprint is automatically added to the database connection string when you upload a TLS certificate. For more information, see Upload TLS Certificate.
STRINGSNOTNULL

boolean

[Y | N]

Defines whether the ODBC driver returns empty strings for NULL strings when reading table data.

Y (enabled), N (disabled)

Default: disabled

Note: The database internally does not distinguish between NULL and empty strings. It will return NULL values to the driver in both cases.

SUPERCONNECTION

boolean

[Y | N]

When enabled, the user can execute queries even if the limit for active sessions (executing a query) has been reached.

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. Enabling this parameter will allow you to analyze the system and kill processes that cause problems.

Only the SYS user can set this parameter.

Y = enabled, N = disabled

Default: database default
UNICODEARGENCODING string

Sets the encoding of the arguments of W functions to the given value.

See also ENCODING.
UNICODEDATAENCODING string

Sets the encoding of Unicode string data (SQL_C_WCHAR) to the given value. For example, the data of prepared parameter values.

See also ENCODING.

Supported standards

The Exasol ODBC driver supports the ODBC 3.5 standard (core level).

The following features are not supported:

  • Positioned update/delete and batched updates/deletes (SQLSetPos)
  • Asynchronous execution
  • Bookmarks

If the rowcount of a query exceeds 2147483647 (231-1), the ODBC driver will return the value 2147483647 for the function SQLRowCount(). This is because of the 32-bit limitation for this return value defined by the ODBC standard.

Support for character sets

Internally, the ODBC driver uses the UTF-8 unicode character set. Other character set input data is converted to UTF-8 and transferred to Exasol. Data from Exasol is converted by the ODBC driver into the character set which is specified on the client site.

On Windows servers, the data sent to the ODBC driver should be in an encoding that is locally installed. You can specify the encoding through the language settings of the connection.

On Linux servers, you can set the encoding through environment variables. For example, to set the language to German and the encoding to UTF-8 in the console, you can use the command export LC_CTYPE=de_DE.utf8. For graphical applications, a wrapper script is recommended.

The encoding to be used must be installed on your system. You can identify the installed encoding on Linux systems using the command locale -a.

Best practices

Objective Details

Reading big data volumes

Do not use SQLGetData, instead use SQLBindCol and SQLFetch.

To get the best performance, try to fetch about 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 more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 50 and 100 MiB.

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

Autocommit mode

To disable the autocommit mode on Windows systems, use SQLSetConnectAttr() instead of changing the data source settings. If you disable autocommit in the data source settings, the Windows driver manager will not notice this change and assumes that autocommit is switched on. In this case, SQLEndTran() calls will not be passed to the database.