[#11151] - Using a JNDI DS for subreport ?

Category:
Feature request
Priority:
High
Status:
Confirmed
Project: Severity:
Major
Resolution:
Open
Component: Reproducibility:
Always
Assigned to:
0

Hi,

I have a report (JNDI DataSource on JasperServer, PostGreSQL) with a subreport using a parameter for the connection expression, class : java.sql.Connection.

I use this subreport to get back a numeric value from a different database (Oracle), hard-coding the path :
java.sql.DriverManager.getConnection("jdbc:oracle:thin:@<SERVER>:1521:<DB>", "<USER>", "<PASSWORD>")

For any reason I have now a permanent message in Studio and JasperServer on 2 environments: "java.sql.SQLException: No suitable driver found for jdbc:oracle..."
No changes of jdbc driver, java version... And the Data Adapter test in JasperStudio is still OK...

I didn't find any solution yet but I wonder how to use a JNDI DataSource from JasperServer for the subreport, as for the main report?
I already have the JNDI DataSource on JasperServer for this 2nd Oracle database to connect to.

The best I found is this article but I did not succeed (syntax error), any ideas please ?

https://community.jaspersoft.com/blog/jndi-datasources-subreports-jasper...

((javax.sql.DataSource)(new javax.naming.InitialContext())
.lookup("java:comp/env"
+
((com.jaspersoft.jasperserver.api.metadata.jasperreports.domain.JndiJdbcReportDataSource)
(com.jaspersoft.jasperserver.api.engine.jasperreports.util.RepositoryUtil
.getThreadRepositoryContext()
.getRepository())
.getResource(null,"/Resources/DS/JNDI_Anemone"))
.getJndiName()))
.getConnection()

Thanks in advance,
Yann

v6.5.1
Yann22300's picture
Joined: Apr 29 2015 - 1:10am
Last seen: 1 month 5 days ago

24 Comments:

#1

In JasperReports Server you can set a report/subreport/subdataset property called "com.jaspersoft.jrs.data.source" as the path of a data source in the repository. See https://community.jaspersoft.com/wiki/how-use-2-different-database-conne...

Regards,
Lucian

#2

Hi Lucian,

Thanks a lot for your answer. I added a property "com.jaspersoft.jrs.data.source" with value "repo:/Resources/DS/JNDI_Anemone" in my subreport.
I understand the subreport will use this property as a default data source to retrieve data.
In the main report, on the subreport element, I just let the expression with the path of the report, but no "connexion expression" or "data source expression".
If I understood correctly it should be ok this way and I have no error but I don't get back my numerical value from the subreport anymore.
Did I miss anything ?

Regards,
Yann

#3
  • Status:New» Feedback Requested
#4

Are you running the report in JasperReports Server?

#5

Yes, I was thinking to always use the JNDI Datasources of JasperReports Server instead of hard coding the path in a parameter.
I attached a screenshot with :
- main report and the subreport element, with only the path in Expression
- subreport without elements in it, but an added property "com.jaspersoft.jrs.data.source"

ps : the subreport alone is ok from JasperReports Server, but it uses directly the JNDI data source

AttachmentSize
Image icon sr_jndi.png84.21 KB
#6

What JRS version are you using?

#7

JRS 6.4.0 - 6.4.2

#8

Hi,

Does the screenshot help ? Maybe I just didn't fill the properties properly ?
The only alternative I have is to rebuild my report but I'm still convinced I can plug my subreport using a JNDI datasource of JRS !?
Thanks beforehand,

Regards,
Yann

#9

I made a test with JRS 6.4.2 and setting com.jaspersoft.jrs.data.source on a subreport worked fine for me. I'm attaching my report, it uses the sample DBs (master report uses sugarcrm and the subreport uses foodmart JNDI).

If you have a JRS instance with the sample DBs installed, import my report and check if it works. I'm not able to tell from the screenshots why it doesn't work for you, creating a test report with the sample DBs would be easier to investigate.

AttachmentSize
Package icon export43.zip5.58 KB
#10

Thank you Lucian for your test.
I runned your report and it is ok. But there are differences between your report and mine that maybe explain the behaviour :

- your subreport display information but not mine. ps : my subreport on his own is ok
- you don't pass any parameter and don't get back any variable. My problem is the variable to get back
- your subreport is a jrxml into your report unit, mine is a reportUnit in /Resources/...

How do you make a connection between the 2 dataset if you don't pass any parameter?

ps : On the subreport element I added 'repo:' in the expression (missing in my previous screenshot) but still not succesfull...

Does it give you a clue ?

#11

Me again, I did more tests and I got more informations.

In JasperReport Server, via the UI I set the parameters 'org.hibernate.SQL' and 'net.sf.jasperreports.engine.query.JRJdbcQueryExecuter' to DEBUG to see the value of the parameter for the subreport SQL request.
Then in the jasperserver.log I saw for my subreport :

'2018-09-14 18:02:08,659 WARN JRJdbcQueryExecuter,EA_Diag_003_SIG_Anemone_Fuite subreports #2:144 - The supplied java.sql.Connection object is null.'

I reopened my subreport and actually the property "com.jaspersoft.jrs.data.source" has gone. 7 properties instead of 8 a few seconds later!
I did it twice, the property vanishes!
Looks like a bug, doesn't it ? :-(

#12

I did more tests :

- JasperStudio 6.5.0 : KO
- JasperStudio 6.6.0 : KO
I guess the problem is on the server side but :
- JasperReports Server 6.4.0 : KO
- JasperReports Server 6.4.2 : KO
- JasperReports Server 6.4.3 : not tested
- JasperReports Server 7.1.0 : not tested

I don't have an environment for now for these 2 last releases.

#13

I moved the subreport into a separate report unit and added a parameter and return value to it. It still works fine.

If you think there's a bug, try creating a test case with the sample DBs so that we can reproduce it. Otherwise investigating the problem will be much more difficult.

AttachmentSize
Package icon export48.zip6.73 KB
#14

I runned your report and everything is ok.
So I created a new (sub)report and added the property com.jaspersoft.jrs.data.source : "repo:/datasources/JServerJNDIDS"
Then I published it twice, once choosing the 'JServer JNDI Data Source', once choosing 'Don't use any Data Source'.

In both cases, when I reopen the report from the repository the property is missing.
And I have a property 'ireport.jasperserver.report.resource : /reports/SR_files/main_jrxml' you don't have in your.

I see you also used JS 6.5.1 to create your reports, but which release of JRS ?
I attached a screenshot with the 2 sets of properties.

AttachmentSize
Image icon reports_properties.png22.06 KB
#15

I installed latest releases JRS 7.1.0 and JS 6.6.0 to move forward :

- JS 6.5.0 & JRS 6.4.0 (Windows) : KO
- JS 6.6.0 & JRS 6.4.0 (Windows) : KO
- JS 6.5.0 & JRS 6.4.2 (Unix) : KO
- JS 6.5.0 & JRS 7.1.0 (Windows) : KO
- JS 6.6.0 & JRS 7.1.0 (Windows) : KO

The property "com.jaspersoft.jrs.data.source" is always deleted once published on the repository!

Ps : I tested with 2 others properties (com.jaspersoft.studio.report.description & com.jaspersoft.studio.data.defaultdataadapter) and it is OK

#16
  • Reproducibility:N/A» Always
#17

I didn't use Studio to design or publish the reports. If Studio removes the com.jaspersoft.jrs.data.source property, then there's indeed a problem.

#18
  • Priority:Normal» High
  • Severity:Minor» Major

You think the problem is in Jasper Studio ?
So JasperReports in JasperStudio would have a bug, and JRS is only stocking the report at this time ?

You don't have the problem cause you use eclipse + jasper plug-in, do you ?

If this is right I'll probably try cause I need a solution as I didn't find why to use a jdbc connection string in a parameter has failed for a few weeks...

Thank you Lucian for your tests and your time

#19

I installed Eclipse 4.8.0 + jasper plug-in 6.6.0.
I tested with JRS 6.4.0 : KO

I compared the 2 jrxml, yours and mine nothing special..

#20

I used neither Studio nor the plugin, I edited the JRXML files in a text editor (starting from sample reports).

My goal was to answer your question "... I wonder how to use a JNDI DataSource from JasperServer for the subreport, as for the main report?"

My suggestion was to use the com.jaspersoft.jrs.data.source property, which as far as I know works properly in JasperReports Server.

If Studio prevents you from uploading reports which use that property, I guess that it's a problem with Studio. I don't know that much about Studio, so I'll let other people have a say.

#21

I miss something, how do you publish your report so ?

I did a last test, your report and subreport works fine, so I deleted the property from your subreport, published it, reopened it, added the property, published again and reopened it : the property has gone.

I tested with the plug-in, without the Studio, so the problem is in the piece of code that send data (via REST ?)

Anyone is using subreport with JNDI DataSource ? I'd be really surprised..

#22

I published the reports via the JasperReports Server web UI.

#23

Ok, this is why it worked when I imported your reports.
If I do the same my report 'should' run ok then.

I'm out for a week, I'll test as soon as I can. If it works there is a workaround.

Do you think this bug, concerning at least Jasper Studio & Jasper plug-in 6.5.0 and 6.6.0, has a chance to be attached to the next release ?

Thanks Lucian

#24
  • Status:Feedback Requested» Confirmed

Hi,

Just to let you know that, as Lucian said, the only workaround is to use the JasperReports Server web UI to export, fix then import your subreports.
This because of the property "<property name="com.jaspersoft.jrs.data.source" value="repo:/Resources/DS/JNDI_xxx"/> you have to add within the xml of your subreport.

Is there a special request to do for this bug to be considered ?

Regards,
Yann

Feedback