dynamic sql using optional parameter

[2005 IR Help]

By: Pete O'Donnell - peteodonnell

dynamic sql using optional parameter

2003-01-27 09:07

I'm fairly new to jasper, but love what I see so far. The one case, i haven't seen an example of or been able to figure on my own is a dynamic query. i.e.

 

if $P{barValue} exists

 

<queryString><![CDATA[sELECT * FROM FOO WHERE Bar = $P!{barValue}]]></queryString>

 

else

 

<queryString><![CDATA[sELECT * FROM FOO]]></queryString>

 

Any suggestions?

 

Thanks in advance,

-Pete

 

 

 

 

By: Chuck Deal - cdeal

RE: dynamic sql using optional parameter

2003-01-27 10:28

You can build the entire SQL query at runtime

and send it as a parameter. Then you could use it like this:

 

<queryString>$P!{MyDynamicQuery}</queryString>

 

 

 

 

 

By: Pete O'Donnell - peteodonnell

RE: dynamic sql using optional parameter

2003-01-27 10:15

Thanks for the quick reply. I tested your solution and received a strange sql exception...

 

The gist of it was an additional '>' appended to my query. It doesn't look like your exception handling is adding the '>', and I can't really tell what is.

 

...

NESTED BY :

dori.jasper.engine.JRException: Error executing report query :

 

SELECT * FROM FOO WHERE Bar = barValue>

 

 

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.

java:357)

at dori.jasper.engine.util.JRQueryExecuter.executeQuery(JRQueryExecuter.

java:131)

at dori.jasper.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:363)

at dori.jasper.engine.fill.JRFiller.fillReport(JRFiller.java:114)

at dori.jasper.engine.JasperFillManager.fillReport(JasperFillManager.jav

a:217)

at gov.ca.doj.ecp.report.ReportManager.report(ReportManager.java:48)

at gov.ca.doj.ecp.report.ReportManager.main(ReportManager.java:64)

 

 

If it's of interest, the top level exception was...

java.sql.SQLException: ORA-00933: SQL command not properly ended and the exception and printing of the query plus trailing '>' occurred whether or not the parameter had a value. So I guess the good news is the logic worked.

 

Thanks again for your help on this,

-Pete

 

 

 

 

By: Teodor Danciu - teodord

RE: dynamic sql using optional parameter

2003-01-27 10:30

 

Hi,

 

What is the value that you supply at runtime

to the "barValue" parameter?

 

Thank you,

Teodor

 

 

 

 

 

By: Pete O'Donnell - peteodonnell

RE: dynamic sql using optional parameter

2003-01-27 10:47

Here is the code (both versions) which assign the 'barValue' value...

 

parameters = new HashMap();

 

parameters.put ("barValue", null);

 

<or>

 

parameters.put ("barValue", "0109");

 

it is querying against an oracle database and the column is a char.

 

Thanks again,

-Pete

 

 

 

 

 

By: Pete O'Donnell - peteodonnell

RE: dynamic sql using optional parameter

2003-01-27 10:50

Here is the code (both versions) which assign the 'barValue' value...

 

parameters = new HashMap();

 

parameters.put ("barValue", null);

 

<or>

 

parameters.put ("barValue", "0109");

 

it is querying against an oracle database and the column is a char.

 

Thanks again,

-Pete

 

 

 

 

 

By: Pete O'Donnell - peteodonnell

RE: dynamic sql using optional parameter

2003-01-27 11:27

The error was in my jasper template, which I've been recompiling while in development, so it wasn't the parameter passed at runtime causing the issue.

 

Thanks so much for the postings. The defaultValueExpression solution will be ok.

 

Cheers,

-Pete

 

 

 

 

By: Teodor Danciu - teodord

RE: dynamic sql using optional parameter

2003-01-27 09:36

 

Hi,

 

An interesting approach would be to use the

<defaultValueExpression> of some String report

parameters that you actually never pass to the

engine.

 

Try this:

 

<parameter name="barValue" class="java.lang.String"/>

<parameter name="SQL1" class="java.lang.String">

<defaultValueExpression>"SELECT * FROM FOO WHERE Bar = " + $P{barValue}</defaultValueExpression>

</parameter>

<parameter name="SQL2" class="java.lang.String">

<defaultValueExpression>"SELECT * FROM FOO"</defaultValueExpression>

</parameter>

<parameter name="MyQuery" class="java.lang.String">

<defaultValueExpression>($P{barValue}!=null?$P{SQL1}:$P{SQL2})</defaultValueExpression>

</parameter>

 

<queryString>$P!{MyQuery}</queryString>

 

I have never tested it, but it should work.

 

Thank you,

Teodor

 

 

 

 

 

By: Leo Tai - hkleo

RE: dynamic sql using optional parameter

2004-06-25 19:25

Another suggestion:

 

SELECT * FROM FOO where bar = nvl($P{barValue},bar)

 

if $P{barValue} is null, where clause is bar=bar which is dummy.

if $P{barValus} is not null, where clause is bar=$P{barValue}

 

It works for me. My DB is Oracle.

 

Cheers,

Leo Tai