Pass java.util.List as parameter to SQL server stored procedure

[yzhou]

I have post a relative common question in iReport quary design about 2 weeks ago, but havn't got any response.

 

Here is the problem. A multi-select (Or multi-dropdown) parameter is required in our report, the parameter then passed to a stored procedure written in MS/SQL server. In order to use the multi-select parameter, java.util.List or java.util.Collection is required, but the stored procedure is expecting a string with carma delimit in the string. Such as "'Office1', 'Office 2',...".

 

To summarize, how to convert java.util.List as parameter to a String. There is function $X(} to do this kind of casting, but that is only good in SQL.

 

I'd appreciate if someon can give any idea, or any response even if you have run into same issue and need to be solved.

 

Thanks,

 

YZ1

 

[cbarlow3]

Coincidentally, I was working on the opposite problem yesterday...I needed to let the user enter a comma-delimited list, when I would have preferred working with a List or Collection object.  Anyway, my first thought is that your stored procedure is doing more work than is necessary if it's trying to parse out data from a String to get it back into separate entries again...any chance you could either change your stored procedure or make another version of it that accepts the more complex object instead of the string?

I realized while working on my issue yesterday that (a) I don't know of any tool within JasperReports Server that can convert a string to a java.util.List or vice versa, and (b) I don't know of any way to use a java.util.List input control except in a $X{ IN, , } construct.  I eventually ended up having the user enter the comma separated list of values (numbers, not preset values or result of a query from the database, so multi-select input control wouldn't work for me) and then had my SQL query "walk the string" (a technique described in chapter 6 of O'Reilly's "SQL Cookbook" to simulate loops for parsing strings, etc) to turn that single string into a result set of rows, each containing an individual value.

I wish I could tell you how to do the opposite, but again, I can't figure out any way for the SQL query or anything else in iReport to do anything with a java.util.List input except the $X{}, which is admittedly very useful in its own way.

If anyone else knows anything else you can do with a java.util.List input control within iReport/Jasper Reports Server, I'm all ears.

Carl

[mattbates]

You could use StringUtils.join in an Expression to turn a List into a String (or StringUtils.split for the opposite).

 

But I can't see a good way to get this into your SQL. You might try using a dummy report just to change the list your users select into a String and pass it as a parameter into your real report embedded as a subreport.

[yzhou]

mattbates,

Thanks for your idea of using the dumy report. It works Here are steps in brief, and maybe useful to others. I think convert data type from parameter  to query type should be a very common practice. I still don't understand that why iReport don't let variable as parameter to pass into a query.

Thank you again.

Convert java.util.List as parameter data type to stored procedure data type.

 

1. Create a java.util.List parameter in main report.
2. Write a java Scriptlet class, and create a method to convert an ArrayList to a String. Such as: convertArrayToChar(ArrayList in).
3. Create a jar for the Scriptlet class. Load the jar into iReport libs folder, and add it to classpath of iReport;
4. Reference the Scriptlet in report properties->Scriptlet class field. No .class extension needed. Ex.: “com.scriptlets.MyReprotScriptlet”.
5. Create a variable in main report. Define variable as java String type. In variable expression, call the scriptlet method to convert parameter (java.util.List). Example: $P{REPORT_SCRIPTLET}.convertArrayToChar((ArrayList)$P{LocationOIDs}).   Here $P{LocationOIDs is a List.
6. In the sub-reprot, create a parameter as String,
7. In main report, add the sub-repot and open sub-report properties. In parameter field, set the parameter in sub-report to reference the variable created in main report.

 

Post Edited by yzhou at 08/25/2011 18:48

[mashi02_02]

Hi yzhou,

I am very new to iReport and really need help on this issue. It seems like you have found a solution on this issue, possible to share with me in more details?

Is there other methods to convert the collection type into varchar using sql?

Thanks!

[mwarner_2]

I'm using Studio

Let's say your collection parameter is called Groups.  This is the what the user will see and chose from when making their selection(s).
Create a string parameter (Physically located down under the Groups parameter) called Groups_String, with this default value:
$P{Groups}.toString().replace("[", "").replace("]", "")
Uncheck "Is For Prompting".

In your dataset dialogue, where you might have had this ...@Groups = $P{Groups}..., change it to ...@Groups = $P{Groups_String}...

Now your SQL is getting a string.

[Dmclean]

Great article.....helped me a lot the last post