Allan Border

hello everyone

I had a similar requirement where I needed to dynamically load the query(to be used to run the report) among two queries based on some condition. This condition in my case was the database the application is using. I had to run an application with two databases(oracle and posgresql). I did not want to make much changes in my java code to run my jasper reports. I wanted to modify the jrxml file only and put the condition there. The solution given here almost worked except for a small change.
This is the part of the jrxml where I have the condition put:

<parameter name="post" class="java.lang.String">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>

<parameter name="query" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA[(new String("postgres").equals($P{post}) ? "select * from employee limit 5" : "select * from employee where ROWNUM>=1")]]>
</defaultValueExpression>
</parameter>
<queryString>
<![CDATA[$P!{query}]]>
</queryString>

Here's the explanation:
When you run the report, it is going to ask to enter value for the parameter "post". If you enter "postgres"(for postgresql) then the first query("select * from itgs_bank_master limit 5") is returned and this is set in "query" parameter and viceversa. This might seem complex but it is not. Then in the <queryString > tag, the value(which is the query) which is there in "query" parameter is put which is then picked while running the report.

isForPrompting specifies whether or not you want a prompt for entering the value for the parameter after pressing preview. This is set to true(default) in the first parameter and false in the second. Reason for this is that I had to enter the database name(oracle or postgres) I am using for running the report. In the second one I don't want to enter anything, I want it to be dynamically set during run time.

limit and rownum are different keywords in postgres and oracle respectively. Therefore different queries.

I spent a lot of time doing all this and intend to save time of those who seek a similar solution.

A big thanks to yann luppo's post without which I wouldn't have come up with this solution.

Hi guys

I have a jasper report for which the database I have used is postgresql. Now I have a situation where I got to run the same jasper report with oracle database. Now the problem is that both database differ in syntax. So I've ended up having 2 sql's, one each for postgresql and oracle. I am looking for a way where depending on the database I use the query for the corresponding database should be dynamically fired in iReport. I tried doing the following way but it doesn't work. It works if the queries are for the same database. My observation about this is that during compilation time of the report the queries are checked for syntax. So if you have 2 queries for 2 database then no matter what connection you have it'll always throw an error. For example if you have postgres connection then you'll see a syntax error thrown for the oracle query and vice versa. limit and rownum are 2 different keywords in postgres and oracle respectively but they perform the same function which is to limit the result set.

<parameter name="dynamicSelect" class="java.lang.String" isForPrompting="false">
<defaultValueExpression>
<![CDATA[
(new String("testValue").equals($P{reportType}.toLowerCase()) ? new String("Select * from employee limit 10") : new String("Select * from employee where ROWNUM<=10"))
]]>
</defaultValueExpression>
</parameter>

<queryString>
<![CDATA[
$P!{dynamicSelect}
]]>
</queryString>

Hope to get a quick reply soon. I am need of urgent help and I am badly stuck.
Thanks in advance.