Database Resource#

A Database connection is a set of connection parameters, required to connect to a SQL database distribution and execute SQL query statements on it.

This connection can be stored in two ways:

related to the object which is going to use it (Custom source type), or
as a Rulex Platform Resource of Database (Saved source type) type, which can be used at any time and in any operation this connection is needed.

If the database connection is stored as a Database Resource, the user can define a set of permissions linked to this resource - as explained in the permissions page - to limit/control the access other users have on the inserted connection.

To use a database resource in any panel a database connection is required, the user must have the view permission set to allow on the resource itself, while modify permissions are required to see its internal details.

Tip

By creating a Database Resource with view permissions for all users and modify permissions for the administrator only, you are exposing a connection without communicating username and password used in the authentication procedure.

Database connection configuration#

Rulex Platform uses the ODBC connection interface to connect to any SQL database.

However, SQL syntax and procedure are not completely equal among the various distributions. For this reason, Rulex Platform provides a simplified interface for a set of supported databases and takes care for you when dealing with technicalities such as escaping, exceptions, etc.

Any database supporting the ODBC standard and is not available in the list can be connected using the generic case.

Warning

By using the Generic ODBC Driver connection, the user needs to manage connection string escaping, unusual entry tags or not supported standard statements.

The list of supported SQL database distribution is the following:

SQLite
Oracle
MySQL
SQL Server
PostegreSQL
IBM_DB2
IBM DB2 AS400
Azure Synapse Analytics
Impala
Spark
Databricks
Hive
Teradata
OpenText Gupta SQLBase
Microsoft Access
SAP HANA
Generic ODBC Connection

The ODBC driver for all these distributions for Windows OS (standalone version) or Ubuntu LTS 22.04 Linux OS (cloud/server version) can be found here:

SQLite

To connect Rulex Platform to a SQLite database you first need to install the correct ODBC driver on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a SQLite database in Rulex Platform API call use the value SQLite in type field.

Name	Description	API param key	Default
Database Path	The path to the SQLite database file.	name
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains SQLite in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Oracle

To connect Rulex Platform to an Oracle database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to an Oracle database in Rulex Platform API call you must use the value Oracle in type field.

Name	Description	API param key	Default
Host	The server host of the chosen Oracle database.	host
Port	The server port of the chosen Oracle database.	port
Authentication	The type of authentication you want to achieve: one of Username/Password (0) or Windows Authentication (1).	auth
Username	If an authentication is required, enter the username exchanged with the Oracle database (only if Authentication is Username/Password).	username
Password	If an authentication is required, enter the password exchanged with the Oracle database (only if Authentication is Username/Password).	password
Service Name	The name of the Oracle Service exposing the chosen Oracle database.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Oracle in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

MySQL

To connect Rulex Platform to a MySQL database you first need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a MySQL database in Rulex Platform API call use the value MySQL in type field.

Name	Description	API param key	Default
Host	The server host of the chosen MySQL database.	host
Port	The server port of the chosen MySQL database.	port
Username	If an authentication is required, enter the username exchanged with the MySQL database.	username
Password	If an authentication is required, enter the password exchanged with the MySQL database.	password
Database Name	The name of the selected database in the MySQL cluster.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains MySQL in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

SQL Server

To connect Rulex Platform to a SQL Server database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a SQL Server database in Rulex Platform API call use the value SQL Server in type field.

Name	Description	API param key	Default
Host	The server host of the chosen SQL Server database.	host
Port	The server port of the chosen SQL Server database.	port
Authentication	The type of authentication you want to achieve: one of Username/Password (0) or Windows Authentication (1).	auth
Username	If an authentication is required, enter the username exchanged with the SQL Server database (only if Authentication is Username/Password).	username
Password	If an authentication is required, enter the password exchanged with the SQL Server database (only if Authentication is Username/Password).	password
Database Name	The name of the selected database in the SQL Server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains SQL Server in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

PostgreSQL

To connect Rulex Platform to a PostgreSQL database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a PostgreSQL database in Rulex Platform API call use the value PostgreSQL in the type field.

Name	Description	API param key	Default
Host	The server host of the chosen PostgreSQL database.	host
Port	The server port of the chosen PostgreSQL database.	port
Authentication	The type of authentication you want to achieve: one of Username/Password (0) or Postgres Pass File (1).	auth
Username	If an authentication is required, enter the username exchanged with the PostgreSQL database (only if Authentication is Username/Password).	username
Password	If an authentication is required, enter the password exchanged with the PostgreSQL database (only if Authentication is Username/Password).	password
Database Name	The name of the selected database in the PostgreSQL server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains PostgreSQL in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

IBM DB2

To connect Rulex Platform to an IBM DB2 database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to an IBM DB2 database in Rulex Platform API call use the value IBM_DB2 in the type field.

Name	Description	API param key	Default
Host	The server host of the chosen IBM DB2 database.	host
Port	The server port of the chosen IBM DB2 database.	port
Username	If an authentication is required, enter the username exchanged with the IBM DB2 database.	username
Password	If an authentication is required, enter the password exchanged with the IBM DB2 database.	password
Database Name	The name of the selected database in the IBM DB2 server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains DB2 in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

