Pentaho Scriptable Data Source and grabbing the underlying JNDI connection

This post is a little stray from the usual posts I've been putting up about LibreOffice.  This one deals with using Pentaho!  Now for those not in the know, Pentaho is a BI software suite.  At my job, one of my duties is Big Data and building reporting, analytic models, and dashboards for distribution of the collected data.

Well, I tend to play with the software in my off-time to get to know it and the source code.  Now one of the things coming from a Java programming background is having direct access to the underlying database.  Now if you setup your BI suite correctly, you should never need access to the database itself.  However, there was this one time where I wanted to create a temp table on the database and not in memory, I wanted direct access to the database.  After talking to myself for a while, I decided to not break my rule of trying to get direct access to the database through the report.  I changed my method and report was made...

However, after that moment, I was left with an itch to figure out, "How does someone go about grabbing a data source object from the BI server?"  For about a year or so, the urge to sit down and code an example came and went.  Most of the time I distracted myself with some other project, but always in the back of my mind was this thing that I hadn't done before and it was killing me.  So fast forward to about two weeks ago.  I finally, decided to bury the hatchet and code up an example of a report grabbing the data source from the JNDI tree.

So let's begin!!!

Today, I'm breaking out the "trusty" Windows system (because it's the one I randomly grabbed to start typing this post).  I'm going to assume the following, if none of this applies to you and you don't know how to get to these points, then this post isn't going to be very helpful for you.

1. You have Pentaho Report Designer (CE or EE edition)
2. You have a running instance of Pentaho BI Server that you can publish to!!
3. Your PRD install has a proper simple-jndi setup already.
4. Your BI server instance has the JNDI connections setup in the application context within Tomcat.
5. You already know a bit about the PRD scriptable data source.

That being said, go ahead and fire up Pentaho Report Designer.

Step one will be to start a new scriptable data source.

Starting a scriptable data source

A scriptable data source invokes the Rhino environment to execute your Java/Groovy/JavaScript code.  I'll be using plain Jane Java here (also know as Beanshell to the Rhino environment).

Go ahead and add a query and type the following in:

import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import org.pentaho.reporting.engine.classic.core.util.TypedTableModel;

InitialContext cxt = new InitialContext();
//DataSource ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/PROD_RM_JNDI");
DataSource ds = (DataSource) cxt.lookup("RM_SYS_1_JNDI");

Connection c = ds.getConnection();
Statement stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM ACC_CODES_PTE");

String [] columnNames = new String [] {"Column 1"};
Class [] columnTypes = new Class [] {String.class};

TypedTableModel model = new TypedTableModel(columnNames, columnTypes);

while(rs.next())
 model.addRow(new Object [] {rs.getString(1)});

c.close();
return model;

Now notice how I have one of those context lookups commented out. There is a reason for that.  Within the standard PRD environment, you are provided a FULL JNDI tree, you are actually using a little project called simple-jndi to provide pseudo-JNDI functionality.  When you switch over to the BI server, go ahead and comment out the current context lookup and use the lookup with the big huge path.  That's a proper JNDI path on the Tomcat server.

Once you've added an element to represent your data into the details section of the report, preview it.  You see that the system grabs a connection from the JNDI pool, uses it, and then tosses it back into the pool.  The model is then sent back to the report for processing and reporting.

It's pretty simple and this now allows you to create a temp table when the report runs on the DB.  Just remember that you need to take care to get rid of temp tables when you no longer need them, especially if you are using Tomcat's connection pooling.