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.
In the EasyTravel installation (dist) folder, search for this file:
These properties control the Database:
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:
must point to some free port. EasyTravel must start a derby instance, even when it is using external RDBMS
Oracle versions 12 is only supported in easyTravel builds
easyTravelBusiness is an Oracle SID
$user_pass should be filled with real values
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:
also files launching jvm like weblauncher/weblauncher.sh should look like below:
Example code for the DB user creation:
The easyTravel user must be granted the EXECUTE permission on the DBMS_LOCK package (e.g with grant execute on dbms_lock to easytraveluser). DBMS_LOCK grant to a role is not sufficient.
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
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
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
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.
How to configure MySQL
Create a database and allow access from other machines:
Adjust config at /etc/mysql/my.cnf, in section [mysqld]:
character-set-server = utf8
collation-server = utf8_bin
- comment out
bind-address = 127.0.0.1
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.
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: