Changing Repository Database
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.
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),
Run the following scripts where x is the database type (e.g. 01-create_DB_oracle.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.
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.
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.
2. Stop the studio. Also stop the server if using an external server.
3. Open the following file depending on your server type:
4. Comment
the existing
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.
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:
No error messages or information messages are shown when the server is started. Cannot connect using the studio.
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.