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

Problem

When running a Domain Ad Hoc Report with multiple selected filter values, user got the following error:

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 multi-selected value list in domain ad hoc view filter contains comma and/or apostrophe (single quote) that PostgreSQL engine is unable to handle, For example, if the filter value consists

['Tibco Software, Inc.', 'Jaspersoft']

query prepare process expects 'Tibco Software' is wrapped around with single quote before the comma that separates the listed values. Since the single quote is missing after "Software", it failed the process causing the error. The same could happen if listed values contain single quote because of missing expected comma after the single quote:

['Today's Date','Sales Total']     

Resolution

User will need to suppress those characters from user's data if the fields will be used as filters to avoid this error.

User can define a calculated field with SQL function to remove those characters then inner join it with the table to feed domain. User can then use this calculated field to filter Ad Hoc view data to meet their needs. The only caution is that the "muffled" filter will not be able to tell the difference between values from true table content and a modified one, such as "Tibco Software, Inc." and  "Tibco Software Inc.". In most cases, however, this could cause of little concern.     

=================================================================

TTC-20191030