Create a connection

This chapter describes the steps required to create a new connection to the virtual database.

Connections

Navigate to the CONNECTIONS section accessible directly from the sidebar, then on the top right-hand corner please click the ADD CONNECTION button.

Add connection

Provider

Here we define the following parameters: Connection name, Provider and Instance selection mode. Available actions are:

  • CANCEL - it abandons the changes and goes back to the connection list.
  • NEXT - it goes to the next screen.
Provider section

Connection name

A mandatory field that contains a name of the new connection. The name can be searched later on in the main Connections screen.

Provider

Contains the list of the supported providers by Querona. There are few views for configuring the Provider parameters.

Instance selection mode

If you set up a Querona cluster, data and queries are exchanged between the nodes. Depending on the security access rights you might be allowed to connect and run queries against any node. Nodes may be distributed in different physical locations; for instance: one node is in Poland, second in the USA. In such case, it might not be a good idea to allow a USA node to access directly connections available in the node in Poland. Therefore, you can limit connectivity to the designated node with the Instance selection mode.

You might select from following options:

  • Any Querona instance
  • Selected Querona instance
  • Selected network

Provider parameters

This view allows specifying connection parameters. It changes depending on the selected provider.

Is connection trusted and seed value

“is connection trusted” checkbox indicates whether the connection is trusted or not. By default new connections are set as trusted.

Data written by Querona using trusted connection will not participate in data encryption based on data sensitivity settings. Every data written using untrusted connection will cause the sensitive data to be encrypted and made unreadable or fake, depending on data type.

When the connection is set as untrusted (checkbox is unchecked), an integer seed value is required to be entered into “Trusted connection seed” field. The seed value is used to initialize the encryption engine and can be reused among connections that need to encrypt and decrypt data in the same way.

Generic ADO.NET

Parameter Required Default value Description
Dialect Yes   Specifies the connection dialect. The dialects are listed below.
ADO.NET Provider Yes   Specifies the connection dialect. The dialects are listed below.
Connection string Yes Provider dependent The connection string template. Each driver can use a predefined template.
Server name No   The server name.
Username Yes   The name of the login account.
Password Yes   The password for the login account.
Command timeout No   The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Initialization SQL No   SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size Yes   An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.
Generic ADO.NET

MySQL

Parameter Required Default value Description
Server dialect Yes MySQL MySQL Dialect, currently regular MySQL and MariaDb is supported.
Server name Yes   The name of the MySQL Server
Protocol Yes TCP/IP Possible values are TCP/IP and Named pipes.
Port Yes 3306 The MySQL Server port number.
Authentication method Yes Username / password Possible options are Username / password and Windows authentication on the service account.
Username Yes*   The name of the MySQL login account.
Password Yes*   The password for the MySQL login account.
Default database Yes   The default MySQL database. The datasets are listed once the connection has been successfully tested.
Encrypt Yes False Activates SSL encryption for all data sent between the client and server.
Connection timeout No   Specifies the length in seconds to wait for a server connection before terminating the attempt and receive an error.
Command timeout No   Use this one to specify a default command timeout for the connection.
Pooling Yes   Specifies if the pooling mechanism is enabled.
Min pool size Yes** 1 Specifies the minimum amount of available and borrowed (in use) connections that a pool maintains.
Max pool size Yes** 100 Specifies the maximum amount of available and borrowed (in use) connections that a pool maintains.
Additional parameters No   Additional parameters passed to the connection.
Initialization SQL No SET @@SQL_MODE=’’ SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size Yes   An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.

(*) only if the Authentication method is set to Username / password. (**) only if the Pooling is enabled.

MySQL

ODBC

Parameter Required Default value Description
Dialect Yes   Specifies the connection dialect. The dialects are listed below.
Connection string template - use [placeholders] Yes Provider dependent The connection string template. Each driver can use the predefined template.
Server name Yes   The server name.
Username Yes   The name of the login account.
Password Yes   The password for the login account.
Command timeout No   The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Initialization SQL No   SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size Yes   An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.
ODBC

Querona offers build-in dialects for ODBC. Editing existing ones and also adding another can be done from ADMINISTER ‣ SQL Dialects section.

JDBC

Parameter Required Default value Description
Dialect Yes   Specifies the connection dialect. The dialects are listed below.
Connection string template - use [placeholders] Yes Provider dependent The connection string template. Each driver can use the predefined template.
Server name Yes   The server name.
Database No   The database name.
Username Yes   The name of the login account.
Password Yes   The password for the login account.
Command timeout No   The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Initialization SQL No   SQL statements in given provider dialect that will be executed just after the connection was opened.
JDBC

