Multiple database connection for subreport

[kitakura]

Hi, I am trying to use different database connection for subreport than Main jrxml.  When I built my report in iReport, I was able to use below in Connection/Data Source Expression choosing "Use connection expression"

it.businesslogic.ireport.util.Misc.getConnection( "repo:/datasources/datasroucename")

which I think translate as below:

<connectionExpression><![CDATA[it.businesslogic.ireport.util.Misc.getConnection( "repo:/datasources/datasroucename")]]></connectionExpression>

Now, I moved this report to Report Unit in JasperServer, this connection does not work.  Although I created same Data Sources in JasperServer.  Below is the error message:

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Error evaluating expression : Source text : it.businesslogic.ireport.util.Misc.getConnection( "repo:/datasources/datasroucename")

I realize I am using ireport method so I probably need to use JR method? but I don't know how to find similar class or method in JR.  I am new to Java so I don't know.

[swood]

The default implementation of JasperServer does not allow a different data source to be used for sub reports.

Do you really need a separate data source, or do you just want to run a different query against the same data source as the main report?

To do this, in the main report, call the subreport with the following entries:

<parametersMapExpression><![CDATA[new HashMap($P{REPORT_PARAMETERS_MAP})]]></parametersMapExpression>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>

 

Sherman

Jaspersoft

 

[kitakura]

That is unfortunate.  Because JasperServer overview states below:

"JasperServer can use JDBC data sources defined within JasperServer, or externally in an application server. A single report may use data from multiple data sources."

http://www.jaspersoft.com/downloads/Datasheet/jasperserver-0206.pdf

Another strange thing is that when I use Query base report options, I can use a data source that is not Main report.

:(

Kassie

[kitakura]

Hi I was able to do this by adding Connection/Data Source Expression choosing "Use connection expression" in iReport below:

java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "jasperdb", "password")

which I think translate as:

<connectionExpression><![CDATA[java.sql.
DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "jasperdb", "password") ]]></connectionExpression>

But I don't know how to change the driver class. 

If I want to use Oracle data source in Main jrxml and for subreport, I want to use Mysql local data source, how can I accomplish this?

 

[swood]

Try putting the code to get the connection in a scriptlet and call the method in the connection expression in JRXML.

 

Sherman

Jaspersoft

[kitakura]

Thank you Sherman.  It worked!

Kassie

[javi_123]

Hi all,

 

we have designed a dashboard in ireports  and have declared "MetaDataConnection" as a parameter which establishes a connection with the database.

The class Type of the parameter is "java.sql.Connection" and default value expression is "java.sql.DriverManager.getConnection("jdbc:mysql://localhost/jasperserver", "username", "password")".

 

The above connection works fine but when it crosses more than 200 connections which is the limit, the dashboard crashes. After every click a connection is established.

I then tried to use another parameter called "CloseDataConnection" in which i used the class type as "java.sql.Connection" and the Default value expression as "$P{MetaDataConnection}.close()"

but unfortunately this dosent work and i dnt knw how to close the connection in ireports.

 

I am not getting anywhere with this problm. Have tried searching on google and ireports forums but no result.

 

If anyone knows the solution to this problem please reply back as soon as possible.

I just need to close the connection evrytime when it executes the query.

 

Please reply back asap.

 

Cheers

[swood]

You need to manage the closing of the connections yourself, as you have tried. You may need a custom QueryExecuter to make this work.

 

Sherman

Jaspersoft

[mindlayr]

swood
Wrote:

The default implementation of JasperServer does not allow a different data source to be used for sub reports.

Why not?  The reason I'm using this product is because you state that it can use multiple data sources for subreports.  I've spent days trying to do what you state your product will do.  How am I supposed to do this?  It seems pretty simple to use the data connection in the second step of the wizard but you can't allow the use of an already defined dataconnection?

Help me use this product the way it states it can be used.  Seems like a reasonable request to me.