# Relational Database Connections

DA VERIFICARE CON PAOLO

## Database Plugins

Apache Hop has optimized support for many database types. If you are using an unsupported database, you can always create a generic connection.

To create a database connection go to the metadata perspective, right-click on `Relational Database Connections` and select `New`.

The connection is saved in a central location and can then be used by all pipelines and workflows. If you have set your project to work with Hop, the database information will be in the `${PROJECT_HOME}/metadata/rdbms` folder. For each connection, separate a .json file will be generated in this folder. This json file will have the name of the connection and will contain all of the connection information.

### Adding JDBC drivers

Apache Hop includes JDBC drivers for all databases that provide their drivers under a license that is compatible with the Apache Public License. All JDBC drivers that come with Apache Hop can be found in `lib/jdbc` in your Apache Hop installation folder.

The documentation for the various database types in following subsections will show you whether the driver for your database is included. If it included, the connection editor will also show the version of the installed driver. If the driver isn’t included, the documentation will guide you to the download location.

Set the `HOP_SHARED_JDBC_FOLDERS` environment variable to a folder that contains your additional JDBC folders. Having them in a central folder helps you to easily upgrade or change your Apache Hop installation, without the need to add your JDBC drivers every time. This variable accepts a comma separated list to point to multiple directories the default value when not set is `lib/jdbc`, e.g. `<PATH_TO_YOUR_HOP_INSTALLATION>/lib/jdbc,<PATH_TO_YOUR_JDBC_FOLDER>`.

To avoid conflicts, make sure you only have on driver for each database, and make sure you don’t have multiple copies of your drivers, e.g. both in `HOP_SHARED_JDBC_FOLDERS` and `hop/lib/jdbc` folder.

### Generic connection

When a specific database type is not yet available for the database you want to use, you can use the generic connection. To use a generic connection you have to copy your jdbc driver to the `<PATH_TO_YOUR_HOP_INSTALLATION>/lib/jdbc` folder or in your `HOP_SHARED_JDBC_FOLDERS` folder.

![Generic Connection Dialog](https://4045370068-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FyxV8XEI7kgTw22LXcPL6%2Fuploads%2Fgit-blob-5f398d703e6bcb2cb8dceaa4ad275785fe60fb2c%2Fgeneric_connection.png?alt=media)

Check the documentation for your databases' driver class and URL syntax to create your connection.

In the Driver Class field you specify your driver class, for example if you use PostgreSQL the class is `org.postgresql.Driver`.

### Advanced properties

The advanced tab lets you specify a number of additional properties for your database connection.

| Property                                                | Description                                                                                                  |
| ------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------ |
| Supports the Boolean data type                          | is Boolean supported?                                                                                        |
| Supports the Timestamp data type                        | is Timestamp supported?                                                                                      |
| Quote all identifiers in database                       | Add quotes around all identifiers for generated SQL statements.                                              |
| Force all identifiers to lower case                     | Change all identifiers to lower case for generated SQL statements.                                           |
| Force all identifiers to upper case                     | Change all identifiers to upper case for generated SQL statements.                                           |
| Preserve case of reserved words                         | Don’t change the casing for all reserved words for generated SQL statements.                                 |
| The preferred schema name                               | The schema name to use by default (can be overruled).                                                        |
| The SQL statements to run after connection (; separated | a semicolon (';') separated list of SQL statements that need to be executed after the connection is created. |

### Options

The options table contains a list of key/value pairs that can be added to your JDBC driver. Check your database JDBC driver documentation for the right syntax.

For example, to add additional options to a MS SQL database, you can add options to achieve a JDBC URL like the one below. Apache Hop will take care of these properties in the background, there usually is no need to manually modify the JDBC URL (but you *can*, and in a `Generic` connection, you *have to*).

```highlight
jdbc:sqlserver://localhost:1433;" +
     "databaseName=AdventureWorks;integratedSecurity=true;" +
     "encrypt=true;trustServerCertificate=true
```

|                        |      |
| ---------------------- | ---- |
| integratedSecurity     | true |
| encrypt                | true |
| trustServerCertificate | true |


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.primeur.com/data-shaper-1.20/knowing-the-data-shaper-designer/relational-database-connections.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.