Using set/collection values in sql query

[les_mills]

I set up a control on a report in Jasper Server and that returns a set of values.

 

The closest that you can use in iReport for parameters is a Collection. I then need to use those values in the parameter in the sql query.

 

So, the example is:

 

Jasper Reports gives a list of customer types. - this is stored as a set

Passed to iReport and I try to save it as a collection

 

Passing it to query as:

 

select * from customer where cust_typeid in ($P{Customer_Type})

 

This produces the compilation error:

 

it.businesslogic.ireport.gui.logpane.ProblemItem@efb588 Parameter type not supported in query : Customer_Type class java.util.Collection /jasperReport/queryString[1]

 

How do I access the values from the set supplied by Jasper Server, in iReport when I only have a collection as a parameter type, and how to get those values out of the collection for the sql query.

 

Cheers,

 

Nick

[n0n00b]

Try this:

 

select * from customer where $X{IN,cust_typeid,Customer_Type}

[les_mills]

thanks n0n00b

 

this worked perfectly.

 

Is $X a different type?

 

All I have found in the iReport and JasperReports manuals are $F, $V and $P.

 

Do you know anywhere that has more information about this type?

 

Cheers

[mdahlman]

Nick,

 

Info about this feature was placed here when it was first coded. Tracker.

 

You'll find other info if you search the forum.

 

Regards,

Matt

[itzbobi]

hi i am bobby,

 

i am in similar situation. But my problem is with sql query in .java file.

 

st.executeQuery("Select * from <table> where <col_name> IN '+<collection obj>+' ";

 

error : right syntax near <collection obj>.

 

can any one help me please.

[Giulio Toffoli]

Bobby, java is not so smart to "explode" your collection and present it in the query with the right syntax.

If you don't want let JasperREports do the job for you using the special syntax

select * from <table> where $X{ IN, <col name>, collection param}

the solution is to manually build something like:

 

select * from <table> where <col name> IN ( ?, ?, ?, ?, ?, ?, ?, ?,)

with a question mark for each entry in your collection, and then set the values for each question mark to the statement.

 

Giulio

[itzbobi]

Thanks for a quick reply, Giulio. I am sending ( ?, ?, ?, ?, ?, ?, ?, ?,) parameters dynamically.