Jump to content
Changes to the Jaspersoft community edition download ×

Access is Denied using SQL 'LIKE' operator


dc137654

Recommended Posts

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?

Link to comment
Share on other sites

  • Replies 5
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...