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.5 Server is successfully setup as described in openCRX 2.5.1 Server Installation Guide and openCRX 2.5 SDK is successfully setup as described in openCRX 2.4/2.5 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:
Setup a database connector
Next configure the database connector which allows Tomcat+EJB 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_5 UserName sa Password manager99 JtaManaged true </Resource>
according to your database settings.
PostgreSQL:
JdbcDriver org.postgresql.Driver JdbcUrl jdbc:postgresql:''localhost/CRX_2_5
MySQL:
JdbcDriver com.mysql.jdbc.Driver JdbcUrl jdbc:mysql:''localhost:3306/CRX_2_5
DB/2:
JdbcDriver com.ibm.db2.jcc.DB2Driver JdbcUrl jdbc:db2:''localhost:50000/CRX_2_5
Oracle:
JdbcDriver oracle.jdbc.driver.OracleDriver JdbcUrl jdbc:oracle:thin:@localhost:1521:XE UserName CRX_2_5
SQL Server:
JdbcDriver com.microsoft.sqlserver.jdbc.SQLServerDriver JdbcUrl jdbc:sqlserver:''localhost:1433;databaseName=CRX_2_5;selectMethod=cursor
Also adapt correspondingly the openCRX launch script {tomcat_installdir}/bin/opencrx.sh and {tomcat_installdir}/bin/opencrx.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.5.1/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.5.1/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.
Known Bugs
Version 2.4.1
CopyDb does not work with DB/2 due to a configuration issue in openmdx-base.jar. This can be fixed as follows:
- Download and install the openMDX SDK 2.4.1. For more information about openMDX SDK see here]
- Download Sdk24.jdbc-driver-sql.properties and copy it to the directory openmdxSdk-2.4.1/openmdx-2.4.1/core/src/resource/org/openmdx/kernel/application/deploy
- Open a shell and cd to openmdxSdk-2.4.1/openmdx-2.4.1/core
- Run ant deliverables. This rebuilds openmdx-base.jar
- Copy openmdx-base.jar to opencrxSdk-2.4.1/opencrx-2.4.1/opt/openmdx-2.4.1/jre-1.5/core/lib. Also copy it to the Tomcat lib directory, e.g. openmdxTomcatEjb-2.4.1/apache-tomcat-6/lib
Version 2.5.1
CopyDb might throw errors when copying the table OOCKE1_CONTRACT. You can fix this problem as follows:
- Start the HSQLDB Console for openCRX 2.5.1
- Run the script below. It drops deprecated columns.
alter table oocke1_contract drop quote_; alter table oocke1_contract_ drop quote; alter table oocke1_contract drop column invoice_; alter table oocke1_contract_ drop column invoice; alter table oocke1_contract drop column opportunity_; alter table oocke1_contract_ drop column opportunity;
Start Tomcat+EJB
Now you are ready to start Tomcat+EJB. openCRX now connects to the newly created and populated database.
