Using JNDI Connections in Spoon Data Integration

The Pentaho ETL tools are really powerful, flexible and useful. However, being so complex they are prone to sometimes throwing errors that aren’t that easy to interpret. To assist others with this, I’m posting some of these error strings and how I managed to solve them. This should help when searching in Google for that exact string.

I’d opened an ETL off the 7.1 Enterprise Server Repository (running on my laptop) and was running in it my local copy of Spoon (so I could watch the progress of the data live) when it threw the following mysterious error:

Error connecting to database [b3staging_ro] :org.pentaho.di.core.exception.KettleDatabaseException:
Error occurred while trying to connect to the database

javax.naming.NamingException: Invalid data source:’b3staging_ro’
Invalid data source:’b3staging_ro’

org.pentaho.di.core.exception.KettleDatabaseException:
Error occurred while trying to connect to the database

javax.naming.NamingException: Invalid data source:’b3staging_ro’
Invalid data source:’b3staging_ro’

at org.pentaho.di.core.database.Database.normalConnect(Database.java:472)
at org.pentaho.di.core.database.Database.connect(Database.java:370)
at org.pentaho.di.core.database.Database.connect(Database.java:341)
at org.pentaho.di.core.database.Database.connect(Database.java:331)
at org.pentaho.di.core.database.DatabaseFactory.getConnectionTestReport(DatabaseFactory.java:80)
etc etc …..

I tracked down the connection and it was indeed using a JNDI connect. I checked on the server for the tomcat/webapps/pentaho/META-INF/context.xml and it was defined there as normal:

Everything checked, but it wouldn’t work when I ran it in Spoon. After an hour of very serious cogitating, it dawned on me that the context of the server and the spoon aren’t the same and Spoon needs the JNDI connection defined somewhere it knows about.

And local JNDI connections are defined in <Spoon-install-path>/data-integration/simple-jndi/jdbc.properties as follows:

b3staging_ro/type=javax.sql.DataSource
b3staging_ro/driver=org.postgresql.Driver
b3staging_ro/url=jdbc:postgresql://localhost:5432/staging?searchpath=public
b3staging_ro/user=b3staging_user
b3staging_ro/password=thepassword

Problem solved.