Connecting iReport to Vertica

I’m continuing my journey of connecting JasperReports and Vertica, and it’s not an easy task.
The Vertical team did some damage – they changed their JDBC driver class name (it used to be com.vertica.Driver and now it com.vertica.jdbc.Driver), so the iReport application doesn’t let you connect to the Vertica database, since you constantly get a ClassNotFoundException.
Have no fear – it’s easy to fix.

1. Add the Vertica JDBC driver JAR to the iReport ClassPath. It can be done under Tools|Options–>ClassPath. Just click the “Add JAR” button and map it to the location of your JAR file
2. Create a new XML file, with the following data (update to your environment):
   [xml]
   <?xml version=”1.0″?>
   <iReportConnectionSet>
   <iReportConnection name=”Vertica” connectionClass=”com.jaspersoft.ireport.samples.db.SampleDatabaseConnection”>
   <connectionParameter name=”ServerAddress”><![CDATA[IP_ADDR]]></connectionParameter>
   <connectionParameter name=”SavePassword”><![CDATA[true]]></connectionParameter>
   <connectionParameter name=”Url”><![CDATA[jdbc:vertica://IP_ADDR:5433/DB_NAME]]></connectionParameter>
   <connectionParameter name=”JDBCDriver”><![CDATA[com.vertica.jdbc.Driver]]></connectionParameter>
   <connectionParameter name=”Database”><![CDATA[DB_NAME]]></connectionParameter>
   <connectionParameter name=”Password”><![CDATA[PASSWORD]]></connectionParameter>
   <connectionParameter name=”Username”><![CDATA[dbadmin]]></connectionParameter>
   </iReportConnection>
   </iReportConnectionSet>
   [/xml]
3. Create a new Data Source. Click the Report Datasources icon, and instead of new, click import and choose the XML file you created.

Your report should now work with Vertica.

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 😉