IBM DB2 AS400

To connect Rulex Platform to an IBM DB2 AS400 database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to an IBM DB2 AS400 database in Rulex Platform API call use the value IBM DB2 AS400 in type field.

Name	Description	API param key	Default
Host	The server host of the chosen IBM DB2 AS400 database.	host
Port	The server port of the chosen IBM DB2 AS400 database.	port
Username	If an authentication is required, enter the username exchanged with the IBM DB2 AS400 database.	username
Password	If an authentication is required, enter the password exchanged with the IBM DB2 AS400 database.	password
Database Name	The name of the selected database in the IBM DB2 AS400 server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains DB2 in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Azure Synapse Analytics

To connect Rulex Platform to an Azure Synapse Analytics database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to an Azure Synapse Analytics database in Rulex Platform API call use the value Azure Data Warehouse in type field.

Name	Description	API param key	Default
Host	The server host of the chosen Azure Synapse Analytics database.	host
Port	The server port of the chosen Azure Synapse Analytics database.	port
Authentication	The type of authentication you want to achieve: one of Username/Password (0) or Windows Authentication (1).	auth
Username	If an authentication is required, the username exchanged with the Azure Synapse Analytics database (only if Authentication is Username/Password).	username
Password	If an authentication is required, the password exchanged with the Azure Synapse Analytics database (only if Authentication is Username/Password).	password
Database Name	The name of the selected database in the Azure Synapse Analytics.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains SQL Server in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Impala

To connect Rulex Platform to an Impala database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to an Impala database in Rulex Platform API call use the value Impala in the type field.

Name	Description	API param key	Default
Host	The server host of the chosen Impala database.	host
Port	The server port of the chosen Impala database.	port
Username	If an authentication is required, enter the username exchanged with the Impala database.	username
Password	If an authentication is required, enter the password exchanged with the Impala database.	password
Database Name	The name of the selected database in the Impala server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Impala in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Spark

To connect Rulex Platform to a Spark database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a Spark database in Rulex Platform API call use the value Spark in type field.

Name	Description	API param key	Default
Host	The server host of the chosen Spark database.	host
Port	The server port of the chosen Spark database.	port
Username	If an authentication is required, enter the username exchanged with the Spark database.	username
Password	If an authentication is required, enter the password exchanged with the Spark database.	password
Database Name	The name of the selected database in the Spark server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Spark in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Spark

To connect Rulex Platform to a Databricks database you need to have the correct ODBC driver installed on your machine.

Name	Description	API param key	Default
Host	The server host of the chosen Spark database.	host
Port	The server port of the chosen Spark database.	port
Username	If an authentication is required, enter the username exchanged with the Databricks database.	username
Password	If an authentication is required, enter the password exchanged with the Databricks database.	password
Database Name	The name of the selected database in the Databricks server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Databricks in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Hive

To connect Rulex Platform to a Hive database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a Hive database in Rulex Platform API call use the value Hive in type field.

Name	Description	API param key	Default
Host	The server host of the chosen Hive database.	host
Port	The server port of the chosen Hive database.	port
Username	If an authentication is required, enter the username exchanged with the Hive database.	username
Password	If an authentication is required, enter the password exchanged with the Hive database.	password
Database Name	The name of the selected database in the Hive server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Hive in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Teradata

To connect Rulex Platform to a Teradata database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a Teradata database in Rulex Platform API call use the value Teradata in type field.

Name	Description	API param key	Default
Host	The server host of the chosen Teradata database.	host
Port	The server port of the chosen Teradata database.	port
Username	If an authentication is required, enter the username exchanged with the Teradata database.	username
Password	If an authentication is required, enter the password exchanged with the Teradata database.	password
Database Name	The name of the selected database in the Teradata server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Teradata in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

OpenText Gupta SQLBase

To connect Rulex Platform to a OpenText Gupta SQLBase database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to an OpenText Gupta SQLBase database in Rulex Platform API call use the value SQLBase in type field.

Name	Description	API param key	Default
Host	The server host of the chosen OpenText Gupta SQLBase database.	host
Port	The server port of the chosen OpenText Gupta SQLBase database.	port
Username	If an authentication is required, enter the username exchanged with the OpenText Gupta SQLBase database.	username
Password	If an authentication is required, enter the password exchanged with the OpenText Gupta SQLBase database.	password
Database Name	The name of the selected database in the OpenText Gupta SQLBase server.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains SQLBase in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Microsoft Access

To connect Rulex Platform to a Microsoft Access database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a Microsoft Access database in Rulex Platform API call use the value Access in type field.

Name	Description	API param key	Default
Database Path	The path to the Microsoft Access database file.	name
Username	If an authentication is required, enter the username exchanged with the Microsoft Access database.	username
Password	If an authentication is required, enter the password exchanged with the Microsoft Access database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains Access in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

SAP Hana

