Too many multi-select query selections (>128 characters) causes error

[mwarner_2]

I use Jaspersoft Studio Professional 6.1, and JasperReports Server 6.1. I don’t use iReports (though I have it).

I want to use a table in my report (so as to take advantage of the column sort, column filter, and column resize on-screen features that a table offers).

I also want to have a multi-select query input control, so that the users can select as many values as they want to use in the report.

Let’s say that my report will show data about selected cities.

I create a new report, with a Main Report sql query of ‘select null as test’. This query doesn’t do anything useful but at least establishes the database connection. I have an input parameter of City, which I have defined as being java.util.collection. I add a table to the report. I add a parameter of ‘Table_City’, which I defined as being java.util.collection. And I have a sql stored procedure of “EXEC get_table_data @city = $P!{Table_City}”. I am required to use this stored procedure. In the Table, Dataset, Parameters, I have created (or what I call ‘mapped’) Name: Table_City to Expression:$P{City}. I put the desired fields from the query into the table, and save the report. Then I ‘Publish Report to JasperReports Studio’.

In Server, I created a root, Public, Controls, input control called “City”, which I defined as being a multi-select Query. Let’s say the SQL query for this is “EXEC get_cities”. Back in the Server Repository, I have edited the report, Controls & Resources, Input Controls for “City” by ‘Select an Input Control from the repository’ and choosing ‘/public/Controls/City’.

Then I run the report and if I select one city, it works perfectly. If I select three cities, it works perfectly. If I select six cities, it works perfectly. But when I select all, or if I select too many cities, then I get an error message like: Caused by: java.sql.SQLException: [TIBCO][sqlServer JDBC Driver][sqlServer] The identifier that starts with ‘Atlanta, Alberta, Albuquerque, Alpharetta, Chicago, Memphis, Minneapolis, Saint Louis, Toronto, Paris, Hong Kong, Tokyo, San Fra’ is too long. Maximum length is 128.

So I have used the collection, $P! method. But it doesn’t work in a table when the multi-selections, when taken as a string, are more than 128 characters.

 

To try to get around this, I tried defining the parameters as java.lang.String, but there are two problems with this.

1. The multi-select query encloses the user’s selections with square brackets, like ‘[Atlanta]’. And when [Atlanta] is passed in the query, there is no matching [Atlanta] in the city database. To try to solve that, I had the ‘get_table_data’ stored procedure changed such that it would strip any square brackets in the passed-in parameter values. Perhaps this could be done in the XML java, but I don’t know how or where to do this.

2. I get an error message of Caused by: java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String. Maybe there is some way in the XML java to do this conversion between the ArrayList (of the multi-select query) and String parameter, but I don’t know how or where to do this.

 

Can you tell me what to do such that users can select more than 128 characters worth of multi-selections?

 

ps – When I do the multi-select query in the Main Report, and define the City parameter as a String, and I have the stored procedure strip out the square brackets, then I am not limited to 128 characters in my selected cities. I can select all cities and it works perfectly. Of course the problems with this is that I:

1. Don’t get the benefits of the table.

2. Have to have the stored procedure do the stripping of the square brackets.

3. Can’t show the user-selected cities in my footnotes page because I get an error of java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String. Normally on the summary section I would have a StaticText field of ‘Selected Cities: ‘ and then have a TextField with the expression of $P{City}

 

Thank you ahead of time!

[mwarner_2]

Some significant progress.  Maybe even a solution!

Remember the problem is that a multi-select query won't work when the user-made selections have a commulative tolal of more than 128 characters.   Like you can't select 10 cities.

 

I created a new parameter, called StringCity, of type java.lang.String. Is For Prompting is unchecked.  CityString has a default value expression is ${City}.toString().replace("[","").replace("]","").

This is doing two things.  toString() is converting City from a collection to a string.  replace is stripping out square brackets.  I don't have to do that stripping in my stored procedure (which I was doing above).

 

If you just want this multi-select to work in the Main Report, and don't need it in a table, then create the above string parameter and change the sql query

from EXEC get_table_data @city = $P!{City}

to     EXEC get_table_data @city = $P{StringCity}.

 

If you want this to work in a table, with an empty or other query in the Main Report, then create the above string parameter, and then in the table dataset, in Table, Dataset, Parameters map Name: Table_City to Expression $P{StringCity}. My sql query is EXEC get_table_data @city = $P{Table_City}.  Table_City is java.lang.String.

 

Here is one url that was a help to me (but I wasn't smart enough to understand it the first 4 times I read it):

http://alexandru-ersenie.com/tag/sending-collection-parameter-in-jasper-report/

[mwarner_2]

ps - I am finding out that the internal name of the input controls HAVE to match the name of your parameters.

For instance, if your parameter is named Select_City, then your input control can NOT be internally named City_Single_Select.

If they don't match (even though in Jasper Reports Server, in the Repository, for this report, you have editted the Controls & Resources, for Select_City to point at City_Single_Select), then the report ignores any selection you made (as if you left Select_City blank).

Maybe the documentation is full of warnings about this, but until you've made this error a few times, you won't learn it.