Querona offers some build-in dialects for JDBC. Editing existing ones and also adding another can be done from ADMINISTER ‣ SQL Dialects section.

Oracle

Parameter Required Default value Description
Connection method Yes TNS alias Describes the connection method to the Oracle database. Possible options are: TNS alias, Connect Descriptor and Easy Connect. Each option has its own parameter set that is shown between Connection method and Server dialect parameters.
Server dialect Yes Oracle The dialect used when conversing with Oracle.
Connection pooling enabled Yes False When turned on, this option enables caching the database connections.
Min pool size Yes*   Specifies the minimum amount of available and borrowed (in use) connections that a pool maintains.
Max pool size Yes*   Specifies the maximum number of available and borrowed (in use) connections that a pool maintains.
Increment pool size Yes*   Controls the number of the connections that are established when all the connections in the pool are used.
Decrement pool size Yes*   Controls the number of connections that are closed when an excessive amount of established connections are unused.
Connection timeout No   Maximum time (in seconds) to wait for a free connection from the pool.
Connection lifetime No   Maximum lifetime (in seconds) of the connection.
Command timeout No   Maximum lifetime (in seconds) of the connection.
Enlist (always False) Yes False Enables or disables serviced components to automatically enlist in distributed transactions.
Fetch size (kB) Yes 4096 Rows fetch size in kB between 64kB and 1GB (1048576kB) returned from the database cursor.
Additional parameters No   Additional parameters passed to the connection.
Initialization SQL No   SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size Yes 500 An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.

(*) only when the Connection pooling enabled is checked.

Oracle

TNS alias

This connection method enables connecting with a Transparent Network Substrate (TNS) alias.

Parameter Required Default value Description
TNS Alias name Yes   The name of the TNS Alias.
Username Yes   The name of the Oracle login account.
Password Yes   The password for the Oracle login account.

Connect Descriptor

A specially formatted description of the destination for a network connection. A connect descriptor contains destination service and network route information.

Parameter Required Default value Description
Connect Descriptor - use [placeholders] Yes   Connect Descriptor text.
Username Yes   The name of the Oracle login account.
Password Yes   The password for the Oracle login account.

Easy Connect

Enables clients to connect to a database server without any configuration. Clients use a connect string for a simple TCP/IP address, which includes a host name, port number and service name.

Parameter Required Default value Description
Service name Yes   Specifies the service name.
Host name Yes   Specifies the host name or IP address of the database server computer.
Port Yes 1521 The port number where the server listens for the requests.
Username Yes   The name of the Oracle login account.
Password Yes   The password for the Oracle login account.

Spark

See chapter: Spark connection & management for further information.

SQL Server

Parameter Required Default value Description
Server dialect Yes SQL Server The SQL server dialect. Possible options are SQL Server and Azure SQL Data Warehouse.
Server name Yes   Specifies the server name.
Authentication method Yes Windows authentication on the service account Possible options are Username / password and Windows authentication on service account.
Username Yes*   The name of the SQL Server login account.
Password Yes*   The password for the SQL Server login account.
Connection pooling Yes True Specifies if the pooling mechanism is enabled.
Min pool size 1 Yes**   Specifies the minimum amount of available and borrowed (in use) connections that a pool maintains.
Max pool size 100 Yes**   Specifies the maximum amount of available and borrowed (in use) connections that a pool maintains.
Application intent No   Possible options are Read/Write and Read only.
Connection timeout No   The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Command timeout No   The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Encrypt Yes False Specifies if the connection should be encrypted.
Trust server certificate Yes False Indicates if the channel will be encrypted while bypassing walking the certificate chain to validate trust.
Data Push-down method Yes*** Table Valued Parameter Specifies the data Push-down method. Available options are No push-down and Table Valued Parameter. Data Push-down method is not supported on Azure SQL Data Warehouse dialect.
Initialization SQL No   SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size Yes 0 An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted in each batch; the default value the most recommended for SQL Server;

* only if the Authentication method is set to Username / password.

** only if the Connection pooling is enabled.

*** only if Server dialect is set to SQL Server.

SQL Server

Available actions

The following actions are available:

  • CANCEL - it abandons the changes and goes back to the connection list.
  • TEST CONNECTION - it checks if a connection can be established with given parameter set.
  • NEXT - it goes to the next screen. This button is only available when there is Source database section visible.
  • SAVE - stores the connection. When the connection was not tested the following prompt appears:
Save not tested connection

Source database

This section appears only when SQL Server provider is selected. It has optional Initial catalog selection that lists all the databases visible for given connection.

Note

The selected Initial catalog collation will determine what database can be selected when Create a virtual database.

Source database