ADO.NET Data Provider

Specifies the authentication method for OpenID connections.

• If the value for the parameter is accesstoken, the connection string will be "Server=<host>;Port=<port>;AuthMethod=accesstoken;PWD=<access_token>" and the password will be your OpenID Access Token.

• If the value for the parameter is refreshtoken, the connection string will be "Server=<host>;Port=<port>;AuthMethod=refreshtoken;PWD=<refresh_token>" and the password will be your OpenID Refresh Token.

For more information, see Authentication using OpenID.

Enables/disables autocommit.

Default: ON

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.

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

Server=exadb1.example.com:8563;FingerPrint=<fingerprint>;

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:

• Server=exadb1.example.com/NOCERTCHECK:8563
• Server=exadb1.example.com:8563/nocertcheck
• Server=exadb1.example.com:8563;FingerPrint=NoCertCheck;

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

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)

Path to a log file for the driver. For example: logfile='C:\tmp\ado.log'

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

Default: empty (logging disabled)

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)

SQL string that is executed directly after the connection is established. If an error occurs, the connection will be aborted.

The TCP port for Exasol.

This port is used if you did not specify a port in the host parameter.

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

Default: 0 (infinite)

The schema to be opened after login.

If the schema cannot be opened, the login fails.

Defines the servers, fingerprint, and port of the Exasol cluster to connect to. For example:

192.168.6.11..14/<fingerprint>:8563).

If host names are specified there, they can have DNS entries with multiple IP addresses.

Enables/disables snapshot transactions for the session.

Default: session default

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

Possible values:

• VerifyServer = The driver verifies the certificate in the SSL connection (default).
• VerifyNone = 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:

Server=exadb1.example.com/<fingerprint>;Port=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.

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

Instead of using single insert statements like "INSERT INTO t VALUES 1, 2, ..." you should use more efficient interface of prepared statements and their parameters. Prepared statements achieve optimal performance when using parameter sets between 500 KiB and 20 MiB. Moreover you should insert the data by using the native data types.

For execution of prepared statements we recommend using the IParameterTable interface. You can create an instance of ParameterTable with a "Command" through CreateParameterTable() method.

Exasol ADO.NET provides a special interface to execute prepared statements (class ParameterTable) since it is not possible to update rows in Exasol through the ADO.NET specific interfaces DataSet and DataAdapter.

Executing parametrized queries is done by using SQL statements including question marks at the position of parameters. The values of the ParameterTable are used to replace the parameters when executing the statement.

Example for a parametrized query is given below:

using (EXACommand command = (EXACommand)connection.CreateCommand())
{
   command.CommandText = "CREATE OR REPLACE TABLE TEST (I INT, TS TIMESTAMP)";
   command.ExecuteNonQuery();
   command.CommandText = "INSERT INTO TEST VALUES (?, ?)"; 
   command.Prepare();
   IParameterTable tab = command.CreateParameterTable(); 
   tab.AddColumn(DbType.Int32); 
   tab.AddColumn(DbType.String);
   for (int i = 0; i < 10; i++)
   {
     int currentRow = tab.AppendRow(); 
     tab[0].setInt32(currentRow, i); 
     tab[1].setString(currentRow, "2017-06-30 11:21:13." + i);
   }
   command.Execute(tab);
}

Note: The two options to specify the decimal types are:

• AddColumn(DbType.Decimal): When using this general method, the internal type DECIMAL(36,8) is used.
• AddDecimalColumn(p,s): When using this specific method, the internal type DECIMAL(p,s) is used (36 digits as maximum).