Subreport: Using a completely different datasource

[jimmo]

Hi All!

I have a subreport which reads data from a different datasource which I configured like this:

Paremeter: subConnTICKET
Paremeter Class: java.sql.Connection
Default Value Expression: java.sql.DriverManager.getConnection("jdbc:oracle:thin:@10.2.29.77:1521:ticket_pu", "ticket", "ticketpw")

Subreport Properties:
Connection Type: Use a connection expression
Connection Expression: $P{subConnTICKET}

In one master report it works fine and I believe this works because the master and the subreport are both using an oracle database. If I use the same sub-report with a different master,  I get the following error:

Error filling print... Error evaluating expression :      Source text : java.sql.DriverManager.getConnection("jdbc:oracle:thin:@10.2.29.77:1521:topd_pu", "ticket", "ticketpw")
Running against: http://10.2.29.180/slm/servlet/export/tmp.xml?user=USERNAME&pass=PASSWORD  net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression :      Source text : java.sql.DriverManager.getConnection("jdbc:oracle:thin:@10.2.29.77:1521:ticket_pu", "ticket", "ticketpw")      at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:197)
.....
Caused by: java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@10.2.29.77:1521:ticket_pu

The "Running against: " line is from our monitoring server which is the datasource used by the master report and several other subreports. This is an XML datasource and works fine without the new subreport.

What I am missing is how to tell iReport to user this other data source.

In the master report I found a parameter REPORT_DATA_SOURCE, which is hard coded to "net.sf.jasperreports.engine.JRDataSource".  My assumption is that this is passed from from iReports and this is where I run into the dead-end. Where and how do I set a completely different datasource type for the subreport.

Any assistance is appreaciated.

Regards,

jimmo

Post Edited by jimmo at 11/03/2010 08:19

[jimmo]

I got it to work by placing the Oracle jar file (ojdbc14.jar) in Java\jre6\lib\ext. I had already added the file into iReports under Options->Classpath and the sub-reports worked when I started them individuall.y It also seems that I have to load the driver explicitely. A friend suggested I simply add a field to the report with Expression Class "java.lang.String":

Class.forName('oracle.jdbc.driver.OracleDriver').getName()

It does not make sense to me that if the class is already added that I have to go through all of this.

Any ideas?

[ntatlock]

I was using oracle for main report, and trying to use ms sql server for subreport.  The solution for me was to not use Java 8 and add sqljdbc41.jar to class path.  Using the sqljdbc41.jar drivers worked when I changed the report to compile with Java 7.    

A few key things that took me some time to get right:

From main report, the subreport settings have param map expression, connection expression and data source expression all blank.

From the main report I pass a parameter to subreport's REPORT_CONNECTION.  The param I created uses expression that looks like this: java.sql.DriverManager.getConnection("jdbc:sqlserver://host:port;databaseName=dbname","user","pw")  

The subreport is in a group header band.  The query for the sub report is in the sub report only.

Since i'm not running a query on main report, I need to select All Sections No Detail 

 

Good luck!