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:
The ODBC driver for all these distributions for Windows OS (standalone version) or Ubuntu LTS 22.04 Linux OS (cloud 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 |
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 |
Default |
|---|---|---|---|
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.