OdaException - when result set returns zero records

ganesh1984

Hi All,

I am using BIRT 2.3.2 in my Project. I have created many reports so far and executed them successfully. I am using Hibernate data source for creating reports. Suddenly i am getting a strange issue in my reports.

Scenario:

User can enter the data from the Front end (xhtml's are used for UI). These data's will be passed to the query as parameters and then the report will be rendered. I am getting the report as i wanted (table , charts with exact data) when the result set of the query returns more than zero record.

Issue:

If the result set of the query returns zero records, then i will get the following exceptions.

org.eclipse.birt.data.engine.odaconsumer.Connection prepareOdaQuery
SEVERE: Cannot prepare statement.
org.eclipse.datatools.connectivity.oda.OdaException
at org.jboss.tools.birt.oda.impl.AbstractOdaFactory.parseQuery(AbstractOdaFactory.java:114)
at org.jboss.tools.birt.oda.impl.AbstractOdaFactory.prepare(AbstractOdaFactory.java:51)
at org.jboss.tools.birt.oda.impl.HibernateOdaQuery.prepare(HibernateOdaQuery.java:45)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doPrepare(OdaQuery.java:210)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.prepare(OdaQuery.java:167)
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:222)
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareStatement(Connection.java:159)
at org.eclipse.birt.data.engine.executor.DataSource.prepareStatement(DataSource.java:309)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:261)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:345)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:331)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:498)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:189)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:177)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:143)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:482)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:115).......................
..........................................................................................................................................................
......................ERROR [STDERR] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
19:28:55,968 ERROR [STDERR] Jul 30, 2009 7:28:55 PM org.eclipse.birt.report.engine.executor.ExtendedGenerateExecutor execute
SEVERE: An exception occurred during processing. Please see the following message for details:
Failed to prepare the following query for the data set type org.jboss.tools.birt.oda.dataSet.
[select sum(loanapp.amount),avg(loanapp.amount),
avg(loanapp.primaryCustomer.creditReport.summary.scoreValue),
count(loanapp.statusHistory.startTime),
month(loanapp.statusHistory.startTime) ,
date(loanapp.statusHistory.startTime),
(select count(la.status) from
LoanApplication la where month(la.statusHistory.startTime) = month(loanapp.statusHistory.startTime)
group by month(la.statusHistory.startTime))
from LoanApplication loanapp where loanapp.status = 'APPROVED' and
loanapp.statusHistory.startTime between ? and ?
group by month(loanapp.statusHistory.startTime)]
org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details:
Failed to prepare the following query for the data set type org.jboss.tools.birt.oda.dataSet.
[select sum(loanapp.amount),avg(loanapp.amount),
avg(loanapp.primaryCustomer.creditReport.summary.scoreValue),
count(loanapp.statusHistory.startTime),
month(loanapp.statusHistory.startTime) ,
date(loanapp.statusHistory.startTime),
(select count(la.status) from
LoanApplication la where month(la.statusHistory.startTime) = month(loanapp.statusHistory.startTime)
group by month(la.statusHistory.startTime))
from LoanApplication loanapp where loanapp.status = 'APPROVED' and
loanapp.statusHistory.startTime between ? and ?
group by month(loanapp.statusHistory.startTime)]
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:493)
at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:115)
at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:79)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:253)
at org.eclipse.birt.report.engine.executor.ExtendedGenerateExecutor.executeQueries(ExtendedGenerateExecutor.java:204)
at org.eclipse.birt.report.engine.executor.ExtendedGenerateExecutor.execute(ExtendedGenerateExecutor.java:65)
at org.eclipse.birt.report.engine.executor.ExtendedItemExecutor.execute(ExtendedItemExecutor.java:61)..............................
.....................................................................................................................................................................ERROR [STDERR] voke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the following query for the data set type org.jboss.tools.birt.oda.dataSet.
[select sum(loanapp.amount),avg(loanapp.amount),
avg(loanapp.primaryCustomer.creditReport.summary.scoreValue),
count(loanapp.statusHistory.startTime),
month(loanapp.statusHistory.startTime) ,
date(loanapp.statusHistory.startTime),
(select count(la.status) from
LoanApplication la where month(la.statusHistory.startTime) = month(loanapp.statusHistory.startTime)
group by month(la.statusHistory.startTime))
from LoanApplication loanapp where loanapp.status = 'APPROVED' and
loanapp.statusHistory.startTime between ? and ?
group by month(loanapp.statusHistory.startTime)]
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:233)
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareStatement(Connection.java:159)
at org.eclipse.birt.data.engine.executor.DataSource.prepareStatement(DataSource.java:309)
at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:261)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:345)
at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:331)
at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:498)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:189)
at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:177)
at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:143)
at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:482)
... 74 more
Caused by: org.eclipse.datatools.connectivity.oda.OdaException
at org.jboss.tools.birt.oda.impl.AbstractOdaFactory.parseQuery(AbstractOdaFactory.java:114)
at org.jboss.tools.birt.oda.impl.AbstractOdaFactory.prepare(AbstractOdaFactory.java:51)
at org.jboss.tools.birt.oda.impl.HibernateOdaQuery.prepare(HibernateOdaQuery.java:45)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doPrepare(OdaQuery.java:210)
at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.prepare(OdaQuery.java:167)
at org.eclipse.birt.data.engine.odaconsumer.Connection.prepareOdaQuery(Connection.java:222)
... 84 more

