Parameter null when SQL moved from JRXML to repo

[theodan]

Hello.

 

I have a parameter that I use in my report query to supply part of the SQL itself (using $P! syntax).  The problem I'm seeing is that everything works fine when the <queryString> is defined within the JRXML, but when I move the query into the repository and set my report unit to use it, this same parameter gets substituted into the SQL as "null" at report runtime.

 

I found some past forum topics suggesting there were some issues with parameters in queries becoming null when the queries were moved from the JRXML to the repo, but I haven't yet seen a definitive explanation of what the problem/bug and workaround are.

 

My JRXML:
 <parameter name="idList" isForPrompting="true" class="java.lang.String">
  <defaultValueExpression ><![CDATA[""]]></defaultValueExpression>
 </parameter>
 <parameter name="includeArchived" isForPrompting="true" class="java.lang.Boolean">
  <defaultValueExpression ><![CDATA[boolean.TRUE]]></defaultValueExpression>
 </parameter>
 <parameter name="sqlIncludeArchived" isForPrompting="false" class="java.lang.String">
  <defaultValueExpression ><![CDATA[$P{includeArchived}.booleanValue() ? "" : "and ARCHIVE_DATE is null"]]></defaultValueExpression>
 </parameter>

 

My query:
 select * from T where ID in ($P!{idList}) $P!{sqlIncludeArchived}

 

The weird thing is that idList works just fine.  It's just sqlIncludeArchived that becomes null.

 

I'm running the report via "/jasperaccess/generateReport", so I don't think input controls have anything to do with this.  Just for kicks, I tried defining an input control for includeArchived, but that had no effect.  idList already had an input control defined.

 

We are running both JasperServer 2.1 and 3.5, and the problem exists in both.

 

Currently my only workaround is to move the query back into the JRXML, but this will make repository maintenance a nightmare, because this issue is affecting a lot of report units that share the same query.

 

Any help would be greatly appreciated.

 

Thanks,

-Dan

Post Edited by theodan at 09/27/2009 18:23

[theodan]

Sorry for the bad formatting originally.  Hopefully now this is easier to read.

[mdahlman]

Dan,

 

My only idea was adding in the input control... and I see that you tried that already.

 

Can you post a minimal .jrxml that illustrates the problem? (Just post the version with the query included in the .jrxml. It's easy enough to start from that and manually move the query into the repository.)

 

From that maybe we can log a precise bug... or even think up a workaround.

 

Regards,
Matt

[lucianc]

Parameter default values are not used when the report unit has a query.  You can log this as a bug.

The only workaround that I can think of is to fix this by extending EngineServiceImpl as below and using the extended class in applicationContext.xml.

Regards,

Lucian

Code:

public class PatchedEngineSeriveImpl extends EngineServiceImpl {	@Override	protected JasperPrint fillQueryReport(ExecutionContext context,			JasperReport report, Map reportParameters, Query query)			throws JRException {		Map params = JRParameterDefaultValuesEvaluator.evaluateParameterDefaultValues(				report, reportParameters);		return super.fillQueryReport(context, report, params, query);	}}