Jasper Reports WHERE Clause $X{IN,col_name,param_name}}

Classic Servoy
1

Servoy Version: 6.0.10 - build 1240
Jaspersoft Studio 6.6.2.final
Jasper Plug-In 5.0.0 a2
JRE build 1.7.0_71-b14
Sybase SQL 11

We designate financial ‘periods’ using the following string form: ‘yyyy/mm’ (e.g. ‘2014/12’ is December 2014).

When sending these periods as arguments to Jasper Reports (note the second SQL WHERE clause below), we keep getting the following error message:

com.sybase.jdbc3.tds.SybTimestamp
Wrapped net.sf.jasperreports.engine.JRException: Class not found when loading object from InputStream

In Jaspersoft Studio (preview mode), the following baseline SQL query works and emits no error message:

SELECT * FROM billing_census
JOIN billing_census_summary ON billing_census.census_summary_id = billing_census_summary.census_summary_id
JOIN billing_census_counts ON billing_census_summary.census_id = billing_census_counts.census_count_id
WHERE census_period IN ( '2013/08','2013/09','2013/10','2013/11','2013/12' )
ORDER BY census_pi_name ASC, census_fund_number ASC

Parametizing the WHERE clause (per the Jaspersoft Studio User Guide, Section 8.3.3):

SELECT * FROM billing_census
JOIN billing_census_summary ON billing_census.census_summary_id = billing_census_summary.census_summary_id
JOIN billing_census_counts ON billing_census_summary.census_id = billing_census_counts.census_count_id
WHERE $X{IN, census_period, ARM_REPORT_PERIOD_RANGE}
ORDER BY census_pi_name ASC, census_fund_number ASC

where ARM_REPORT_PERIOD_RANGE is cast as java.lang.String with no default value

and, Servoy sends the following string as the argument:

EDIT: the below line is incorrect. Please see my response further down the thread.

“census_period IN ( ‘2013/08’,‘2013/09’,‘2013/10’,‘2013/11’,‘2013/12’ )”

the error message is emitted.

It appears the ‘period’ arguments are being interpreted as dates, and not as strings (everything is declared as strings). And, what is the culprit…Servoy, JasperReport Plug-in, Jasper Studio, or Sybase SQL?

How do I fix this?

2

if you use

$X{IN, census_period, ARM_REPORT_PERIOD_RANGE}

then ARM_REPORT_PERIOD_RANGE must be a list of values and not the complete IN where expression as a string.
If supplied the complete string IN expression you can use the ‘$P!’ parameter expression:

    SELECT * FROM billing_census
    JOIN billing_census_summary ON billing_census.census_summary_id = billing_census_summary.census_summary_id
    JOIN billing_census_counts ON billing_census_summary.census_id = billing_census_counts.census_count_id
    WHERE $P!{ARM_REPORT_PERIOD_RANGE}
    ORDER BY census_pi_name ASC, census_fund_number ASC
3

Hi, Jeroen. Thanks for responding.

To All:

Sorry, my mistake…I copied the wrong text. The point is not how to implement the query, but, rather, why is/are the string argument/s being treated as a date and not as a string, thus failing the query.

I tried the following WHERE clauses in the Jasper Report query:

WHERE $P!{ARM_REPORT_WHERE_CLAUSE}

Report parameter: ARM_REPORT_WHERE_CLAUSE is cast as a java.lang.String with no default value

Servoy argument: ARM_REPORT_WHERE_CLAUSE = “census_period IN ( ‘2013/08’, ‘2013/09’, ‘2013/10’, ‘2013/11’, ‘2013/12’ )”

Result: com.sybase.jdbc3.tds.SybTimestamp
Wrapped net.sf.jasperreports.engine.JRException: Class not found when loading object from InputStream

WHERE $X{IN, census_period, ARM_REPORT_PERIOD_RANGE}

Report parameter: ARM_REPORT_PERIOD_RANGE is cast as java.util.Collection with no default value

Servoy argument: ARM_REPORT_PERIOD_RANGE = aPeriods (an array of periods)

Result: com.sybase.jdbc3.tds.SybTimestamp
Wrapped net.sf.jasperreports.engine.JRException: Class not found when loading object from InputStream

WHERE $X{[BETWEEN], census_period, ARM_REPORT_PERIOD_FROM, ARM_REPORT_PERIOD_TO}

Report parameter: ARM_REPORT_PERIOD_FROM and ARM_REPORT_PERIOD_TO are cast as java.lang.String with no default value

Servoy arguments: ARM_REPORT_PERIOD_FROM = ‘2013/08’, ARM_REPORT_PERIOD_TO = ‘2013/12’

Result: com.sybase.jdbc3.tds.SybTimestamp
Wrapped net.sf.jasperreports.engine.JRException: Class not found when loading object from InputStream

4

Have you tried to run report without any parameters from Servoy with the query which works in JasperSoft? Maybe your Servoy jasperreports plugin is missing some libraries which are present in JasperSoft.

5

Thanks for the feedback, Jeroen!

Have you tried to run report without any parameters from Servoy with the query which works in JasperSoft?

As a test, I removed all parameters from the report. Then, I assured that the report runs in Studio’s preview mode.

Next, in Servoy, I removed all report arguments; once passing a null, and another time passing any empty object ({}). When I run the report from Servoy, I get the same error message for both:

com.sybase.jdbc3.tds.SybTimestamp
Wrapped net.sf.jasperreports.engine.JRException: Class not found when loading object from InputStream

Maybe your Servoy jasperreports plugin is missing some libraries which are present in JasperSoft.

You may be correct about missing libraries (note that I am using the alpha a2 version of the latest release), but how would I determine this? I would think this would be a question for the plugin developers. Yesterday, I filed a plugin bug report on ServoyForge. Perhaps, the developers would be kind enough to respond.

Ticket No. 835