I'm not sure what thread to put this in, please direct me to the correct one if this one isn't it.
I'm struggling with a sub report in iReport.
I have a report that selects from a MSSQL database, and uses a sub report to do a select from a MySQL database. I've successfully done it the other way around (report selects from MySQL, sub report selects from MSSQL), but doing it this way around has me stumped.
This is how I'm doing it currently: Main report (just the sub report tags, let me know if I need to post more info about the main report. I replaced some of the variables, my work requires me to):
<subreport>
<reportElement x="360" y="0" width="100" height="20"/>
<subreportParameter name="ID">
<subreportParameterExpression><![CDATA[$F{pkiCandidateID}]]></subreportParameterExpression>
</subreportParameter>
<connectionExpression><![CDATA[java.sql.DriverManager.getConnection("jdbc:mysql://<ip address>:3306/<db name>","<username>","<password>")]]></connectionExpression>
<subreportExpression class="java.lang.String"><![CDATA["repo:/Enterprise/jrxml/Candidates/report1_subreport1.jrxml"]]></subreportExpression>
</subreport>
The error I am receiving:
Error filling print...
Error executing SQL statement for : report1_subreport1
net.sf.jasperreports.engine.JRException:
Error executing SQL statement for : report1_subreport1
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:684)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:605)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:901)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:845)
at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247)
at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:877)
at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)
at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tCandidate'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:281)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137)
... 11 more
Print not filled. Try to use an EmptyDataSource...
Please let me know if I need to give any more information, and please, if you know what I'm doing wrong, help me out. This has stumped me for 2 days now.
Looking at the exception it is trying to use the MSSQL Server jdbc Driver. Are you sure you registered the MySQL JDBC driver first. Maybe when your app starts, or before executing try running:
Class.forName("com.mysql.jdbc.Driver");
Other than that, you could build the connections in your application before calling the report, and pass them in as a parameter, which you could use one for the main report and a second could be passed through to your subreport.