How to migrate from HSQLDB to another database

This guide explains how to migrate from HSQLDB to another database such as PostgreSQL, MySQL, Oracle, SQL Server or DB/2.

IMPORTANT: This guide assumes that openCRX 2.8 Server is successfully setup as described in openCRX 2.8.0 Server Installation Guide and openCRX 2.8 SDK is successfully setup as described in openCRX 2.8 SDK for Ant Step-by-Step.

Setup the database

In a first step you must create an empty openCRX database. This is described in these guides:

• MySQL 5
• PostgreSQL 8
• SQL Server 2005
• DB2/9
• Oracle 10

Setup a database connector

Next configure the database connector which allows Tomcat + OpenEJB to connect to your newly created database. For this purpose open the file {tomcat_installdir}/apache-tomcat-6/conf/openejb.xml and adapt the following section:

<Resource id="jdbc_opencrx_CRX" type="DataSource">
 JdbcDriver org.hsqldb.jdbcDriver
 JdbcUrl jdbc:hsqldb:hsql://127.0.0.1:9001/CRX_2_7
 UserName sa
 Password manager99
 JtaManaged true
</Resource>

according to your database settings.

PostgreSQL:

 JdbcDriver org.postgresql.Driver
 JdbcUrl jdbc:postgresql://localhost/CRX_2_7

MySQL:

 JdbcDriver com.mysql.jdbc.Driver
 JdbcUrl jdbc:mysql://localhost:3306/CRX_2_7

DB/2:

 JdbcDriver com.ibm.db2.jcc.DB2Driver
 JdbcUrl jdbc:db2://localhost:50000/CRX_2_7

Oracle:

 JdbcDriver oracle.jdbc.driver.OracleDriver
 JdbcUrl jdbc:oracle:thin:@localhost:1521:XE
 UserName CRX_2_7

SQL Server:

 JdbcDriver com.microsoft.sqlserver.jdbc.SQLServerDriver
 JdbcUrl jdbc:sqlserver://localhost:1433;databaseName=CRX_2_7;selectMethod=cursor

Also adapt correspondingly the openCRX launch script {tomcat_installdir}/bin/webapp01.sh and {tomcat_installdir}/bin/webapp01.bat. If required, disable the START and STOP commands for the HSQLDB database.

Copy the database

The tool CopyDb allows to copy an openCRX database from a target to a source database. You can do it as follows:

• Open a shell and cd to the openCRX SDK home directory (= {opencrxsdk_home}).
• Run setenv.bat on Windows platforms or setenv.sh on Unix.
• cd to opencrx-2.8.0/core.
• Now run ant CopyDb. This prints a README which explains the parameters and options of CopyDb.
• To make your life easier the directory {opencrxsdk_home}/opencrx-2.8.0/core/etc/scripts contains some examples which show the usage of CopyDb. E.g. if you want to copy the HSQLDB to a PostgreSQL database use the script CopyDb-HSQLDB-to-PG.txt as template.
• Adapt the JDBC driver locations, connection URLs, user names and passwords to your database settings and run CopyDb.
• CopyDb shows all processed tables. CopyDb must complete without errors. Fix any errors before proceeding.

Start Tomcat + OpenEJB

Now you are ready to start Tomcat + OpenEJB. openCRX now connects to the newly created and populated database.