Added by Steven Barkdull, last edited by Jem Matzan on Oct 28, 2008  (view change) show comment

Labels:

Enter labels to add to this page:
Wait Image 
Looking for a label? Just start typing.

Motivation

Occasionally when developing an action sequence to extend the Pentaho BI platform, the action sequence needs access to information in a relational database. The Relational action or SQL Command action can be added to the action sequence to get access to this information.

The Relational and SQL Command actions are configured to reference a specific database using the database's JNDI name. When running the Pentaho BI Platform in the JBoss Application server, there are several steps required to successfully configure a JNDI name for the datasource. In the example, I'll use values appropriate for a MySQL database, and I'll be using the Pentaho Preconfigured Install to verify that the datasource was configured properly.

Working with the Preconfigured Install
You will need to learn how to install and work with the Pentaho preconfigured install in order to test your datasource configuration.

Installing the Database Driver

The first step is to get a driver for your database and drop the driver file into /pentaho-preconfiguredinstall/server/default/lib. You can get the MySQL driver from the MySQL driver download page. Extract the driver (mysql-connector-java-5.0.4/mysql-connector-java-5.0.4-bin.jar) from the zip file and place it in the /pentaho-preconfiguredinstall/server/default/lib folder.

Configuring the JNDI Name

Configuration Property Names for the Example
For the example, I will choose myJNDIName for my JNDI name, myDatebaseName for my database name, username for the username, and password for the password.

After installing the driver, you'll need to configure the JBoss server with a datasource and a JNDI name to reference the datasource. This is done by creating a datasource definition file in /pentaho-preconfiguredinstall/server/default/deploy. The datasource definition file is an XML file whose name consists of an arbitrary name followed by "-ds.xml", for instance, my-datasource-name-ds.xml.

Below is an example of a datasource definition file. The datasource is referenced by the JNDI name myJNDIName, and is configured to connect to a database named myDatabaseName which is hosted on a MySQL RDBMS. The RDBMS is hosted on a machine called localhost, and the RDBMS is listening for connections on port 3306.

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <local-tx-datasource>
    <!-- JNDI name of the datasource, it is prefixed with java:/ -->
    <jndi-name>myJNDIName</jndi-name>
    <connection-url>jdbc:mysql://localhost:3306/myDatabaseName</connection-url>
    <driver-class>org.gjt.mm.mysql.Driver</driver-class>
    <user-name>username</user-name>
    <password>password</password>

    <!-- optional params follow -->

    <!-- sql to call when connection is created -->
    <new-connection-sql>select * from myTable</new-connection-sql>
    <!-- sql to call on an existing pooled connection when it is obtained from pool -->
    <check-valid-connection-sql>select * from myTable</check-valid-connection-sql>
    <!-- minimum connections in a pool. Pools are lazily constructed on first use -->
    <min-pool-size>5</min-pool-size>
    <!-- maximum connections in a pool. -->
    <max-pool-size>20</max-pool-size>
    <!-- Time to wait to check if connection is idle. Connection destroyed
         somewhere between 1x and 2x this timeout after last use -->
    <idle-timeout-minutes>0</idle-timeout-minutes>
    <!-- Whether to check all statements are closed when the connection is returned to the pool,
        this is a debugging feature that should be turned off in production -->
    <track-statements>true</track-statements>
  </local-tx-datasource>
</datasources>

 Bellow is the same BD now on a PostgreSQL server localhost, port 5432:

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
  <local-tx-datasource>
    <!-- JNDI name of the datasource, it is prefixed with java:/ -->
    <jndi-name>myJNDIName</jndi-name>
    <connection-url>jdbc:postgresql://localhost:5432/myDatabaseName</connection-url>
    <driver-class>org.postgresql.Driver</driver-class>
    <user-name>username</user-name>
    <password>password</password>
  </local-tx-datasource>
</datasources>

TODO: create sample datasource definition files for other RDBMSs.

Almost there! Now, modify the pentaho web application configuration files to include our connection info.

  1. Locate the web.xml file in <pci-home>\jboss\server\default\deploy\pentaho.war\WEB-INF.
  2. Inside the <webapp></webapp> tags, after the last </resource-ref> tag, add the following:
    NOTE that the <res-ref-name> value should match the JNDI name used in the report definition above, preceded by "jdbc/".
    <resource-ref>
        <description>mysql_db</description>
        <res-ref-name>jdbc/mysql_db</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
    
  3. Locate the jboss-web.xml file in <pci-home>\jboss\server\default\deploy\pentaho.war\WEB-INF.
  4. Inside the <jboss-web></jboss-web> tags, after the last </resource-ref> tag, add the following:
    NOTE that the <res-ref-name> value and the <jndi-name> value should include the JNDI name used in the report definition above.
    <resource-ref>
        <res-ref-name>jdbc/mysql_db</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <jndi-name>java:/mysql_db</jndi-name>
    </resource-ref>
    

Please note you have to use the proper configuration based on your database supplier. Example above has used  MySQL, but if you were using PostgreSQL for instance you would have to have it changed accordingly.

The above directions were missing from this article, but where present in many others, like in http://wiki.pentaho.com/display/COM/Designing+Reports+With+MySQL+and+JNDI, from where it was copied from.

That is it! You now have to restart the Pentaho preconfigured install.

Testing the Datasource Configuration

To test the datasource configuration, you'll need to have the Pentaho preconfigured install setup and running.

Creating the Action Sequence using the Design Studio
In order to create the Action Sequence to test your datasource configuration, you may like to use the Pentaho Design Studio. You can get the Design Studio from SourceForge: pentaho-design-studio_1.2.0.341-GA.zip. The Design Studio is implemented as an Eclipse plugin.
Prior to creating the Action Sequence, it may be useful to learn more about using the Design Studio and its Action Sequence Editor.

Related Information

Managing JBoss Datasources
Using Multiple Databases with JBoss
Nicholas Goodman on Business Intelligence

Consider these informations valid for the pentaho_demo release.

Comment: Posted by finiderire at Oct 29, 2007 11:44

Note: I felt I should mention that the below step is for the manual install only, not the PCI. 

Good documentation, I used it to help me with a manual setup. Although there was one step missing, I had to add the following to application.xml:

myJNDI-ds.xml

Then I had to rebuild the ear file

BTW - the application.xml file was in the following path: /pentaho-res/ear/application.xml (in the j2ee download)

Comment: Posted by Nathan T at Jan 04, 2008 17:35 Updated by Nathan T

I did all this and I still dont see my Teradata DB in the list... HEEELPPPP!! (franciscomartin.cl at gmail)

Comment: Posted by Anonymous at Feb 08, 2008 10:00

Hi.

it perfect, but what about when you have, let say 5 databases in the same server and your web application use all of them. How do you do that?.

Thanks

Greetings

Comment: Posted by Anonymous at Mar 02, 2008 15:49

The example uses a seriously outdated JDBC driver class for MySQL. Should probably use com.mysql.jdbc.Driver

Comment: Posted by Anonymous at Jun 10, 2008 08:27

Any example of using web services data source for Pentaho BI platform?

Comment: Posted by Joshua Hua at Jul 14, 2008 14:13

This example is for the PCI version or manual version?

 Can I do the same with Oracle?

Thanks

Comment: Posted by Anonymous at Sep 12, 2008 11:50

I have done that but I get the following error:
"The JNDI datasource is not defined on server"

Somebody can help me? please... (mpastor@tissat.es)

Comment: Posted by Anonymous at Oct 02, 2008 08:35