Executing JNDI based Pentaho reports using the Java API

Executing JNDI based Pentaho reports using the Java API.


Working on the Alfresco Business Reporting project. In there, the tool can execute PentahoJaspersoft reports (against Alfresco business objects, using the Pentaho/Jasersoft Java API) on a scheduled basis. I ran into the issue of portability of the (Pentaho) reports. The database, url, credentials were not identical on my Development, Test and Production environments… JNDI is the answer.

In JNDI ( (Java Naming and Directory Interface) world, a datasource is identified by a name, and each naming provider can carry different credentials/url’s for the same name in different environments. Datasources can be LDAP systems, JDBC connections etc. Just what I needed.
I ran into 3 issues, initially entangled;
  1. How does the Pentaho reporting API deal with JNDI
  2. Running a report using my custom Java logic from a command-line
  3. Running a report from within Alfresco – How to manage the duplicate credentials. The reporting database connection properties were already defined in alfresco-global.properties, and I prefer to re-use these in the named JNDI connection.
 Although I like journeys, I do prefer reaching destination too, especially in this particular case. There were not many useful guides along the route, so having it finally figured out, also resulting in this blog.
How does the Pentaho reporting API deal with JNDI

The Pentaho report definition (.prpt files) are zip files containing mostly XML files. One of the files contains all database related info. This means the JNDI name and the JDBC userame/password/driver/url information is stored inside the report. It appears hard to override when executing the report using the Java API.
The Pentaho API has a class MasterReport, that has the methods setDataFactory and setDataSource. The DataFactory is typically used for JDBC like connections (user, pass, url, driver).  I have had no success in overriding these values when executing a report in a different environment.
The JNDI name is stored in the report too, but since it is resolved against the context at time of execution, it is perfectly fine to use the name as stored. Exactly how JNDI was meant. Find here how to configure the Pentaho Report Designer for JNDI.
Running a report using my custom Java logic from a command-line
I created a command-line version of the logic to execute reports. Building stuff into Alfresco, restarting and the full procedure takes too much time. But how to use JNDI like this?
Most guides expect some kind of JNDI provider (usually application servers like JBoss, Tomcat), and tell you how to get definitions from there. I do not want to depend om yet another definition of my reporting database details, it is in Alfresco’s global properties already. I got stuck in Simple-JNDI and related tools, that apparently get datasources and details from, yet again, an existing JNDI provider. The Report executer does not accept anything that Simple-JNDI provides (DataSource or Connection). (Wonder how Pentaho Report Designer does the trick itself… I have had my share of reverse engineering for now…) The Pentaho report just requests the JNDI name, and gets provided by the environment. How to get such a responsive environment?
I finally ran into this guide from Oracle. It inspired me to finalize their idea (stopRegistry was missing):
01 private static Registry startRegistry() throws RemoteException {
02     System. out .println("enter startRegistry" );
03     Registry registry = null ;
04     try {
05         registry = LocateRegistry.createRegistry(Registry. REGISTRY_PORT );
06         System. out .println("exit startRegistry: RMI registry created.");
07     } catch (ExportException e){
08         System. out .println("exit startRegistry: RMI registry already existed.");
09     }
10     return registry;
11 }
13 private static boolean stopRegistry(Registry registry) {
14     System. out .println("enter stopRegistry" );
15     boolean result = false;
16     try {
17         result = UnicastRemoteObject.unexportObject(registry, true );
18     } catch (NoSuchObjectException e) {
19         System. out .println("stopRegistry: RMI registry already stopped.");
20     }
21     System. out .println("exit stopRegistry: RMI registry stopped: " + result);
22     return result;
23 }
25 private static InitialContext createContext() throws NamingException {
26     Properties env = new Properties();
27     env.put(Context. INITIAL_CONTEXT_FACTORY ,"com.sun.jndi.rmi.registry.RegistryContextFactory" );
28     env.put(Context. PROVIDER_URL , "rmi://localhost:" +Registry. REGISTRY_PORT);
29     InitialContext context = new InitialContext(env);
30     return context;
31 }
33 private static void setJndiDataSource(String source) throws NamingException, RemoteException{
34     System. out .println("enter setJndiDataSource source="+source);
36     ConnectionPoolDataSource dataSource = newMysqlConnectionPoolDataSource();
37     ((MysqlDataSource) dataSource).setUser( "username" );
38     ((MysqlDataSource) dataSource).setPassword("password" );
39     ((MysqlDataSource) dataSource).setServerName("localhost" );
40     ((MysqlDataSource) dataSource).setPort(3306);
41     ((MysqlDataSource) dataSource).setDatabaseName("alfrescoreporting" );
42     DataSource ds = (javax.sql.DataSource)dataSource;
43     InitialContext context = createContext();
44     context.rebind( "java:/comp/env/jdbc/" +source, ds);
45     System. out .println("exit setJndiDataSource" );
46 }
Remind that the unexportObject() method only works with the exact object as returned by createRegistry() call … I invoked startRegistry() and setJndiDataSource() before and stopRegistry() after Pentaho’s  reportProcessor.processReport() and got exactly what I needed!  (I suggest you call the stopRegistry() in a finally part of the try/catch block around the processReport()).
Now I was able to build my own JNDI provider that actually allowed me to execute a Pentaho JNDI-based report from the command-line (e.g. without any application server).
Running a report from within Alfresco
It would be too easy if my command-line code would work inside an application server too. Th application Server (Tomcat in my case) owns the Registry. You can access the registry read-only, but not rebind and therefore ‘dynamically’ adding the appropriate details for any given JNDI name. This actually makes sense of course.
Basically, define a JNDI source within Tomcat (or application server of choice). I followed Tomcat’s example, and added
1 <Resource defaultTransactionIsolation="-1" defaultAutoCommit="false"
2     maxActive="15" maxIdle="3" initialSize="1"
3     username="username" password="password"
5     driverClassName="org.gjt.mm.mysql.Driver"
6     type="javax.sql.DataSource" auth="Container"
7     name="jdbc/alfrescoReporting"/>
to my META-INF/context.xml. My web.xml was enahnced with:
1 <resource-ref>
2     <description>Alfresco Reporting JNDI definitition</description>
3     <res-ref-name>jdbc/alfrescoReporting</res-ref-name>
4     <res-type>javax.sql.DataSource</res-type>
5     <res-auth>Container</res-auth>
6 </resource-ref>
You also need to make sure your database driver (mysql-connector-java-5.x.yy.jar, or any Postgres, Oracle, MSSql equivalent) is located in tomcat/lib. (Usually it is already there.)
According the guys from Tomcat, you’re done. However, it doesn’t work. Apparently you also need to tweak <applicationname>.xml (in my case: alfresco.xml) in tomcat/conf/Catalina/localhost. You need to insert exactly the same line(s) as added to context.xml above.
And now we’re done. Pentaho reports are executed using the Java API in a web application like Alfresco using JNDI!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s