You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hop.apache.org by ha...@apache.org on 2024/06/26 16:35:47 UTC

(hop) branch main updated: updated database documentation and minor updates. fixes #3548 (#4089)

This is an automated email from the ASF dual-hosted git repository.

hansva pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/hop.git


The following commit(s) were added to refs/heads/main by this push:
     new 5bce820bdb updated database documentation and minor updates. fixes #3548 (#4089)
5bce820bdb is described below

commit 5bce820bdb2277f3082cc41b03533476a13dfe80
Author: Bart Maertens <ba...@know.bi>
AuthorDate: Wed Jun 26 18:35:42 2024 +0200

    updated database documentation and minor updates. fixes #3548 (#4089)
---
 .../modules/ROOT/pages/database/databases.adoc     | 64 +++++++++++++++++-----
 .../ROOT/pages/database/databases/oracle.adoc      |  5 +-
 2 files changed, 52 insertions(+), 17 deletions(-)

diff --git a/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc b/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
index a74fd1a02a..e3d265e948 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/database/databases.adoc
@@ -21,37 +21,71 @@ under the License.
 :description: Hop supports tens of databases out of the box. If your preferred database has no specific support, you can probably still connect through a generic database connection.
 = Database Plugins
 
-Creating a database connection in HOP is done using one of the many database types available, or you can create a generic connection.
-To create a database connection go to file -> New and select Database connection.
+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 `{openvar}PROJECT_HOME{closevar}/metadata/rdbms` folder.
-Each connection created will generate a .json file in this folder with the name of the connection, containing the connection information.
+If you have set your project to work with Hop, the database information will be in the `{openvar}PROJECT_HOME{closevar}/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.
 
-If the license allowed it, a jdbc driver is included in the distribution, in a folder specific for each driver, in the general path: `Installation directory/plugins/databases/Database type/lib`.
-The documentation of the database type in following subsections shows you whether the driver is included and if it isn't included will guide you to the location on where to download it.
+== Adding JDBC drivers
 
-The `HOP_SHARED_JDBC_FOLDERS` variable can be set before starting Hop to point at a centralized location of your required jdbc drivers.
-This can be set as an OS environment variable or be added to the Hop start script as an exported variable.
-Setting this variable pointed to a folder separated from the Hop installation folder, allows you to keep your drivers versions, no matter which Hop version or installation you use. This variable accepts a comma separated list to point to multiple directories the default value when not set is `lib/jdbc`.
+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.
 
-To avoid conflicts, be sure that for each class of driver, there is only one in any of this folders, `HOP_SHARED_JDBC_FOLDERS`, `hop/plugins/databases/Database type/lib` or `hop/lib/jdbc` 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.
 
-== Generic connection
+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.
 
-HOP can connect to any database that has a jdbc driver available, the list of supplied databases contain some database specific configuration and a list of reserved keywords.
+== 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 `Installation directory/lib` folder, or in the `HOP_SHARED_JDBC_FOLDERS` if you have set this variable.
+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.
+
 
 image::generic_connection.png[Generic Connection Dialog]
 
-Every connection needs a connection name.
+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`.
 
-Then there is a field to specify the username and password.
+== Advanced properties
+
+The advanced tab lets you specify a number of additional properties for your database connection.
+
+[%header, width="90%", cols="1,2"]
+|===
+|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_).
+
+```
+jdbc:sqlserver://localhost:1433;" +
+     "databaseName=AdventureWorks;integratedSecurity=true;" +
+     "encrypt=true;trustServerCertificate=true
+```
 
+[%noheader, width="50%", cols="4,1"]
+|===
+|integratedSecurity|true
+|encrypt|true
+|trustServerCertificate|true
+|===
 
 // tag::website-links[]
 // end::website-links[]
diff --git a/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc b/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
index a3fa19f93b..bb26607037 100644
--- a/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
+++ b/docs/hop-user-manual/modules/ROOT/pages/database/databases/oracle.adoc
@@ -32,8 +32,9 @@ under the License.
 |Driver folder | <Hop Installation>/lib/jdbc
 |===
 
-TIP: Starting with Oracle Database 11g Release 1 (11.1), data type "Date" will be mapped to "Timestamp" by default. 
-Set JDBC property oracle.jdbc.mapDateToTimestamp=false to avoid data type "Date" being converted to data type "Timestamp".
+TIP: Starting with Oracle Database 11g Release 1 (11.1), data type `Date` will be mapped to `Timestamp` by default.
+Set JDBC property `oracle.jdbc.mapDateToTimestamp=false` to avoid data type `Date` being converted to data type `Timestamp`. +
+Check xref:database/databases.adoc#_options[Options] in the Relational Database Connection docs for more information.
 
 == Creating connections