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
Have no fear – it’s easy to fix.
- 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
- Create a new XML file, with the following data (update to your environment):
<iReportConnection name="Vertica" connectionClass="com.jaspersoft.ireport.samples.db.SampleDatabaseConnection">
- 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.
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.
- 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.
- 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).
- Install Tomcat. I used Tomcat 6, but 7 is supported too.
- 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.
- Copy the Vertica JDBC driver to Tomcat (from
- Start Tomcat (using
- Log in to the Jasper Reports application at
http://your_host:8080/jasperreports. Default username is
jasperreports and the password is
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
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:
- Open the
context.xml file in the
- Add the following lines:
<Resource name="jdbc/vertica" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
- Now create a JNDI based data source in Jasper (see here for more details)
- 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.
- Restart the Tomcat server
That’s it. Hope it helped 😉