Using the ODBC Driver

Establishing Connection in an ODBC Application

To establish a connection to Exasol through an ODBC driver, you can use any of the following two ODBC functions:

  • SQLConnect()
  • SQLDriverConnect()

SQLConnect()

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

Example call

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

SQLDriverConnect()

In this method, you have following two options:

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

In both of the above options, a connection string is used to define further options. For more information, see Connecting through Connection Strings.

Example call

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

Connecting through 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 have higher priority than the values of the odbc.ini file on Linux/Unix systems or the configuration of the data source on Windows systems.

An example of a connection string to connect to Exasol is given below.

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
        

The supported keys are described in the following table.

Keys Description
ALWAYSSEARCHPATTERNSINCAT

All search patterns in the catalog function can contain ODBC search patterns. The value of Y activates this.

ANSIARGENCODING

Sets the encoding of the arguments of ANSI functions to the given value. For example - ANSIARGENCODING=UTF-8.

ANSIDATAENCODING

Sets the encoding of ANSI string (SQL_C_CHAR) data to the given value. This data can be, for example, the data of prepared parameter values.

AUTOCOMMIT

Autocommit mode of the connection. Valid values are Y and N, and the default value is Y.

COGNOSSUPPORT

If you want to use the Exasol ODBC driver in combination with Cognos, we recommend to set on this option to Y. The default is N.

CONNECTIONLCCTYPE

Sets LC_CTYPE to the given value during the connection. Here are some examples:

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

Sets LC_NUMERIC to the given value during the connection. Here are some examples:

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

The maximum time (in milliseconds) the driver waits to establish a TPC connection to a server. This timeout is required to limit the overall login time especially in case of a large cluster with several reserve nodes. The default value is 2000.

DEFAULTPARAMSIZE

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

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 ANSIARGENCODING, ANSIDATAENCODING, UNICODEARGENCODING, and UNICODEDATAENCODING to the given value.

ENCRYPTION

Switches on the automatic encryption. Valid values are Y and N, and the default value is Y.

EXAHOST

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 randomly chooses 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 also define a comma-separated list of host:port pairs. For example:

Defines the servers and the port of the Exasol cluster (for example, 192.168.6.11..14:8563). You can also have a hostname mapped to 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/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Single server with name myhost and port 8563. Or single host myhost with multiple IP addresses mapped to it, fingerprint /72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
  • myhost1,myhost2/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Two servers with fingerprint /72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30 and port 8563.
  • myhost1..4/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Four servers (myhost1, myhost2, myhost3, myhost4), fingerprint /72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
  • 192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Four servers from 192.168.6.11 to 192.168.6.14, fingerprint /72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.
  • 192.168.6.11..14/72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30:8563: Four servers from 192.168.6.11 to 192.168.6.14, fingerprint /72975B4D331DEEB44FA41FB858A5417E163151BBFB45376574706800A8DCCE30, and port 8563.

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.

EXALOGFILE

Log file for the driver.

Note:

  • Depending on the log mode, the size of the log file can be huge. This is not recommended in productive environment.
  • Do mention that the log file can possibly contain sensitive data (SQL commands and data).
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.

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 Y activates this.

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.

The valid values are Y and N, and the default is N.

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 exasol is used as default.

LEGACYCATALOG

The ODBC catalog functions will use SQL statements to generate their results. The value of Y activates this.

LOGMODE

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

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

MAXPARAMSIZE

Maximum size for VARCHAR parameters in prepared statements, even if the determined data type was bigger. Use value 0 for deactivating. The default is 0.

PREPAREAS

Prepare always successful: SQLPrepare and SQLPrepareW always return SQL_SUCCESS. Valid values are Y and N, and the default is N.

QUERYTIMEOUT

Defines the query timeout in seconds for a connection. If set to 0, the query timeout is deactivated. The default is value 0.

SHOWONLYCURRENTSCHEMA

Defines whether the ODBC driver considers all the schemas or just the current schema for metadata like the list of columns or tables. The valid values are Y and N and the default is N.

SILENT

If an Exasol ODBC DSN is configured using odbcconf.exe, you can use parameter SILENT to stop showing the Exasol ODBC configuration dialog.

To enable this option add SILENT=Y to the DSN parameter.

SNAPSHOTTRANSACTIONS

The parameter defines the transaction Snapshot Mode for the connection. The default value for the parameter is N.

Set the parameter to Y to enable Snapshot Mode for system tables.

SQLSTATEMAPPINGACTIVE

The parameter activates the mapping for ODBC SQL states received for errors. To enable this, set .SQLSTATEMAPPINGACTIVE=Y

The default mapping string is given below.

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

SQLSTATEMAPPINGS

The parameter specifies a mapping state for SQL states. It is used only if SQLSTATEMAPPINGACTIVE value is set as Y. If the value is set, the new values are used first and then the default values.

STRINGSNOTNULL

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

Note: The database internally doesn't distinguish between NULL and empty strings and returns in both cases NULL values to the driver.

The valid values are Y and N, and the default is N.

SUPERCONNECTION

Enables the execution of queries even if the limit for active sessions (executing a query) is reached. Valid values are Y and N, and the default is N.

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
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.

AUTHMETHOD

Specifies the authentication method for OpenID connections.

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.

SSLCERTIFICATE

The name and path of the certificate file (cert.pem) used by SSL.

You can use SSL_VERIFY_NONE to disable server verification and SSL_VERIFY_SERVER to enable it. By default the server certificate check is enabled.

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 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 about TLS certificate upload, see Upload TLS Certificate.

USELEGACYENCRYPTION Uses ChaCha encryption instead of SSL.
LOGINTIMEOUT Specifies connection timeout for each connection attempt. If multiple hosts are specified in the connection string, each host waits for the specified time for a successful connection.

Supported Standards

The driver supports 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 unicode character set UTF-8. 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 which is sent to the ODBC driver should be in an encoding which is installed locally. You can specify the encoding through the language settings of the connection.

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

It is important that the used encoding is installed on your system. You can identify the installed encoding through 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-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, ..., use more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 50 and 100 MB.

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

Autocommit mode

You should deactivate the autocommit mode on Windows systems only through the method SQLSetConnectAttr() and not in the data source settings.

If you deactivate in the data source setting, the windows driver manager doesn't notice this change and assumes that autocommit is switched on and doesn't pass SQLEndTran() calls to the database. This behavior could lead to problems.

Problems using the localization attributes

If the settings CONNECTIONLCCTYPE or CONNECTIONLCNUMERIC do not work, the connect function will return SQL_SUCCESS_WITH_INFO and a corresponding text is added in DiagRec.