It has become very difficult to debug this issue . can some one please help me to resolve this issue.

regards,
ganesh

Find more posts tagged with

Comments

mwilliams

Hi ganesh,

Does this only happen in this scenario? If you create a report with the sample database that returns no data, do you get the same error?

ganesh1984

Hi mwilliams,

I checked with the sample data base. I am not getting this exception for sample database when the results set returns zero records. so what could be the issue when we use hibernate data source.

waiting for your reply...

regards,
ganesh

ganesh1984

can some one help me to solve this issue......

regards,
ganesh

mwilliams

ganesh, 
 
What type of dataSource were you using before you started using Hibernate? You never had an issue with them? I have not used Hibernate, so I don't know how much help I can be. 
 
Here are some of the devShare posts having to do with using BIRT with Hibernate. Maybe one of them will help you find something wrong with your setup. 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/704-how-to-integrate-eclipse-birt-with-hibernate/#description'>How to Integrate Eclipse Birt with Hibernate - Designs & Code - BIRT Exchange</a> 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/839-scripted-data-set-with-hibernate/#description'>Scripted Data Set with Hibernate - Articles - BIRT Exchange</a> 
 
<a class='bbc_url' href='http://www.birt-exchange.org/devshare/designing-birt-reports/423-birt-community-contributions/#description'>BIRT Community Contributions - Designs & Code - BIRT Exchange</a>

ganesh1984

Hi williams,

Thanks for your reply. I had already gone through the links you had sent. Infact i have followed the same articles when i started working on BIRT with Hibernate. i didn't find any mistakes in my steps.

I am not getting any exception when the result set returns more than zero records. do you know how to handle result set ?. can we do anything in the afterClose() method (or any such methods), so if the result set is empty we can show some customized message like "No Records Found " .

Note: I am not getting this exception when i am using jdbc datasource (native sql queries) even if the result set is empty. So, only the HQL is giving problem.

waiting for your reply.

regards,
ganesh

ganesh1984

Hi All,

I got the reason for this exception. I have a table in the top of the report and below the table i have chart. after struggling for a long time i observed one change. I just removed the chart part of the report and pressed the preview button. Suddenly there are no exceptions at all, even though the result set is empty. so i concluded , we can still see empty table with column names. even if the result set is empty,

I used the following expression in the "visibility" of the table .

Expression:

if(row.__rownum!=-1){
false
}else{
true}

Now when results set is empty , table is hidden . now i am not even seeing columns of the table. So every thing is working as far as table is concerned.I learnt hiding table based on the records count. Great!!!.

After all these new changes i tried to revert my report. I got the same exception.
This exception is regarding chart.

Exception :

The following items have errors:

Chart NewChart:
- An exception occurred during processing. Please see the following message for details:
Failed to prepare the following query for the data set type org.jboss.tools.birt.oda.dataSet.
[select la.product.name ,
count(la.product.name),
sum(la.amount), avg(la.amount)
from LoanApplication la
where la.statusHistory.startTime between ? and ?
and la.client.id = ?
group by la.product.name]

I tried to use the same expression to hide chart also. but no luck. still i am getting exceptions.

Conclusion: If the result set is empty, table will be render but not chart. it throws exception during the rendering phase of chart. Infact what it should show in the chart(pie) when the result set is empty.

please tell me how to hide the charts when the result set is empty.

waiting for your reply.

Note: I am getting the above problem only with hibernate queries (HQL) . It works fine while using native sql queries.

regards,
ganesh