Changing Repository Database

Documentation home 

 

Introduction

 

Certain elements used by the Verj.io server system are stored in the Repository Database, and the systemis supplied with a Repository Database using embedded Apache Derby. This document contains instructions for switching the Repository Database to another database provider. There are three supported databases in addition to Apache Derby: Mysql, Oracle and MS Sql Server. Other databases can also be used but database schema scripts are not supplied; these would need to be created using standard database tools.

 

The process consists of the following steps:

 

·         Create the new database with minimum content

·         Configure the server to use the new database

 

Please follow the detailed instructions below.

 

Note: if you have installed Verj.io to the “Program Files” folder on a Windows system, you may experience problems editing some of the configuration files mentioned below as the files are read only. If you have this problem, you will need to launch the editing tools e.g. Notepad as administrator e.g. right click on the application in the start menu and select Run as administrator.

 

Create the new database

 

1.      The database schema script files are located in directory databaseSchemas of the standard Verj.io distribution. These script files have been tested using MySQL-Front (Mysql),  SqlPlus (Oracle), Enterprise manager (MS Sql Server) and may need slight adjustments when used with other tools.

Run the following scripts where x is the database type (e.g. 01-create_DB_oracle.sql).

 

  • 01-create_DB_x.sql

Creates 2 databases/schemas – ufs and ebase_samples

Note: The following scripts create and populate tables in these 2 databases/schemas. You may change the database/schema names to suit your own naming conventions.

  • 02-repository_tables_x.sql

Connect to database/schema ufs before running the script.

Creates repository tables in ufs and populates tables with minimum data to allow the studio and server to start and user admin to login to the Server Admin App.

  • 03-sample_forms_tables_x.sql  (optional – used by tutorials)

Connect to database/schema ebase_samples before running the script.

Creates and populates tables in ebase_samples used by tutorials.

 

For MS Sql Server, ignore any warning messages about maximum row sizes.

Configure the server to use the new database

 

2.      Stop the studio. Also stop the server if using an external server.

 

3.      Open the following file depending on your server type:

  • For an embedded server, open file ebasetest.xml in folder Verjio/Studio/embeddedServer/tomcat/conf in the user’s home directory.
  • For an external server, open file <xxx>.xml in folder Userdata/VerjioServer/tomcat/conf/Catalina/localhost where <xxx> is the web application name (ebase by default)

 

4.      Comment the existing Derby resource definitions at the top of this file for jdbc/UFSREPOSITORY and jdbc/EBASE_SAMPLES (Comments begin with <!-- and end with -->).

 

5.      This file contains commented examples of resource definitions for all supported databases. Remove the comments lower down in the file for resources jdbc/UFSREPOSITORY and jdbc/EBASE_SAMPLES for your database system (Mysql, Oracle or MS Sql Server).

 

6.      Change the url, username and password parameters for both of these resources to point to the database system where you have created the new databases.

 

7.      Save the file.

 

8.      If you are switching to either Oracle or Mysql and this is the first time you have connected to one of these databases, you must also install the appropriate JDBC driver file. These can be downloaded using these links:

 

 

Copy the JDBC driver file to the following folder depending on your server type:

 

·         For an embedded server, to folder Verjio/Studio/embeddedServer/tomcat/lib

·         For an external server, to folder Userdata/VerjioServer/tomcat/lib

 

9.      Restart the studio/server.

  • For an embedded server, restart the studio
  • For an external server, restart the server

 

10.  Start the Server Admin App by clicking the server icon  on the Shared Toolbar. Login as an administrator user, e.g. admin/xxx in a new V5 installation or ebaseuser/xxx in an system upgraded from V4.

 

11.  Click Database Connections and change the connection type of both connections to the required database type. Test and save both connections.

 

 

Possible errors restarting the server

Editing the ebasetest.xml or xxx.xml (as instructed in steps 4,5 and 6 above) can often cause problems starting the embedded server or the external server. This includes..

 

Error messages indicating that the system cannot connect to the repository database are issued when the server is started. Frequently, the error messages are not specific enough to identify the reason for the failure.

Possible causes include:

  • Syntax error in the .xml file. Check that the any comment blocks are correctly closed <!--  -->.
  • url parameter is incorrect – database system is not running on the target host, the port is incorrect etc
  • username or password parameter is incorrect
  • JDBC driver file is not installed

 

No error messages or information messages are shown when the server is started. Cannot connect using the studio.

  • There is probably a syntax error in the ebasetest.xml or xxx.xml file. Check that the any comment blocks are correctly closed <!--  -->.

 

If you cannot resolve these problems, revert to using the supplied Apache Derby embedded database. Then start the Server Admin App, click Database Connections and use the Database Connection Wizard to check if you can connect to the new Databases.