easyTravel - using other RDBMSes

Karolina_Linda — Fri, 29 Sep 2023 13:17:28 GMT

By default, easyTravel uses an internal Derby Database on the Java side and an SQL Server Compact internal database on the .NET side. However, you can change this to use other types of RDBMS, this page describes how you can configure this.

Java

Connection settings

In the EasyTravel installation (dist) folder, search for this file:

resources/easyTravelConfig.properties

These properties control the Database:

# for starting the internal Derby database in the Launcher config.internalDatabaseEnabled=true config.internalDatabasePort=1527 # how to connect to the Java database, user/password can be empty on Derby config.databaseDriver=org.apache.derby.jdbc.ClientDriver config.databaseUrl=jdbc:derby://localhost:1527/easyTravelBusiness;create=true config.databaseUser=APP config.databasePassword=APP

The above values are part of the standard easyTravel installation.

They can be changed to use any database desired. The following sets of properties have been
tested against DB/2, SQLServer and Oracle DBMS.

⚠️If external RDBMS is on the same machine, the property config.internalDatabasePort=1527
must point to some free port. easyTravel must start a derby instance, even when it is using external RDBMS.

DB/2

config.internalDatabaseEnabled=false config.databaseDriver=com.ibm.db2.jcc.DB2Driver config.databaseUrl=jdbc:db2://dbserv:50000/easyTravelBusiness config.databaseUser=easytraveluser config.databasePassword=labpass

Oracle

⚠️Oracle versions 12 is only supported in easyTravel builds 2542 or higher due to a required newer version of Hibernate (see HHH-9044).

config.internalDatabaseEnabled=false config.databaseDriver=oracle.jdbc.OracleDriver config.databaseUrl=jdbc:oracle:thin:$user_name/$user_pass@dbserv:1521:easyTravelBusiness config.databaseUser=easytraveluser config.databasePassword=labpass

⚠️ easyTravelBusiness is an Oracle SID $user_name and $user_pass should be filled with real values

Using Oracle service:

config.databaseUrl=jdbc:oracle:thin:@//dbhost:1521/easytravel1

User and password must be provided as parameters. easytravel1 is service name

Warning! Due to issue ORA-01882: timezone region not found while connecting to Oracle workaround there is workaround, jvm parameters have to contain -Duser.timezone=GMT. Of course it should be adjusted to your timezone. Example is below:

config.javaopts=-Djava.util.logging.config.file=../resources/logging.properties,-Duser.timezone="+01:00" config.frontendJavaopts=-Xmx160m,-Duser.timezone="+01:00" config.backendJavaopts=-Xmx64m,-Duser.timezone="+01:00"

also files launching jvm like weblauncher/weblauncher.sh should look like below:

$JAVA_BIN $COVERAGE -Duser.timezone='+01:00' -Dcom.dynatrace.easytravel.install.dir.correction=.. -jar ../com.dynatrace.easytravel.weblauncher.jar "$@"

Example code for the DB user creation:

create user easytraveluser identified by labpass; grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE to easytraveluser;

SQL Server

ℹ️Locale

Note that easyTravel will insert a number of locations with non-ASCII characters as part of the name, e.g. Århus. Make sure you set up the SQL Server database with a local that supports these characters.

SQL_Latin1_General_CP1_CI_AS is reported to work, also any UTF-8 or UTF-16 based encoding should be fine.

There are at least two different JDBC drivers available for SQL Server, depending on which one you want to use the following settings apply:

SQL Server with JTDS-JDBC Driver

config.internalDatabaseEnabled=false config.databaseDriver=net.sourceforge.jtds.jdbc.Driver config.databaseUrl=jdbc:jtds:sqlserver://dbserv5:1433/easyTravelBusiness;instance=dynasqlserver config.databaseUser=easytraveluser config.databasePassword=labpass

⚠️ Parameter 'instance' is optional and can be omitted.

There are two stored procedures used by easyTravel but not created automatically:

* sp_findJourneys - if you intend to use the FindJourneysSqlServerQueryOverride easyTravel plugin, you can either setup the sp_findJourneys stored procedure yourself, or use the provided source file sp_findJourneys.sql, which is located in the plugin's JAR file root.

* sp_verifyLocation - used by SlowAuthentication plugin. You can create the procedure using provided source file in the sp_verifyLocation.sql which is located in the plugin's JAR file root.

SQL Server with Microsoft JDBC Driver

config.internalDatabaseEnabled=false config.databaseDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver config.databaseUrl=jdbc:sqlserver://[dbhostname]:1433;databaseName=easyTravelBusiness config.databaseUser=easytravel config.databasePassword=easytravel

easyTravel cannot ship the Microsoft JDBC driver because of license considerations. To make it work, you need to complete the following steps:

