As discussed in Section 1.3.2.1 - The ERES Database, EspressReport ES uses a database as a persistent storage mechanism for users, groups, permissions, data sources, and published reports and charts. By default, EspressReport ES uses an HSQL database that comes with the installation. However, since this database is generally inappropriate for production environments, users can easily configure ERES to use a different one.
The first step in configuring ERES to run with another database is to setup the tables and fields that are used to store application information. Several scripts for different databases are available in the <ERESInstallDir>/data directory. The scripts contain the create table statements for the database.
In addition to database specific scripts, a generic script is also provided that can be modified to run with other databases. The quadbasedb_jdbc.sql file contains default scripts that are used to create the HSQL database.
For each of the supported databases, there can be a couple additional configuration steps that needs to be taken to correctly setup the tables.
The script to create the tables for DB2 is named quadbasedb_db2.sql. For DB2, you will need to first create a TABLESPACE for the new tables. To do so, use the following steps from the command line:
connect to your database using
db2adminusing this command:db2 connect to <DBNAME> user <DB2USER> using <Password>Be sure to substitute your database username and password when making the connection.
Create buffer pool with the command below:
db2 CREATE BUFFERPOOL BP32k SIZE 100 PAGESIZE 32kCreate tablespace with the command below:
db2 "CREATE REGULAR TABLESPACE DATASPACE1 PAGESIZE 32 K MANAGED BY DATABASE USING ( FILE 'c:\db2\db2data\dataspace1_01.dbf' 1000 ) EXTENTSIZE 4 OVERHEAD 10.5 PREFETCHSIZE 4 TRANSFERRATE 0.33 BUFFERPOOL BP32k"
The script to create the tables for Microsoft SQL Server is named quadbasedb_sqlserver.sql. There is no additional setup needed to run the script for this database. However, the database connection cannot be established to SQL Server with the default settings. The requirements may vary with different SQL Server versions.
MS SQL Server 2005
Download the latest MS SQL Server 2005 JDBC Driver from: msdn.microsoft.com
Unpack the driver and copy the sqljdbc.jar file into the <ERES>/WEB-INF/lib directory.
Recommended Admin Console entries (see Section 9.1.2 - Specifying the Database Connection):
Database URL: jdbc:sqlserver://hostname:port;databaseName=<databaseName>;
Database Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
ERES will connect to the SQL server instance using TCP/IP protocol, so you will have to determine the TCP port number of your SQL Server instance. To do that, launch SQL Server Configuration Manager, expand the SQL Server 2005 Network Configuration node and click Protocols for <YourSQLServerInstanceName> (for example Protocols for MSSQLSERVER). Double click on the TCP/IP protocol. First of all, make sure that the TCP/IP protocol is enabled. Look at the Enabled option in the Protocol tab, it should be set to Yes, if it is not, please do so. Then look at the Listen All option, there are two possibilities:
Listen All is set to Yes (default) : Go to IP Addresses tab and scroll down to the IP All section. The port number you are looking for is below the IP All section in the TCP Dynamic Port option.
Listen All is set to No : Go to IP Addresses tab and find the section that has the IP Address option set to the IP address you will be connecting to. The port number can be found one row below the particular IP address option (TCP Dynamic Ports option).
ERES connects to the SQL database using SQL Server Authentification mode, so you have to have this function enabled in your MS SQL Server. You can do that in MS SQL Server Management Studio. Right click on the server name in Object Explorer and click Properties. Go to the Security page and select the SQL Server and Windows Authentification mode option. Also the user that you will use to connect to the SQL Server has to have the SQL Server authentification mode set. Be sure that the user has sufficient right to use the database.
MS SQL Server 2000
Download the latest tar version of MS SQL Server 2000 JDBC Driver from: download.microsoft.com
Unpack the mssqlserver.tar file. Go to the unpacked directory and unpack the msjdbc.tar file. Go to the msjdbcd/lib directory. You should see three jar files in the lib folder. Copy them into the <ERES>/WEB-INF/lib directory.
Recommended Admin Console entries (see Section 9.1.2 - Specifying the Database Connection):
Database URL: jdbc:microsoft:sqlserver://hostname:port;databaseName=<databaseName>;
Database Driver: com.microsoft.jdbc.sqlserver.SQLServerDriver
The script to create the tables for MySQL is named quadbasedb_mysql.sql. There is no additional setup needed to run the script for this database.
Please make sure that your MySQL database uses case-insensitive identifiers (see MySQL Identifier Case Sensitivity).
Once you have successfully created the tables in your database, you can configure ERES to use that database for storage. To setup the database connection, log in as Admin and enter the Admin Console. The database information can be entered under → .
Replace the URL, driver, username, and password with values of your database. ERES uses JDBC to connect to the database, so you will need to make sure to enter the correct URL and JDBC driver class for the connection.
Once you finish making the changes, save on Save Settings and restart the ERES Server. Note that you may need to copy the JDBC driver for your database (typically one or several Jar files) to the <ERESInstallDir>/WEB-INF/lib directory before restarting the ERES Server. For application servers other than Tomcat, please refer to the instructions in Section 9.3 - Using Other Application Servers.
You can also specify a connection to the ERES database using JNDI. To do so, you must first edit <ERESInstallDir>/WEB-INF/web.xml and uncomment the JNDI tag (remove the <!-- and --> tags). You can also change the JNDI reference name (in the <res-ref-name> tag) from jdbc/ERESdb to another name. Then login as Admin, enter Admin Console and pass in the JNDI details (such as JNDI context factory, JNDI provider URL and JNDI name) under → Repository. The values can be entered after the JNDI radio button is selected. Note that depending on the application server and the mapping used, the JNDI context factory and JNDI provider URL may not be necessary. For example, if ERES is installed on a Tomcat or WebSphere Application Server, connecting to the JNDI server only requires supplying the JNDI name. For Oracle 10g, you have the choice of either omitting the context factory and provider URL to use the default environment, or you can specify one of context factories they provide: oracle.j2ee.rmi.RMIInitialContextFactory, oracle.j2ee.naming.ApplicationClientInitialContextFactory, or oracle.j2ee.iiop.IIOPInitialContextFactory. Due to the wide range of application servers and JNDI implementations, please see the documentation for your JNDI server for configuration options and details. When the JNDI name is specified, the database information (URL, driver, username, password) is ignored.
With the connection information set, restart the server. The ERES server should start connected to the new database. Note that this new database will be blank, so any information about old users, privileges, or reports and charts will not be available. In addition, several key pieces of information will need to be manually setup in order to successfully deploy and run reports in ERES with the new database.
- Web Root:
You will need to enter the correct Web Root for your application server/servlet container. This information is supplied in the Admin Console. For more information, see Section 1.4.1.2 - Setting Info.
- Servlet Context:
You may need to re-set the servlet context information. For more on this, please see Section 1.4.1.2 - Setting Info.
- URL Mapping:
You will need to add a virtual directory in Organizer that maps the Web path to your ERES installation. For more information, see Section 2.1.5 - URL Mapping.
Sometimes it may be necessary to migrate the ERES database from one platform to another. For example, initial development on an implementation may be done using ERES in default configuration with the HSQL database. However, since HSQL is not recommended for production environments, users may need to migrate data to another database. To accomodate this, ERES provides a database migration utility for all supported databases.
![[Warning]](../../../images/warning.png) | Warning |
|---|---|
Since version 6.3, this tool can be used for database migration only. Database upgrade using this tool is no longer supported. Please use dbupgrade (DBUpgradeGUI or UpgradeAppl) application instead (Section 1.3.2.1.3 - Upgrading ERES Database from previous version of ERES). |
Before starting the database migration process, you will have to prepare the destination database, which must contain empty ERES 6.6 tables. To create the tables, run the appropriate SQL script from the <ERESInstallDir>/data directory. To learn more about the scripts, see the Section 9.1 - Using Other Databases chapter.
Please make sure that both the source and the destination databases belong to ERES version 6.6. If not, use the dbupgrade application to fix this problem.
The program for the utility is in <ERESInstallDir>/data/converter directory. It is a Java application that is run from the command line (the souce code is also provided). To run the converter, modify FromDB.properties file to use the JDBC connection information for your source database. Then modify ToDB.properties file to use the JDBC connection information for your destination database (sample connection files are provided for different databases in the directories under <ERESInstallDir>/data/converter).
At the command line, navigate to <ERESInstallDir>/data/converter directory and execute the following command:
java -classpath ".;driver1;driver2" CopyERESDB
For example:
java -classpath ".;hsqldb.jar;jdbc_mysql.jar" CopyERESDB
Within the -classpath argument, you will need to specify the archives/classes that contain the JDBC drivers for the source and destination databases.
Running this program will copy all data from the source (FROM) ERES database tables to the destination (TO) ERES database tables.
Once the conversion is complete, you need to modify the database connection information in the Admin Console as described in Section 9.1.2 - Specifying the Database Connection, before restarting the server.
Please note that ERES log records can not be transferred during the database migration.