Examples for linking different database systems
| Table of contents |
The differences in the connection of the different DBMS are shown here in detail by way of examples. If a DBMS is to be prepared before a FirstSpirit installation and uncertainties exist regarding the database administrator's access parameters or the necessary driver files, in most cases it helps to test the database link by means of an external JDBC client. This can be done, e.g. using the DB Visualizer from https://www.dbvis.com.
The following configuration examples show use as a default layer. If used as a DBA layer, the jdbc.SCHEMA=… line is omitted.
Configuration example MySQL
Driver: mysql-connector-java-x.x-bin.jar
jdbc.DRIVER=com.mysql.jdbc.Driver
jdbc.URL=jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=UTF8
jdbc.USER=test
jdbc.PASSWORD=test
jdbc.layerclass=de.espirit.or.impl.mysql.MySQLLayer
jdbc.SCHEMA=dbname
It is only necessary to specify ?useUnicode=true&characterEncoding=UTF8 for jdbc.URL if using a UTF8-coded database (MySQL 5 and higher). If using the default MySQL coding (latin1, ISO-8859-1), it is not necessary to specify this parameter.
Configuration example MS-SQL-Server
Driver: sqljdbc-x.x.jar
jdbc.DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.PASSWORD=test
jdbc.URL=jdbc:sqlserver://myServer3306;DatabaseName=testDB
jdbc.USER=test
jdbc.layerclass=de.espirit.or.impl.mssql.MSSQL2005Layer
module=JDBC_MS_SQL_2016
Configuration example Oracle (< 19c)
Driver: ojdbcx_x.x.jar
jdbc.DRIVER=oracle.jdbc.OracleDriver
jdbc.URL=jdbc:oracle:thin:@myserver:1521:ORCL
jdbc.USER=test
jdbc.PASSWORD=test
jdbc.layerclass=de.espirit.or.impl.oracle.OracleLayer
jdbc.SCHEMA=test
jdbc.oracle.TABLESPACE=USERS
jdbc.property.oracle.jdbc.RetainV9LongBindBehavior=true
For URL the instance name of the oracle server is specified as last parameter (in the example ORCL) and not the schema name. The schema name given with jdbc.SCHEMA complies with the user name specified by jdbc.USER.
Configuration example Oracle 19c
Driver: ojdbc8.jar
jdbc.URL=jdbc:oracle:thin:@tcp://localhost:1521/ora
jdbc.USER=dbuser
jdbc.PASSWORD=verysecret
jdbc.oracle.TABLESPACE=USERS
jdbc.DRIVER=oracle.jdbc.OracleDriver
jdbc.layerclass=de.espirit.or.impl.oracle.Oracle19cLayer
jdbc.property.oracle.jdbc.J2EE13Compliant=true
jdbc.oracle.CHARACTERSET=AL32UTF8
jdbc.MAXSTRINGLENGTH=8191
module=JDBC_Oracle_Database_19c
For URL the instance name of the oracle server is specified as last parameter (in the example ORCL) and not the schema name.
 |
