Connecting JasperReports to Vertica

I have a customer that asked me to build a Vertica environment and connect it to JasperReports. Fun!
So, it took a while (mostly due to technical difficulties on my end), but it works. And I thought I’d document how.

1. Install Vertica. That’s an easy task, lots of documentation on the Vertica site. Frankly – I haven’t read any of it. Just clickty-click and you’re done.
2. Create the Vertica database. Use /opt/vertica/bin/adminTools for that. Make sure you run it under the dbadmin user (use su - dbadmin if you have problems to login directly).
3. Install Tomcat. I used Tomcat 6, but 7 is supported too.
4. Install JasperReports Server (can be downloaded here). I used the WAR file version (had problems with the X Windows support over SSH) – see installation documentation here. This was not the easiest thing to do.
5. Copy the Vertica JDBC driver to Tomcat (from /opt/vertica/java/lib/vertica_5.1.1_jdk_5.jar to TOMCAT_HOME/lib)
6. Start Tomcat (using TOMCAT_HOME/bin/startup.sh)
7. Log in to the Jasper Reports application at http://your_host:8080/jasperreports. Default username is jasperreports and the password is jasperreports too.
8. Create a new DataSource in Jasper. For driver class enter com.vertica.jdbc.Driver. For URL enter jdbc:vertica://vertica_host:5433/db_name. User is dbadmin and password is the database password you entered when you created the database

9. update

   Turns out I was overly optimistic. Although Jasper UI shows the connection works, reports can’t run with this connection. You need to use a JNDI Data Source connection. Easy to do:

   1. Open the context.xml file in the TOMCAT_HOME/webapps/jasperreports/META-INF
   2. Add the following lines:
      <Resource name="jdbc/vertica" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="dbadmin" password="test"
driverClassName="com.vertica.jdbc.Driver"
url="jdbc:vertica://localhost:5433/test" />
   3. Now create a JNDI based data source in Jasper (see here for more details)
   4. I have also saw that the TOMCAT_HOME/conf/Catalina/default/jasperreports.xml file needs to be updated, and contain the same code snippet from above. I have no clue why Tomcat doesn’t update this file automatically.
   5. Restart the Tomcat server

That’s it. Hope it helped 😉