Jump to content
Changes to the Jaspersoft community edition download ×

Query error with input parameters


Recommended Posts


I´m having some problems with some Standard and Custom Reports.
Can execute without any error all reports that do not have  multi-select query inputs.
Query in report:
                           WHERE  cr.type_symbol  IN $P!{TypesList}
Input control query:
                            SELECT DISTINCT cr.type_symbol as type from cr
Then add as resource the input control.
Have 2 parameters at report:
Name: Types  Class: Collection  Nested: String
Name: TypesList Class: String Expression: "('"+org.apache.commons.lang.StringUtils.join($P{Types}.toArray(),"', '") + "')"

When i execute the report, input control works fine (shows what it´s suppossed to), then when i click "Acept" i get this error:

(Just showing "Caused By" parts)

com.jaspersoft.jasperserver.api.JSException: Error filling report Arguments:...
..... Caused by: net.sf.jasperreports.engine.JRException: Error preparing statement for executing the report query: SELECT cr.ref_num as REF_NUM, cr.open_date as OPEN_DATE, cr.close_date as CLOSE_DATE, cr.status_symbol as STATUS, cr.summary as SUMMARY, cr.impact as IMPACT, cr.priority as PRIORITY, cr.type_symbol as TYPE, request_area_description as AREA_DESC FROM cr WHERE cr.type_symbol in ('Incident', 'Problem', 'Request')
Caused by: java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected $end at ( ... N'Incident at com.ddtek.openaccess.ssp.Diagnostic.toSQLException(Unknown Source) ......

Things that i tryed:
- Instead of using $P!{TypesList}, used $X{IN,cr.type_symbol,TypesList}. Got similar error. Instead of "WHERE cr.type_symbol in ('Incident', 'Problem', 'Request')" i get an error like this: "WHERE cr.type_symbol in (?,?,?)"
- Made an input control of class string, executed report and wrote down: ('Incident','Request')  and it worked. Bad thing about this, is that i want to use multi-select query as input control, this way user don´t have to write anything.
- Tryed same report using a different datasource (this datasource point to a "test" DB while the one that fails point to "production") and it worked too. From this what i get is, maybe a JDBC controller problem?

What do you think? What am i doing wrong? Any idea will apreciate.


Link to comment
Share on other sites

  • Replies 1
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

So in your Jasper Studio, set the given input control/parameter class as java.util.Collection
Default expression new ArrayList(Arrays.asList()).

In your jasper server you can set given IC type as mutli-select query and add your query. 

IN your SQL in where clause: 
AND $X{IN,YourDBColumn,  yourInputControlName} 


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...