Download the MS SQL JDBC drivers at: http://go.microsoft.com/fwlink/?LinkId=245496
Unpack "mssql-jdbc-7.0.0.jre8.jar" to C:\Program Files\dynaTrace\easyTravel (x64)\lib
Unpack "META-INF/MANIFEST.MF" from "com.dynatrace.easytravel.database.jar" in your main easyTravel directory
Add "lib/mssql-jdbc-7.0.0.jre8.jar" to the end of the Class Path in MANIFEST.MF
Overwrite the MANIFEST.MF in "com.dynatrace.easytravel.database.jar" with your changed file.

⚠️If you are using a new edition of SQL Server you might encounter an error which looks like "Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'". In such a case, you need to:

Unpack "META-INF/persistence.xml" from "com.dynatrace.easytravel.database.jar" in your main easyTravel directory
Modify this file by adding a new property in the properties section:

Overwrite the persistence.xml in "com.dynatrace.easytravel.database.jar" with your changed file.

MySQL

⚠️Using easyTravel with MySQL leads to errors about duplicate entries with special characters in the Journey/Location-Name, e.g. "Ibagué" and "Ibague"! (Detailed informations: https://bugs.mysql.com/bug.php?id=57877). There are two possible solutions for this:

Setting collation to utf8_bin, as described below should remove this problem (tested with MySQL 5.5, 5.6).
You can try to adjust the file "data/Cities.txt" inside the file com.dynatrace.easytravel.database.jar, by removing following cities: Vitória, Ibagué, Århus, Székesfehérvár, Mérida, Gdañsk, Córdoba, Skövde, Tabor, Malmo, Villa Maria.

config.internalDatabaseEnabled=false config.databaseDriver=com.mysql.jdbc.Driver config.databaseUrl=jdbc:mysql://<client-host-ip>/easyTravelBusiness config.databaseUser=root config.databasePassword=labpass

How to configure MySQL

Create a database and allow access from other machines:
Adjust config at /etc/mysql/my.cnf, in section [mysqld]:

add
character-set-server = utf8
collation-server = utf8_bin
comment out
bind-address = 127.0.0.1

mysql -u root -p create database easyTravelBusiness; GRANT ALL PRIVILEGES ON *.* TO root@'<client-host>' IDENTIFIED BY 'labpass';

http://dev.mysql.com/doc/refman/5.0/en/, Install MySQL on Ubuntu 14.04, Install MySQL on Ubuntu 16.04

Database schema

easyTravel will try to automatically create the schema, which works for built-in Derby and the above mentioned DBMS. This is done with the hibernate feature hibernate.hbm2ddl.auto set to update.

It also creates the database content on startup. Existing content will not be erased.

.NET

Currently, two databases are supported:

SQL Server Compact Edition (which is default)
SQL Server

to switch to SQL Server you have to modify dotNetPaymentBackend/web.config.

at the bottom of the file you will find the element "connectionStrings". There remove the currently active connection-strings for "PaymentEntities" and "SQLConnection" and uncomment the other two, which are prepared for SQL Server.
Then fill out the place-holders with the corresponding values:

**source** --> server
**database** --> datebase-name
**user** --> db-user
**password** --> db-password

Make sure to modify both the SQLConnection and PaymentEntities.

Currently Schema has to be set to "dbo" which is the default value of SQL Server.

To create the tables on SQL Server with the correct constraints the following Script can be used:

CREATE TABLE [CreditCard] ( [number] nvarchar(32) NOT NULL , [user] nvarchar(64) NOT NULL , [validThrough] datetime NOT NULL ); GO CREATE TABLE [Payment] ( [bookingId] nvarchar(64) NOT NULL , [ccNumber] nvarchar(32) NOT NULL , [amount] money NOT NULL , [date] datetime NOT NULL ); GO CREATE TABLE [Report] ( [tenant] nvarchar(255) NOT NULL , [date] datetime NOT NULL , [xmlData] ntext NULL ); GO ALTER TABLE [CreditCard] ADD CONSTRAINT [PK__CreditCard__0000000000000084] PRIMARY KEY ([number]); GO ALTER TABLE [Payment] ADD CONSTRAINT [PK_Payment] PRIMARY KEY ([bookingId]); GO ALTER TABLE [Report] ADD CONSTRAINT [PK__Report__000000000000006C] PRIMARY KEY ([tenant],[date]); GO CREATE UNIQUE INDEX [UQ__CreditCard__0000000000000089] ON [CreditCard] ([number] ASC); GO CREATE UNIQUE INDEX [UQ__Payment__000000000000002D] ON [Payment] ([bookingId] ASC); GO ALTER TABLE [Payment] ADD CONSTRAINT [FK_CREDITCARD_PAYMENT] FOREIGN KEY ([ccNumber]) REFERENCES [CreditCard]([number]) ON DELETE NO ACTION ON UPDATE NO ACTION;

Re: easyTravel - using other RDBMSes

ChadTurner — Mon, 09 Jan 2023 21:07:09 GMT

Thank you for the information @Karolina_Linda

topic Re: easyTravel - using other RDBMSes in Start with Dynatrace