A subsequent change of the parameter values "jdbc.MAXSTRINGLENGTH", "jdbc.oracle.CHARACTERSET" and "jdbc.property.oracle.jdbc.J2EE13Compliant" is not possible! If you change your FirstSpirit database schema, new columns would then potentially be created and content already edited would no longer be accessible. |
Configuration example PostgreSQL
Driver: postgresql-x.x.jdbc41.jar
jdbc.DRIVER=org.postgresql.Driver
jdbc.URL=jdbc:postgresql://myServer:5432/testDB
jdbc.USER=test
jdbc.PASSWORD=test
jdbc.layerclass=de.espirit.or.impl.postgres.PostgreSQLLayer
jdbc.SCHEMA=public
module=JDBC_PostgreSQL
If using as a default layer, the value public can be entered for the parameter jdbc.SCHEMA in PostgreSQL (default value) and not the database name.
Configuration example DB2
Driver: db2jcc4.jar
Layer parameter:
For configuring an IBM DB2 data base a JDBC type 4 driver must be used:
jdbc.DRIVER=com.ibm.db2.jcc.DB2Driver
jdbc.layerclass=de.espirit.or.impl.db2.DB2Layer
jdbc.URL=jdbc:db2://myServer:50000/test
jdbc.USER=test
jdbc.PASSWORD=test
jdbc.SCHEMA=test
Port: DB2 Java connector (is provided via: db2jstrt PORTNUMBER).
If using as a default layer, the same value is entered as parameter jdbc.SCHEMA in DB2 as for jdbc.USER, if the default schema of the given database (here “test)” is to be used. Optionally, another schema can be used. In both cases the schema must be created beforehand outside FirstSpirit using the SQL command “create schema test;”.
Drivers:
- since DB 9.5: db2jcc4.jar, db2jcc_license_cu.jar
- before DB 9.5: db2jcc.jar, db2jcc_license_cu.jar
The JAR files are located on the DB2 server in the directory db2inst1/sqllib/java. It is recommended to use always the driver version which is supplied with the DB" server to prevent incompatibilities.
Port: DB2 connector (db2jstrt is not necessary for type 4). Under Unix, the port number can be read out of the /etc/services file, db2_db2inst entry and as a default is set to 50000.
Configuration example: MariaDB
Driver: mysql-connector-java-x.x-bin.jar
The MariaDB JDBC driver must not be used – the MySQL driver must be used instead!
jdbc.DRIVER=com.mysql.jdbc.Driver
jdbc.PASSWORD=<password>
jdbc.URL=jdbc:mysql://localhost:3306/<user>
jdbc.USER=<user>
jdbc.layerclass=de.espirit.or.impl.mysql.MySQLLayer
module=JDBC_MariaDB
Configuration example Internal Apache Derby database
The FirstSpirit server already contains a simple relational database system (Apache Derby) for test systems. FirstSpirit normally stores all data in the file system (Berkeley database) and – depending on the project requirements – only stores a few in relational databases. When a new project is created, this internal database can be activated for the project (see Project, “Internal database (Derby)” option) and write access to the database set for this project (see Databases (Project properties)). A Derby database can be subsequently created in the server properties (see Databases (Server properties)) and added to the project properties of the project (see Databases).
In order to use the Derby database from external processes, e.g. web application with FirstSpirit module DynamicDatabaseAccess in the external application server, the JDBC connector must first be activated for network connections (see Area: internal Database). The parameters from the database settings of the respective project are copied first as the connection parameters in the web application, e.g.
jdbc.URL=jdbc:derby:projects/project_12345/derby;create=true
jdbc.DRIVER=org.apache.derby.jdbc.EmbeddedDriver
jdbc.USER=testuser
jdbc.PASSWORD=testpassword
jdbc.POOLMAX=1
jdbc.POOLMIN=1
jdbc.layerclass=de.espirit.or.impl.derby.DerbyLayer
Then, in the connection parameters for the web application, lines jdbc.URL and jdbc.DRIVER are replaced with the following, where the host name of the FirstSpirit server is entered instead of “fs5server” and the Project ID “12345” is replaced by the actual ID:
jdbc.URL=jdbc:derby://fs5server:1527/projects/project_12345/derby
jdbc.DRIVER=org.apache.derby.jdbc.ClientDriver
The JDBC driver for integration in the web application can be downloaded as a file, derbyclient.jar from http://db.apache.org/derby/, in order to then copy it to WEB-INF/lib or into a global classpath directory of the application server. The respective active version of the Derby database in FirstSpirit can be read from the log file firstspirit5/log/fs-database.log.
In order to automatically create the JDBC configuration for the individual web applications, this modified database configuration can also be made directly in the layer settings of the FirstSpirit server. Then derbyclient.jar must also be copied to firstspirit5/shared/lib-isolated.
If precisely 1 schema is to be accessed in the external application, if necessary, extend jdbc.URL to include the DATABASENAME parameter. For {SCHEMA-ID} and {PROJECT-ID}, refer to the relevant FirstSpirit project for each.
jdbc.URL=jdbc:derby://fs5server:1527/projects/project_12345/derby;
DATABASENAME=P{SCHEMA-ID}_{PROJECT-ID}
It is recommended that JDBC drivers be integrated as a FirstSpirit module instead of in firstspirit5/shared/lib-isolated and manually in WEB-INF/lib, so that they are automatically integrated in all FirstSpirit web applications. Then, the parameter module=JDBC module name must additionally be given. See Creating a JDBC driver module.