subreport with two different datasources!

[senton9999]

Hi everybody!

 

I was just playing around with iReport. I tried to create a report with a very simple subreport. But the point i'd liked to try was to fill the masterreport with subreports-data from a diffrent datasource.

 

So what i've done until now:

 

I create a report and a suitable subreport. When i created the subreport i choose a diffrent datasource in the dialog-field which definitely works because in the query-browser of the subreport i get the correct results from DB. So, the masterreport and the subreport compile correct and produce a correct report-output.

 

But when i try to run the masterreport with the subreport in it, filling of the report stops because of an sql-exception:

 

java.sql.SQLException: Invalid object name 'jsuser'. at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)

 

This is clear for me, because he is trying to use the master-report database connection to retrieve the data for the subreport. I really don't know where and how i should change this behaviour.

 

I could solve this problem using a new Parameter creating a new JDBC:mysql connection to the correct database server and using this one in the subreports Connections Expression: $P{dbcon}.

 

Param dbcon:

java.sql.DriverManager.getConnection("jdbc:mysql://localhost/jasperserver", "username", "password")

 

But i really don't like to put my username and password for my database hardcoded in the statement. So i tried to add a second parameter to the masterreport asking for the password and changed the dbcon as follows:

 

java.sql.DriverManager.getConnection("jdbc:mysql://localhost/jasperserver", "username", "$P{dbPass}")

 

When i fill report it throws an exception because the connection was refused. I guess it is not possible to use a parameter in a paramter according to their diffrent initialization?

 

How can i get Subreports working with different datasources or how can i solve the problem with the parameter in my custom db-param.

 

I apologize for the long posting, but i hope anybody can help me with this problem!

 

stefan

[lucianc]

Short answer to your second question (note the missing quotes around $P{..}):

Code:

java.sql.DriverManager.getConnection("jdbc:mysql://localhost/jasperserver", "username", $P{dbPass})

 

Regards,

Lucian

[senton9999]

thx for your answer, but if i create the connection this way, he tries to connect to DB without password. When i write the following:

 

java.sql.DriverManager.getConnection("jdbc:mysql://localhost/jasperserver", "root", "$P{dbPass}"), he recognize that i try to connect to db with password, but i'm not able to establish a connection because of:

 

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

 

So i think, in quotes the Parameter is not correctly interpreted and without the function doens'nt recognize the password.

 

Maybe a problem with the initialisation of the Parameters?

 

thx, stefan

[lucianc]

Is the dbcon parameter declared after the dbPass parameter?

[senton9999]

oh man, I'm really stupid! i didn't expect that it would be that easy! :)

 

thx a lot, for your help!

 

Do you have an idea why the iReport function for setting a new Datasource in the subreport-creation-dialog doesn't work? could it be a bug??

 

 

stefan

[lucianc]

Don't really know iReport, so I'll leave this for others to answer.