How to Solve "PSQLException: The column index is out of range" Problem When Running A Query Based Report in JasperReports Server? (Part 1 of 2)

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
    at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:

............................

Cause

This error comes from PostgreSQL query prepare process and relates to query parameters. The root cause of the error is the parameter values for the query contains unexpected and incorrect values that PostgreSQL engine is unable to handle. 

Resolution

User should review their report designs and pay close attention to any hidden parameters in the report that are used in the report query especially with "placeholder" parameters represented as $P!{} in the query. For example, if user used the following expression in a hidden parameter as:

<parameter name="HIDDEN_PARM" class="java.lang.String"
           isForPrompting="false" evaluationTime="Early">
    <defaultValueExpression>
        <![CDATA[({some conditions based on user input parameter values}? "user_table_colume = 0" : "$X{IN, user_table_colume, USER_INPUT_PARM}"]]>
    </defaultValueExpression>
</parameter>

where $P{USER_INPUT_PARM} is a collection type and use this hidden parameter in a report query as:

select  user_table_colume, ....... from user_table where $P!{HIDDEN_PARM}

If the condition is not met as described in the defaultValueExpression at the report execution time, the generated query parameter will contain

$X{IN, user_table_colume, USER_INPUT_PARM}

The query prepare process is expecting an array of values either as numeric set of [1,2,3......] or as String set of ['TIBCO', 'Jaspersoft', 'Tech Support'.....], etc, hence gets the error.

Users must correct their report design to fix the hidden parameter expression to avoid this problem. In this case, since user wants to rely on JasperReports (JR) $X{} syntax to handle collection type matching but is unable to use it in a hidden parameter due to JR limitation, we can change the design approach to use hidden parameter to handle user input parameter as a condition flag in report query. 

<parameter name="HIDDEN_PARM" class="java.lang.String"
           isForPrompting="false" evaluationTime="Early">
    <defaultValueExpression>
        <![CDATA[({some conditions based on user input parameter values}? "true" : "false"]]>
    </defaultValueExpression>
</parameter>

Then use this flag to set two conditions with mutually exclusive OR to select report data one way or the other.

select  user_table_colume
, .......
from user_table 
where （user_table_colume = 0 and $P!{HIDDEN_PARM}）
or
（$X{IN, user_table_colume, USER_INPUT_PARM} and not $P!{HIDDEN_PARM}）

With this approach, users should be able to handle report multi-select input control for all values types and avoid the index out of range error.

Debugging Tips

If the report design is rather complex and it is hard to spot the offending parameter expressions, user can use the following approach to track down the problem:

1. Create a skeleton report template JRXML file with identical parameter settings;

2. Display those parameters in report title band of the new skeleton report ;

3. Set report to "display all but detail band" "when no data" in report property;

4. In JRS, make a copy of your original report unit that has the error - locate report unit, use "Copy" and "Paste" to make a copy, preferably in a new temp folder for debugging;

5. Edit the debug report unit, and using "Upload a Local File" option in report "Set Up" web UI to browse and upload the skeleton report template JRXML;

6. Set report Data Source to "Do not link a data source" - This is important. We want to run this debug report without execution the report query or we will get the error without producing a report output;

7. Run this report and review report parameter values in the output to help spot parameter used in report query but contains invalid values.

References

JR hidden parameter

TTC-20191030-1808989