How to increase MYSQL functions character lengths without changing validating SQL in Jasper
Prerequisites::
Jasper Server 5.5,IReport
Database:: Foodmart
Scenario : I have two Tables for Table 1 i need to pass Input Control value column and for Table2 i need to pass Input Control visible column
For Table 1 I have created a multiselect parameter(Customer Name) and passing the value column to query
For Table 2 I have created one more hidden cascading parameter(single select) based on above parameter and make it as mandatory(Customer Hidden)
Using hidden parameter we can pass as a single string to the Query using GROUP_CONTACT ,but here I have came across with a problem is maximum character length for GROUP_CONTACT is 1024 characters so I need to increase character length if we selected more than 1024 characters from Customer Name
First I have selected only 3 customers from Customer Name,so it showing 3 records
Now I am selecting all customers from Customer Name but Customer Hidden parameter is filtering data upto “Adams Philp” customer only
To overcome the above issue we need to increase character length by placing SET SESSION group_concat_max_len = <number>; in Query this will work only when we will change validate.sql=false in jasper server and again we need to restart jasper sever
But without changing the validate.sql we can handle for that kindly follow this blog
Pass session value in Datasource URL as follows
Here I am passing Session value as 4294967295 ,now it take length as 4294967295
SET SESSION group_concat_max_len = 4294967295;
Now Data is filtering till Last Customer “Larkins John”
Best Open Source Business Intelligence Software Helical Insight is Here
A Business Intelligence Framework
Thanks,
Satya Gopi,
BI Developer.