Access is Denied using SQL 'LIKE' operator

[dc137654]

Hi,

 

I am having strange issues with JasperServer and my SQL queries.

 

Here is my situation:

I want to use an input control that allow users to select the Quarters of the Year i.e. Q107, Q207, etc. This selection will then be used to populate my query. The issue is that the Quarter is embedded within a field (Deal ID) so I will need to use the 'LIKE' operator. Here is my query:

 

select

a.deal_id as deal_id,

b.status_name as status_name,

a.su_code as su_code,

c.eu_name as eu_name,

d.nr_5 as net_revenue,

a.deal_init_date as deal_submit_date

from

dealstatus a,

nextstep b,

customerdata c,

financialanalysis d

where

a.status_id = b.sub_status_id and

a.deal_id = c.deal_id and

a.deal_id = d.deal_id and

a.status_id in (8, 16, 17, 18, 19, 21, 22) and

a.deal_id like $P{quarter}

 

order by deal_init_date

 

==================================

I can successfully run this when I create a Parameter Prompt from within iReport (I type in %Q107%), however, when I try deploy this report to JasperServer, I get this error:

 

org.springframework.webflow.engine.ActionExecutionException: Exception thrown executing [AnnotatedAction@2ebbbd targetAction = com.jaspersoft.jasperserver.war.action.ViewReportAction@90067, attributes = map[[empty]]] in state 'verifyData' of flow 'viewReportFlow' -- action execution attributes were 'map[[empty]]'; nested exception is org.acegisecurity.AccessDeniedException: Access is denied

 

On JasperServer, I created a list control with the values

NAME VALUE

Q107 %Q107%

Q207 %Q207%

etc.

 

When I remove the %, the report runs with no errors (though it obviously does not return any results)

 

Any ideas?

[swood]

I think you are running into a HTML encoding problem with the %s. Rather than using a code like '%Q207%' in the list, what you can do is have the code be 'Q207' and in the query in the JRXML do:

 

a.deal_id like '%$P{quarter}%'

 

 

Sherman

JasperSoft

[dc137654]

wow, what a quick response! :)

 

you are correct about the HTML encoding problem, however, your solution gives me the following error:

 

Error filling print... Error preparing statement for executing the report query : select a.deal_id as deal_id, b.status_name as status_name, a.su_code as su_code, c.eu_name as eu_name, d.nr_5 as net_revenue, a.deal_init_date as deal_submit_date from dealstatus a, nextstep b, customerdata c, financialanalysis d where a.status_id = b.sub_status_id and a.deal_id = c.deal_id and a.deal_id = d.deal_id and a.status_id in (8, 16, 17, 18, 19, 21, 22) and a.deal_id like '%?%' order by deal_init_date

 

which is the correct error since in a prepared statement, you must enter the ? as the entire value

 

i.e. "... a.deal_id like ?"

 

I fixed this by entering the HTML escape for % in the list control on JasperServer. For example, for Q307, I put %Q307% Not a really pretty solution but it worked for me.

 

Thanks for your advice sherman!

 

Derek

[dc137654]

Seems like this post is having issues with the escape characters too.:pinch: what I meant to say was:

 

For Q307, I put %Q307% and it worked

[lucianc]

Which JasperServer version and what browser are you using? We haven't managed to replicate this issue..

 

Regards,

Lucian

[dc137654]

Firefox 2.0.0.4 on ubuntu, JasperServer 1.2.1