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 <[email protected]>
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