To connect Rulex Platform to a SAP Hana database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection to a SAP Hana database in Rulex Platform API call use the value SAP Hana in type field.

Name	Description	API param key	Default
Host	The server host of the chosen SAP Hana database.	host
Port	The server port of the chosen SAP Hana database.	port
Authentication	The type of authentication you want to achieve: one of Username/Password (0) or Windows Authentication (1).	auth
Username	If an authentication is required, enter the username exchanged with the SAP Hana database. (only if Authentication is Username/Password)	username
Password	If an authentication is required, enter the password exchanged with the SAP Hana database. (only if Authentication is Username/Password)	password
Database Name	The name of the selected database in the SAP Hana.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver	the first driver name on the ODBC machine list which contains SAP Hana in its name.
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Generic ODBC Connection

To connect Rulex Platform to a Generic ODBC database you need to have the correct ODBC driver installed on your machine.

The connection parameters needed for this connection are reported in the following table (parameters in bold are mandatory). To define a Custom connection towards the ODBC database in Rulex Platform API call use the value generic in type field.

Name	Description	API param key
Host	The server host of the chosen Generic ODBC database.	host
Port	The server port of the chosen Generic ODBC database.	port
Authentication	The type of authentication you want to achieve: one of Username/Password (0) or Windows Authentication (1).	auth
Username	If an authentication is required, enter the username exchanged with the Generic ODBC database (only if Authentication is Username/Password).	username
Password	If an authentication is required, enter the password exchanged with the Generic ODBC database (only if Authentication is Username/Password).	password
Database Name	The name of the selected database in the Generic ODBC.	name
Schema	The database schema selected in the target database.	password
ODBC Driver	The name of the ODBC Driver to use.	driver
Additional options	Extra connection parameter to be inserted as is in the connection string.	connstring

Creating a connection to a database#

To create a Database Resource, you need to open the Explorer panel (for more information on the Explorer refer to this page) and follow the procedure below:

Procedure

Click the Explore Resources icon to open the Explorer panel.
Deactivate the primary resource filter by toggling off the Primary filter on the upper right side of the Explorer panel to add general resources.
Hover over the Plus button.
Select Add new Database and a dedicated window will appear on the screen.
Select the database you want to connect to (see supported types).
Configure the required connection parameters following this guide. A test connection will be performed; if unsuccessful, an error message is returned on the interface, providing you with the possibility to continue anyway.
Type a unique name for the new resource.
Click Create: the new Database resource will now be added to the list.

Once the Database resource is defined, it can be referred as a Saved source in any location where a database connection can be used.

As an example, below you’ll find the most important Database Saved connection applications:

In any of the applications above it is still possible to set a Custom connection, specifying the parameters listed in the configuration section.

Database	ODBC download page (Windows OS)	ODBC download page (Linux OS)
SQLite	http://www.ch-werner.de/sqliteodbc/	https://pkgs.org/search/?q=libsqliteodbc
Oracle	https://www.oracle.com/it/database/technologies/releasenote-odbc-ic.html,	https://www.oracle.com/it/database/technologies/releasenote-odbc-ic.html
SQL Server	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16,	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16
My SQL	https://dev.mysql.com/downloads/connector/odbc/,	https://dev.mysql.com/downloads/connector/odbc/
PostgreSQL	https://www.postgresql.org/download/windows/,	https://pkgs.org/download/postgresql-odbc
IBM_DB2	https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads,	https://www.ibm.com/docs/en/db2-big-sql/7.0?topic=drivers-odbc-driver-linux
IBM_DB2 AS400	https://www.ibm.com/docs/en/db2-big-sql/7.0?topic=drivers-odbc-driver-windows,	https://www.ibm.com/docs/en/db2-big-sql/7.0?topic=drivers-odbc-driver-linux
Azure Synapse Analytics	https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/connection-strings#sample-odbc-connection-string,	https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16
Impala	https://www.cloudera.com/downloads/connectors/impala/odbc/2-6-11.html,	https://www.cloudera.com/downloads/connectors/impala/odbc/2-6-11.html
Spark	https://www.databricks.com/spark/odbc-drivers-download	https://www.databricks.com/spark/odbc-drivers-download
Databricks	https://www.databricks.com/spark/odbc-drivers-download	https://www.databricks.com/spark/odbc-drivers-download
Hive	https://www.microsoft.com/en-us/download/details.aspx?id=40886	https://www.cloudera.com/downloads/connectors/hive/odbc/2-6-9.html
Teradata	https://downloads.teradata.com/download/connectivity/odbc-driver/windows	https://downloads.teradata.com/download/connectivity/odbc-driver/linux
OpenText Gupta SQLBase	https://www.opentext.com/products/gupta-sqlbase,	https://www.opentext.com/products/gupta-sqlbase
Microsoft Access	https://www.microsoft.com/it-it/download/details.aspx?id=13255	https://pkgs.org/search/?q=libmdbodbc
SAP Hana	https://tools.hana.ondemand.com/#hanatools	https://tools.hana.ondemand.com/#hanatools