Running SQL on External Databases

It is possible to run SQL queries on external databases, but there are some existing limitations and security concerns on the use of this feature in production environments or on business sensitive databases.

Two ways are available to run SQL queries on an arbitrary DB:

  • If itis acceptable to store the DB user name and password in the Excel template, you can use ${externaldb.getJdbcConnection (jdbcUrl, username, password).execQuery(…))}.

    The user name and password are optional parameters. The drawback of this approach is that everyone with access to the Excel template will know of the user name and password, which is an obvious issue (they won’t be available in the report though). Moreover, if the database information changes (JDBC URL, user name, password), you need to edit your templates.

    This solution makes you target non-Oracle databases. However if that is the case you must make sure that the JDBC driver used to connect to the external database is on PPM classpath (for example, in <SERVER_HOME>/deploy/itg.war/WEB-INF/lib ).

  • You can also create a JNDI datasource in PPM Server.

    Make a copy of itg-ds.xml located in <SERVER_HOME>/deploy and edit the contents to match your database. The new file name must end with -ds.xml and it must be located in the <SERVER_HOME>/deploy/ directory. Make sure you remove the <security-domain> element. You must also add elements <user-name> and <password> in this JNDI datasource definition. When this is done, run kUpdateHtml.sh and restart PPM. There are currently two limitations with this approach:

    • PPM does not support yet encryption of user name and password in custom JNDI datasource.
    • As of PPM 9.50, you can only create JNDI Datasources that target Oracle Databases.

      When the JNDI datasource is added, you can query it from the Excel template using ${externaldb.get(JNDI_name).execQuery(…))}.

Note: Connecting to external database should not be used in production unless the security implications have been fully understood. In the meantime, you can use the workaround of creating DB Links to the external databases in the PPM database or Reporting database and connect to either of these databases.