Using the ODBC Driver
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.
Key | Description |
---|---|
ALWAYSSEARCHPATTERNSINCAT
|
All search patterns in the catalog function can contain ODBC search patterns. The value of |
ANSIARGENCODING
|
Sets the encoding of the arguments of ANSI functions to the given value. For example - |
ANSIDATAENCODING
|
Sets the encoding of ANSI string |
AUTHMETHOD
|
Specifies the authentication method for OpenID connections. If the value for the parameter is If the value for the parameter is For more information, see Authentication using OpenID. |
AUTOCOMMIT
|
Autocommit mode of the connection. Valid values are Default: |
COGNOSSUPPORT
|
If you want to use the Exasol ODBC driver in combination with Cognos,
we recommend to set this option to Default: |
CONNECTIONLCCTYPE
|
Sets
|
CONNECTIONLCNUMERIC
|
Sets
|
DEFAULTPARAMSIZE
|
Default size for Default: |
DRIVERUNICODETYPE
|
If the client uses the DataDirect driver manager, this parameter specifies the type of Unicode to be used in W-functions. Valid values for this option are "1" (UTF-16) or "2" (UTF-8). |
ENCODING
|
Sets |
ENCRYPTION
|
Switches on the automatic encryption. Valid values are Default: |
EXAHOST
|
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:
You can also specify the path to a file that contains a host list, for example, To disable TLS encryption you can use the NOCERTCHECK option. NOCERTCHECK is case insensitive. For example:
|
EXALOGFILE
|
Log file for the driver. Note:
|
EXAPWD or PWD |
Password of user. PWD is automatically removed from the connection string by some applications. |
EXASCHEMA
|
Schema that is opened directly after the connection. |
EXAUID or UID |
Username for the login. UID is automatically removed from the connection string by some applications. |
FEEDBACKINTERVAL
|
During a query the server sends feedback to the client at set intervals. This feedback:
Default: A feedback interval set too high may cause a long lag time before a command is canceled. |
FINGERPRINT
|
If TLS validation fails and you trust the server, use this parameter to include the server fingerprint in the connection string. For example:
To bypass TLS certificate checking, use the
A connection string must only contain a single fingerprint, specified either after the host or port or in a |
HOSTTIMEOUT
|
Specifies the connection timeout for each TCP connection attempt in milliseconds. If multiple hosts are specified in the connection string, each host will wait for the specified time for a successful connection. Default: |
IGNOREUNKNOWNATTR
|
The driver will not return the SQL_ERROR if the client uses an unknown environment, connection, or statement. The client will ignore the command. The value of |
IGNOREPARAMS
|
Comma-separated list of string parameters to be ignored by the driver’s spell checker. Example: |
INTTYPESINRESULTSIFPOSSIBLE
|
If you switch on this option, then DECIMAL types without scale will be
returned as SQL_INTEGER ( 9 digits) or SQL_BIGINT ( 18 digits) instead
of SQL_DECIMAL. Valid values are Default: |
KERBEROSHOSTNAME
|
Host name of the Kerberos service. If nothing is specified, the host name of the parameter EXAHOST is used as default. |
KERBEROSSERVICENAME
|
Principal name of the Kerberos service. If nothing is specified, the name |
LEGACYCATALOG
|
The ODBC catalog functions will use SQL statements to generate their results. The value of |
LOGINTIMEOUT
|
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: 0 (infinite) |
LOGMODE
|
Specifies the mode for the log file. The following options are available:
|
MAXHANDLES
|
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: |
MAXPARAMSIZE
|
Maximum size for Default:
|
PREPAREAS
|
Prepare always successful: Default: |
QUERYTIMEOUT
|
Defines the query timeout in seconds for a connection. If set to Default: |
SHOWONLYCURRENTSCHEMA
|
Defines whether the ODBC driver considers all the schemas or just the current
schema for metadata like the list of columns or tables. Valid values are Default: |
SILENT
|
If an Exasol ODBC DSN is configured using To enable this option add |
SNAPSHOTTRANSACTIONS
|
Changes the snapshottransaction setting in the session. Possible values:
If not set, the default in the session is used. |
SQLSTATEMAPPINGACTIVE
|
The parameter activates the mapping for ODBC SQL states received for errors. To enable this, set The default mapping string is given below.
|
SQLSTATEMAPPINGS
|
The parameter specifies a mapping state for SQL states. It is used only if |
SSLCERTIFICATE
|
The name and path of the certificate file ( You can use If the SSL certificate is not verified or a client does not have the certificate, the connection fails. To overcome this problem, you could use a fingerprint in the connection string that allows the connection. For fingerprint based verification, the connection string is |
STRINGSNOTNULL
|
Defines whether the ODBC driver returns empty strings for NULL
strings when reading table data. Valid values are Default:
Note: The database internally doesn't distinguish between NULL and empty strings and returns in both cases NULL values to the driver. |
SUPERCONNECTION
|
Enables the execution of queries even if the limit for active sessions (executing a query) is reached. Valid values are Default: Note:
|
UNICODEARGENCODING
|
Sets the encoding of the arguments of W-functions to the given value. |
UNICODEDATAENCODING
|
Sets the encoding of Unicode string (SQL_C_WCHAR) data to the given value. This data can be, for example, the data of prepared parameter values. |
LEGACYENCRYPTION
|
Uses ChaCha encryption instead of SSL. |
Supported Standards
The Exasol ODBC driver supports ODBC 3.5 standard (core level).
The following features are not supported:
-
Positioned update/delete
andbatched 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.utf
8
. 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 To get the best performance, try to fetch about 50 to 100 MB of data by choosing the number of rows per |
Inserting data into the database |
Instead of using single insert statements like Insert the data using the native data types. For example, for the number |
Autocommit mode |
To disable the autocommit mode on Windows systems, use |