Single Select Query

[hazamonzo]

I am testing out various report types using Irepotr nad JasperServer and i have come up against a problem with single select queries. I will take you through what i have done.

 

Using Ireport i have created and tested a report listing all my customers in a MySQL DB. The basic query is thus:

 

SELECT customerNumber, customerName

FROM dim_customers

WHERE customerNumber = $P{customerNumber}

 

I have added this parameter to the report too in the parameters section. The name of the parameter is customerNumber and is set to promt the user when the report is run through IReport. If i test this report in Ireport and specify a customerNumber the report complies and is displayed fine. First step done. The problem is when i want to use a single select query to specify the customerNumber in JasperServer.

 

I go to add a new report, name it ect and then add report control. I create a locally defined control - for the input control name i use "customerNumber" (without the brackets) and select a "Single select query" as a type. I select the mandatory and visible select boxes. I then select a locally defined query. Give the query a name. When i am promtde to select a query datasource i have so far tried both the "None" and "From the repository" options. I then type the query below:

 

SELECT customerNumber, customerName

FROM dim_customers

 

For the value column i use customerNumber and for the visible column(s) i use customerName. Last of all i save the control and finish off saving the whole report. When i goto run this report i expect to be promted with a pull down menu consisting of customerName(s) but alas all i get are errors.

 

I have posted the errors below. What might i be doing wrong? I have used the sample report "Employeeaccounts" as a reference for my report and i cant see and difference in what i am doing.

 

java.lang.NullPointerException

at com.jaspersoft.jasperserver.war.action.ReportParametersAction.executeQuery(ReportParametersAction.java:460)

at com.jaspersoft.jasperserver.war.action.ReportParametersAction.createWrappers(ReportParametersAction.java:409)

at com.jaspersoft.jasperserver.war.action.ReportParametersAction.createWrappers(ReportParametersAction.java:119)

at com.jaspersoft.jasperserver.war.action.ViewReportAction.checkForParams(ViewReportAction.java:121)

at sun.reflect.GeneratedMethodAccessor343.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.webflow.util.DispatchMethodInvoker.invoke(DispatchMethodInvoker.java:103)

at org.springframework.webflow.action.MultiAction.doExecute(MultiAction.java:136)

at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:203)

at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:142)

at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:61)

at org.springframework.webflow.engine.ActionState.doEnter(ActionState.java:180)

at org.springframework.webflow.engine.State.enter(State.java:200)

at org.springframework.webflow.engine.Flow.start(Flow.java:557)

at org.springframework.webflow.engine.impl.RequestControlContextImpl.start(RequestControlContextImpl.java:195)

at org.springframework.webflow.engine.SubflowState.doEnter(SubflowState.java:120)

at org.springframework.webflow.engine.State.enter(State.java:200)

at org.springframework.webflow.engine.Transition.execute(Transition.java:229)

at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:112)

at org.springframework.webflow.engine.Flow.onEvent(Flow.java:572)

at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:207)

at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:214)

at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:238)

at sun.reflect.GeneratedMethodAccessor173.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)

at org.acegisecurity.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)

at $Proxy11.resume(Unknown Source)

at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:115)

at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:170)

at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)

at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:45)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:820)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:755)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:396)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:360)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at com.jaspersoft.jasperserver.war.common.UploadMultipartFilter.doFilter(UploadMultipartFilter.java:86)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:264)

at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)

at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:110)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at com.jaspersoft.jasperserver.api.metadata.user.service.impl.MetadataAuthenticationProcessingFilter.doFilter(MetadataAuthenticationProcessingFilter.java:136)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:181)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:131)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:217)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at com.jaspersoft.jasperserver.war.UserPreferencesFilter.doFilter(UserPreferencesFilter.java:131)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:191)

at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:274)

at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:148)

at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:90)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at com.jaspersoft.jasperserver.war.util.CharacterEncodingFilter.doFilter(CharacterEncodingFilter.java:70)

at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:138)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)

at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)

at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)

at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)

at java.lang.Thread.run(Thread.java:595)

 

Any help is appreciated =)

 

Harris

[csbac]

Hi!

You write that you used "null" and locally defined data source for the datasource ... null, of course, will result in an error (probably an NPE) because the query cannot be executed. The same will probably happen if the locally defined datasource is not correct ... why not use the same datasource that you also use for the report itself?

 

From the stack trace, I'd guess the parameter information is read, then, the query is executed ... and failes dramatically. Probably, the datasource is not set, or at least not correctly set ... use the same one as for the report.

 

Hope this helps,

Sebastian

[hazamonzo]

Sebastian,

 

Thanks for the reply. For this report in question i have setup a datasource that i know for a fact works correctly. I use this datasource for other reports. Concerning the report that is having trouble, I am also using this datasource for the single select query. As far as i can see if the connection to the datasource works (and i know it does) and my single select query SQL is correct (the query has been tested in an external application so i know it too works) then i shouldnt have a problem. There is much more information ican post on this other that posted you the .jrxml file itself. Any help is really appreciated.

 

Best regards, Harris

[csbac]

Is it possible you have null entries in the query, especially for the key value? (customerNumber).

The line that crashes is

Code:

inputData.put(
  keyValue.toString(), 
  new Object[] {keyValue, columnValuesString});

The only reason for an NPE in this line would be keyValue being null ... which is probably one of the column values of customerNumber. Is it defined as primary key, i.e., not null and unique?

 

Sebastian

[hazamonzo]

Sebastian,

 

Thanks again for following up my post. You hit the nail on the head with that one! Because query a dimension in a data warehouse i do indeed have a null values on one of my records. This is according to Ralph Kimbals slowly changing dimensions.

 

I now at least have a pulldown menu and no errors. Granted there is nothing in my pull down menu for some reason but i am sure i can figure that out.

 

Thanks for all you help! Really appreciate it!

 

All the best, Harris