nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

BIRT 3.7.2 with Oracle 11g giving "Could not load report query" erro

maulik1

Hi,

I am using BIRT 3.7.2 with Oracle 11g database. Report is working fine when I am seeing it in Eclipse preview but it is throwing "Can not load the report query: 119. Errors occurred when generating the report document for the report element" error from BIRT viewer as well as from deployed location.

I have put ojdbc6.jar in eclipse-plugin folder as well as TOMCAT-lib directory even application's WEB-INF/lib directory. I have set BIRT log level to FINEST ( and then ALL ) but no exception or error is there in log file.

Same report working on other databases like MySQL and DB2.

Following is the report query ( with changed table and column name) :

"SELECT TABLE_1.FIELD_1,
TABLE_1.FIELD_2,
TABLE_1.FIELD_3,
TABLE_1.FIELD_4,
TABLE_1.FIELD_5,
sum(TABLE_1.FIELD_6),
TABLE_2.FIELD_7,
count(*) COUNT_1
FROM TABLE_2 TABLE_2 INNER JOIN TABLE_1 TABLE_1 ON
TABLE_2.ID_1=TABLE_1.ID_2
WHERE (TABLE_2.DATE>= {d ?} AND TABLE_2.DATE< {d ?} )
AND TABLE_2.FIELD_7 LIKE '%'
AND TABLE_1.FIELD_3 LIKE ?
AND TABLE_1.FIELD_2 LIKE ?
AND TABLE_1.FIELD_1 LIKE ?
AND TABLE_1.FIELD_4 LIKE '_%'
GROUP by
TABLE_1.FIELD_1,TABLE_1.FIELD_2,
TABLE_1.FIELD_3, TABLE_1.FIELD_4,
TABLE_1.FIELD_5, TABLE_2.FIELD_7"

Any idea or pointer to solve this problem will be greatly appreciated. Thanks in advance.

- Maulik

Find more posts tagged with

Comments

Hans_vd

The first two data set parameters, what type are they?
Date or String?
And the report parameters they are bound to: Date or String?

maulik1

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="113533" data-time="1358975875" data-date="23 January 2013 - 02:17 PM">
The first two data set parameters, what type are they? 
Date or String? 
And the report parameters they are bound to: Date or String? </blockquote>
 
 
First two dataset parameters are of "String" type and report parameters they are bound to "Date".

Hans_vd

That means that you are relying on implicit date to string conversion. You should never do that.
Can you try this:
- Make the report parameters Date type
- replace the line "WHERE (TABLE_2.DATE>= {d ?} AND TABLE_2.DATE< {d ?} )" by "WHERE (TABLE_2.DATE>=? AND TABLE_2.DATE<? )"

If this still doesn't work, try removing this part of the where clause and the first two parameters of the data set and see if that works. At least you'll know if it is the dates that are causing the problem or if it's something else.

Hope this helps
Hans

maulik1

Hi Hans,

Thanks for your response.

I tried changing the parameter type form String to Date and made the SQL as per your suggestion , but it still doesn't work.
Then I removed the complete where condition from SQL query and tried again, it still fails.

It is failing in Eclipse Preview and Birt Viewer, both places.

As soon as I switch my database from Oracle to MYSQL everything works fine in that case.

- Maulik

Hans_vd

What happens if you create a report with a data set without any parameters? 
 
Something like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT TABLE_1.FIELD_1,
TABLE_1.FIELD_2,
TABLE_1.FIELD_3,
TABLE_1.FIELD_4,
TABLE_1.FIELD_5,
sum(TABLE_1.FIELD_6),
TABLE_2.FIELD_7,
count(*) COUNT_1
FROM TABLE_2 TABLE_2 INNER JOIN TABLE_1 TABLE_1 ON TABLE_2.ID_1=TABLE_1.ID_2
WHERE ROWNUM <= 10</pre>
 
Does that give any results?

maulik1

Hi Hans,

I tried without specifying any SQL "Where" condition nor any parameter in SQL Query. It still fails in Oracle.

The moment I connect with MySQL it works fine.

I tried creating sample JDBC Prepared Statement test program to run the query against the database, and it works fine. Even with bind parameters the query works fine in test JDBC Java Program.

-Maulik

Hans_vd

Hi Maulik,

Does your Oracle data source has Property Bindings filled in?

maulik1

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="113549" data-time="1359027673" data-date="24 January 2013 - 04:41 AM">
Hi Maulik, 
 
Does your Oracle data source has Property Bindings filled in? </blockquote>
 
Hi Hans, 
 
Thanks a lot for help. 
 
After so much of pain, problem is solved. Following are the findings: 
 
1) I have removed {d ?} from the query and put only ? 
2) Report parameter and linked data-set parameter should be of date type. 
3) When you are testing on multiple databases, don't rely on plug-in. Test it following way: 
 
i) Make report for one database ( specify jndi name also) 
ii) Test report by changing datasource in application server ( e.g. tomcat) 
 
Changing datasource in plug-in corrupts the reports sometimes. 
 
Problem mainly solved by point#3. 
 
 
Thanks, 
Maulik