amongalen Posted July 20, 2018 Share Posted July 20, 2018 Here is my situationI got a stored procedure that prepares data for the raport and puts it into a global temporary table. What is important, this temp table is "ON COMMIT DELETE ROWS".Then I got my main procedure that is called from the Jasper report. As a parameter it takes a ref_cursor. First, it calls the other procedure. Immediately after that the cursor is opened for data from my temp table.I've tried to call that exact same procedure in SQL developer and it works just fine. However, when I use it in the Jasper report, there is no data returned.I've done some testing and it seems that changing temp table to "ON COMMIT PRESERVE ROWS" makes it to work. However, I don't want to change the type of the table because some other things could depend on that table.It seems to me that for some reason Jasper performs a commit just after the query (procedure call in this case) and before using data from the cursor so it gets removed from the cursor as well.Is there some other solution? Am I missing something? Maybe the problem is somewhere else? Link to comment Share on other sites More sharing options...
reportdev Posted July 20, 2018 Share Posted July 20, 2018 Any GTT with "ON COMMIT DELETE ROWS" is not getting the next row from the dataset. I have tried changing the GTT with on commit preserve and it works. yes it is frustrating and thats how Jasper is working. I believe its not Jasper committing, I think its Jasper making the session Inactive once it is done fetching the rows.I had to change some of the GTT's to perform this functionality... I can say one thing check for dependencies before changing the GTT. select * from all_dependencies ad where ad.referenced_name = 'GTT_NAME' ; Link to comment Share on other sites More sharing options...
amongalen Posted July 23, 2018 Author Share Posted July 23, 2018 I've done some futher testing. I've tried to run the exact same report using JasperReports Library from Java.When I launch the report with default connection properties it doesn't work (no data). However, setting "AutoCommit" to "false" seems to work.Is it possible to set AutoCommit to false for a data adapter in JasperSoft Studio?When configurating the data adapter there is a "Connection Properties" tab. What are the viable properties? I wasn't able to find any info anywhere. Link to comment Share on other sites More sharing options...
reportdev Posted July 23, 2018 Share Posted July 23, 2018 There is a property in the Jasper Config Reference, but not sure how to use it. http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.jdbc.holdabilityI will be contacting the TIBCO support (create a ticket with them and see what they say). Tried in both the following ways<property name="net.sf.jasperreports.jdbc.holdability.hold"/><property name="net.sf.jasperreports.jdbc.holdability" value="hold"/>failed as below:net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Unable to get next record from result set. at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548) at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523) at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)Caused by: net.sf.jasperreports.engine.JRException: Unable to get next record from result set. at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:145) at net.sf.jasperreports.engine.fill.JRFillDataset.advanceDataSource(JRFillDataset.java:1538) at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1387) at net.sf.jasperreports.engine.fill.JRFillDataset.next(JRFillDataset.java:1366) at net.sf.jasperreports.engine.fill.JRBaseFiller.next(JRBaseFiller.java:1147) at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:113) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:586) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at java.lang.Thread.run(Thread.java:745)Caused by: java.sql.SQLException: ORA-08103: object no longer exists at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193) at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1073) at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:359) at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:263) at net.sf.jasperreports.engine.JRResultSetDataSource.next(JRResultSetDataSource.java:141) ... 8 more Link to comment Share on other sites More sharing options...
reportdev Posted July 23, 2018 Share Posted July 23, 2018 Quick update: TIBCO Support mentioned that the GTT's will work only with On Commit Preserve.They are contacting the developers to see if any other options are available Link to comment Share on other sites More sharing options...
Solution szaharia Posted July 24, 2018 Solution Share Posted July 24, 2018 Properties available in "Connection Properties" tab are driver-specific. For instance, if you're using the OracleDriver class, try to set the 'autoCommit' connection property to false (see attachment). Link to comment Share on other sites More sharing options...
amongalen Posted July 25, 2018 Author Share Posted July 25, 2018 Interesting... I think I've found another bug. It is not possible to edit data adapter's properties inside of JasperSoft Studio. I mean using "DataAdapter Editor" to be precise. For some reason it doesn't let you save the changes if you haven't changed anything else.What is more, it depends on how you've created the data adapter in the first place. If it is visible in the "project explorer"/workspace it doesn't work. But if it is in the "repository explorer" it seems to work just fine.@shertage I've tried it earlier but for somehow changes weren't applied (because of the above mentioned bug). Now I've made sure the changes are saved and indeed it works.Thanks for help.ps. why there even are "two types" of data adapters? Ones in "project explorer", others in the "repository explorer". Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now