The UDC will no longer be supported in the future. Please use our REST API instead.

This article describes how the universal database connector for Meisterplan works, how to integrate it under Manage > Data Sources in Meisterplan, how to download and set it up using the associated xml configuration file and how to run it.

• General
• Prerequisites
• Adding a Universal Database Connector Data Source in Meisterplan
• Configuring the Connector
• Configuring the Connection Details
• Writing Custom SQL Queries
• Adding a JDBC Driver
• Running the Connector
• Other Command Line Arguments
• Proxy Settings
• Adding SSL Certificates in Java

General

With the Meisterplan universal database connector, you can import your project and resource data from any JDBC compatible database into Meisterplan.

The connector comes as a standalone Java program in the .jar-Format. To run the program, you must first take the preliminary steps explained in the following sections.

Prerequisites

The universal database connector comes as a Java program and therefore requires the Java Runtime. See the Client System Requirements article for the currently required version.

Adding a Universal Database Connector Data Source in Meisterplan

To import the data read from the JDBC data source to Meisterplan, you need to add a universal database connector data source in Meisterplan:

• In the left hand side sidebar, under Manage, open the Data Sources view.
• Click the Add Data Source button to open the selection of available data sources.
• Select Universal Database Connector to add a universal database connector data source.
• Enter the following settings and download the current version of the Java executable for the universal database connector via the download link displayed here.

Name: the default name for universal database connectors is "Universal Database Connector". Rename the connector to indicate the data source you wish to connect.

Import-Code and URL: your import code for the webservice, which the universal database connector uses to import data into Meisterplan, and the URL to access the webservice. You will need to enter the import code and URL for the webservice into the configuration XML file used by the universal database connector, as is described in the Configuring the Connection Details section below.

Download: download link to the universal database connector Java executable.

Configuring the Connector

The universal database connector is configured via an XML file that stores connection details and custom queries. To generate a template XML configuration file, you can execute the following command in the directory where the connector executable file is located:

java -jar mp-jdbcconnector-[version].jar --generate-default-config config.xml

This will generate a config.xml file. Enter the following information in this file in order to import the data into Meisterplan:

• The credentials for querying the Meisterplan webservice 
• Your custom SQL queries for collecting the data
• The JDBC driver class
• The location of your JDBC compatible database

Configuring the Connection Details

The <connector>-Tag of the configuration file keeps information about the Meisterplan Webservice.

The url and the importCode properties can be obtained by opening the Universal Database Connector properties under Manage > Data Sources in Meisterplan.

Please note that you may also pass the password as a command line parameter. This will overwrite the password stored in the XML file, such that you don't have to store the actual password in the XML file. See the Other Command Line Arguments section below for information about displaying supported command line arguments.

<connector>
    <url>https://eu.meisterplan.com/ws/dataimport</url>
    <username>{meisterplan-user}</username>
    <password>{meisterplan-password}</password>
    <importCode>0x810101ef1b11e197a78a59a6f8e9bcui0s</importCode>
    <dbName>{meisterplan-database-name}</dbName>
</connector>

Writing Custom SQL Queries

In order to bring your data into the format that Meisterplan expects, you need to write custom SQL queries and enter them in the XML configuration. The expected column names can be seen in the template configuration, that can be generated as described above. Here is a small example query that shows the selection of project data using the SQL AS keyword:

<projects>
    SELECT proj.mycode AS code, proj.mytitle AS title, proj.start, proj.finish,
    proj.managerid, proj.status, proj.costtype, proj.priority, prog.code AS
    programcode, goal.code AS businessgoalcode, proj.mynotescolumn AS notes
    FROM project proj, programs prog, businessgoals goal
    WHERE proj.mycode = prog.mycode AND proj.mycode = goal.mycode
</projects>

Adding a JDBC driver

In order to query your JDBC database, a JDBC driver must be specified in the configuration file. You can download the required driver on the homepage of your database manufacturer. 

In the configuration XML, you need to specify the fully qualified Java class name of the driver and the JDBC connection String. The code-block below shows an example how the Configuration XML tags look like for a PostgreSQL database:

<driverClass>org.postgresql.Driver</driverClass>
<url>jdbc:postgresql://my-postgresql-host/mydatabase</url>
<username>{database-user}</username>
<password>{database-password}</password>

Please note that you may also pass the password as a command line parameter. This will overwrite the password stored in the XML file, such that you don't have to store the actual password in the XML file. See Other Command Line Arguments below for information about displaying supported command line arguments.

For the most popular database systems, the following table provides an overview about the name of the driver class and the download location:

Running the Connector

To execute the connector, the JDBC driver must be available in the classpath of the program. To achieve this, the driver's .jar-file must be passed with the command line arguments.

Here is an example how to run the connector using the PostgreSQL JDBC driver .jar file and the config.xml configuration file:

java -cp "postgresql-9.4.1208.jar;mp-jdbcconnector-[version].jar"
de.itdesign.meisterplan.connector.impl.jdbc.EntryPoint -c config.xml

Other Command Line Arguments

A list of all available command line arguments can be obtained by executing

java -jar mp-jdbcconnector-[version].jar --help

Proxy Settings

If you connect to Meisterplan via a proxy server and Java does not automatically use the browser or the operating system settings, you can start the UDC with proxy arguments. Simply specify the proxy host and the proxy port with these JVM parameters:

• -Dhttps.proxyHost
• -Dhttps.proxyPort

Here is an example call

java -Dhttps.proxyHost=[myproxy-hostname] -Dhttps.proxyPort=[myproxy-port] 
-cp "[my-jdbcdriver.jar];mp-jdbcconnector-[version].jar" 
de.itdesign.meisterplan.connector.impl.jdbc.EntryPoint -c config.xml

Adding SSL Certificates in Java

If Java cannot process the SSL certificates due to the above-described settings, you will receive the following error message upon executing the UDC:

sun.security.validator.ValidatorException: PKIX path building failed: 
sun.security.provider.certpath.SunCertPathBuilderException: 
unable to find valid certification path to requested target.

To resolve this issue, add the SSL certificate to the Java keystore by executing these two commands one after the other (the keytool command can be found in the bin directory of your Java installation directory):

keytool -J-Dhttps.proxyHost=[myproxy-hostname] 
-J-Dhttps.proxyPort=[myproxy-port] -printcert -rfc 
-sslserver api-soap.[eu/us].meisterplan.com:443 > mycertfile.pem

keytool -importcert -file mycertfile.pem 
-keystore %JAVA_HOME%/jre/lib/security/cacerts -alias "meisterplan"

You will be prompted to enter the password for the Java keystore. The default password for the keystore is “changeit“.