Using an External Database with Verastream Process Designer

VPD R6 SP2

This section is for VPD Release 6 Service Pack 2. Complete the following steps to configure the VPD Process Server to use MySQL as the external database.

1. Install MySQL:
• For this example, obtain MySQL 8.0.15 community edition from http://www.mysql.com/downloads/mysql/#downloads.

• Run the installer and choose a developer standard configuration.

2. Create the tables that are required by Apache ODE:

• Download the ODE database schema from http://ode.apache.org/sql/ode-1.3.4-jpa-mysql55.sql. Save the schema as a text file.

• Run the mysql.exe commands below to create and initialize the ODE database:

C:\MyDownloads>c:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe -u root -p
Enter password: ****************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 8.0.15 MySQL Community Server - GPL

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database ode;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

C:\MyDownloads>"c:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe" -u root –p ode < ode_openjpa_mysql-1.3.2.sql
Enter password: ****************
C:\MyDownloads>

3. Important: Be sure that the Verastream Process Server service has been started at least once, but is stopped before you proceed.

4. Copy the MySQL JDBC connector from C:\Program Files (x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.15.jar and copy it to the ProcessServer\services\composite\lib directory. (The .jar will be automatically added to the Process Server classpath.)

5. In the ProcessServer\services\composite\runtime\WEB-INF\conf\ode-axis2.properties file, uncomment the last two lines to enable external MySQL support:

# Uncomment to enable external database support.
#ode-axis2.db.mode=EXTERNAL
#ode-axis2.db.ext.dataSource=java:comp/env/jdbc/ode-ds

6. In the ProcessServer\services\composite\runtime\WEB-INF\jetty-env.xml file, uncomment the "ode-ds" resource and set credentials to enable MySQL JDBC connections in Jetty:

<Configure class=" org.eclipse.jetty.webapp.WebAppContext">
 <!-- Uncomment to enable support for an external mysql database. -->
 <!--
 <New id="ode-ds" class=" org.eclipse.jetty.plus.jndi.Resource">\
<Arg>jdbc/ode-ds</Arg>
  <Arg>
   <New class=" com.mysql.cj.jdbc.MysqlConnectionPoolDataSource">
    <Set name="Url">jdbc:mysql://localhost:3306/ode?autoReconnect=true</Set>
    <Set name="User">root</Set>
    <Set name="Password"></Set>
   </New>
  </Arg>
 </New>
 -->
</Configure>

7.    In the ProcessServer\services\composite\runtime\WEB-INF\web.xml file, uncomment the "resource-ref" at the bottom to enable JDBC connections in Jetty:

<!-- UNCOMMENT TO USE APPSERVER DATASOURCE
    <resource-ref>
        <res-ref-name>jdbc/ode-ds</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>
UNCOMMENT TO USE APPSERVER DATASOURCE -->

8. Configuration is complete. Start the Verastream Process Server service.

Note: If you skip any of the above steps you will see errors in the console (or logs) when you try to start the server.