Hi,
I’m trying to configure Jira Database Values Plugin to get data from the Jira database –HSQL database-. I don’t want to pick data from an external database but from Jira native database, using an sql query to select Jira tables and fields.
When configuring -see 1) and 2)- the plugin, the query do not fetch any data and the only value provided to "Database Values Selection Field" customfield is “none”.
I think my query is correct, because it works –see 3) - when applying it directly to the Jira database, via the Jira documentation method:
https://confluence.atlassian.com/display/JIRA044/Running+SQL+commands+in+a+HSQL+database
For information : I use Jira 5.0 with an evaluation license, so the Jira HSQL built-in database.
1) The plugin configuration:
jira-database-values-plugin-10300.properties:
I have found the native database settings in the “dbconfig.xml” file:
2) The log file –from jira-db-values-plugin.log-:
The query don’t fetch any result. The log say “Table not found in statement [select id, stringvalue from customfieldvalue]” –see line 9-. Do I have something to add like “customfieldvalue.something” or “${_customfieldvalue}, .. ?
2012-06-27 15:29:37,071 http-8080-4 Setting linked fields: []
2012-06-27 15:29:37,087 http-8080-4 Setting jql queries: {}
2012-06-27 15:29:37,181 http-8080-4 Got 0 substitutions in map
2012-06-27 15:29:37,181 http-8080-4 sql query after substitutions: select id, stringvalue from customfieldvalue
2012-06-27 15:29:37,181 http-8080-4 Got 0 substitutions in map
2012-06-27 15:29:37,181 http-8080-4 sql query after substitutions: select id, stringvalue from customfieldvalue
2012-06-27 15:29:37,181 http-8080-4 No project cache for InternalDefaultProjectKey
2012-06-27 15:29:37,181 http-8080-4 Got 0 substitutions in map
2012-06-27 15:29:37,181 http-8080-4 sql query after substitutions: select id, stringvalue from customfieldvalue
2012-06-27 15:29:37,243 http-8080-4 SQL query: select id, stringvalue from customfieldvalue
2012-06-27 15:29:37,243 http-8080-4 Table not found in statement [select id, stringvalue from customfieldvalue]
java.sql.SQLException: Table not found in statement [select id, stringvalue from customfieldvalue]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
at org.deblauwe.jira.plugin.databasevalues.DatabaseRowCache.queryDatabase(DatabaseRowCache.java:256)
at org.deblauwe.jira.plugin.databasevalues.DatabaseRowCache.getDatabaseRowsFromDb(DatabaseRowCache.java:242)
at org.deblauwe.jira.plugin.databasevalues.DatabaseRowCache.getDatabaseRowsFromDb(DatabaseRowCache.java:234)
at org.deblauwe.jira.plugin.databasevalues.DatabaseRowCache.refreshCacheIfNeeded(DatabaseRowCache.java:114)
at org.deblauwe.jira.plugin.databasevalues.DatabaseValuesViewHelper.refreshCacheIfNeeded(DatabaseValuesViewHelper.java:206)
at org.deblauwe.jira.plugin.databasevalues.DatabaseValuesViewHelper.getHtmlForEdit(DatabaseValuesViewHelper.java:233)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.apache.velocity.util.introspection.UberspectImpl$VelMethodImpl.invoke(UberspectImpl.java:234)
at org.apache.velocity.runtime.parser.node.ASTMethod.execute(ASTMethod.java:203)
at org.apache.velocity.runtime.parser.node.ASTReference.execute(ASTReference.java:175)
at org.apache.velocity.runtime.parser.node.ASTReference.render(ASTReference.java:220)
at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:55)
at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:230)
at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:89)
at org.apache.velocity.runtime.parser.node.ASTBlock.render(ASTBlock.java:55)
at org.apache.velocity.runtime.parser.node.ASTIfStatement.render(ASTIfStatement.java:70)
at org.apache.velocity.runtime.parser.node.SimpleNode.render(SimpleNode.java:230)
at org.apache.velocity.Template.merge(Template.java:256)
at org.apache.velocity.app.VelocityEngine.mergeTemplate(VelocityEngine.java:422)
at com.atlassian.velocity.DefaultVelocityManager.getEncodedBody(DefaultVelocityManager.java:77)
at com.atlassian.velocity.DefaultVelocityManager.getEncodedBody(DefaultVelocityManager.java:61)
at com.atlassian.velocity.DefaultVelocityManager.getEncodedBody(DefaultVelocityManager.java:56)
at com.atlassian.jira.plugin.AbstractJiraModuleDescriptor.getHtml(AbstractJiraModuleDescriptor.java:109)
at com.atlassian.jira.plugin.customfield.CustomFieldTypeModuleDescriptorImpl.getEditHtml(CustomFieldTypeModuleDescriptorImpl.java:84)
at com.atlassian.jira.issue.fields.CustomFieldImpl.getEditHtml(CustomFieldImpl.java:897)
at com.atlassian.jira.issue.fields.CustomFieldImpl.getCreateHtml(CustomFieldImpl.java:883)
at com.atlassian.jira.issue.fields.screen.AbstractFieldScreenLayoutItem.getCreateHtml(AbstractFieldScreenLayoutItem.java:90)
at com.atlassian.jira.issue.fields.screen.FieldScreenRenderLayoutItemImpl.getCreateHtml(FieldScreenRenderLayoutItemImpl.java:50)
at com.atlassian.jira.quickedit.rest.api.field.QuickFieldsFactoryImpl.getCreateFields(QuickFieldsFactoryImpl.java:88)
at com.atlassian.jira.quickedit.action.QuickCreateIssue.doDefault(QuickCreateIssue.java:178)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at webwork.util.InjectionUtils$DefaultInjectionImpl.invoke(InjectionUtils.java:70)
at webwork.util.InjectionUtils.invoke(InjectionUtils.java:56)
at webwork.action.ActionSupport.invokeCommand(ActionSupport.java:433)
at webwork.action.ActionSupport.execute(ActionSupport.java:157)
at com.atlassian.jira.action.JiraActionSupport.execute(JiraActionSupport.java:82)
at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:39)
at webwork.interceptor.NestedInterceptorChain.proceed(NestedInterceptorChain.java:31)
at webwork.interceptor.ChainedInterceptor.intercept(ChainedInterceptor.java:16)
at webwork.interceptor.DefaultInterceptorChain.proceed(DefaultInterceptorChain.java:35)
at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:205)
at webwork.dispatcher.GenericDispatcher.executeAction(GenericDispatcher.java:143)
at com.atlassian.jira.web.dispatcher.JiraWebworkActionDispatcher.service(JiraWebworkActionDispatcher.java:152)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:78)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.HeaderSanitisingFilter.doFilter(HeaderSanitisingFilter.java:44)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
at com.atlassian.applinks.core.rest.context.ContextFilter.doFilter(ContextFilter.java:25)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.web.filters.accesslog.AccessLogFilter.executeRequest(AccessLogFilter.java:104)
at com.atlassian.jira.web.filters.accesslog.AccessLogFilter.doFilter(AccessLogFilter.java:88)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.security.xsrf.XsrfTokenAdditionRequestFilter.doFilter(XsrfTokenAdditionRequestFilter.java:54)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
at com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
at com.atlassian.jira.web.filters.SitemeshPageFilter.doFilter(SitemeshPageFilter.java:124)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.seraph.filter.SecurityFilter.doFilter(SecurityFilter.java:211)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.security.auth.trustedapps.filter.TrustedApplicationsFilter.doFilter(TrustedApplicationsFilter.java:98)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.seraph.filter.BaseLoginFilter.doFilter(BaseLoginFilter.java:157)
at com.atlassian.jira.web.filters.JiraLoginFilter.doFilter(JiraLoginFilter.java:70)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
at com.atlassian.oauth.serviceprovider.internal.servlet.OAuthFilter.doFilter(OAuthFilter.java:71)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:99)
at com.atlassian.jira.web.filters.JIRAProfilingFilter.doFilter(JIRAProfilingFilter.java:19)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:71)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.tuckey.web.filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:350)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.gzipfilter.GzipFilter.doFilterInternal(GzipFilter.java:81)
at com.atlassian.gzipfilter.GzipFilter.doFilter(GzipFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
at com.sysbliss.jira.plugins.workflow.servlet.JWDSendRedirectFilter.doFilter(JWDSendRedirectFilter.java:25)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:78)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.cache.AbstractCachingFilter.doFilter(AbstractCachingFilter.java:33)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.encoding.AbstractEncodingFilter.doFilter(AbstractEncodingFilter.java:41)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at com.atlassian.jira.web.filters.PathMatchingEncodingFilter.doFilter(PathMatchingEncodingFilter.java:49)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.web.monitor.ActiveRequestsFilter$PassToChainFilterFunc.doFilter(ActiveRequestsFilter.java:346)
at com.atlassian.jira.web.monitor.ActiveRequestsFilter$DebugLogFilterFunc.doFilter(ActiveRequestsFilter.java:463)
at com.atlassian.jira.web.monitor.ActiveRequestsFilter.doFilter(ActiveRequestsFilter.java:173)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.startup.JiraStartupChecklistFilter.doFilter(JiraStartupChecklistFilter.java:75)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.multitenant.servlet.MultiTenantServletFilter.doFilter(MultiTenantServletFilter.java:91)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.jira.web.filters.steps.ChainedFilterStepRunner.doFilter(ChainedFilterStepRunner.java:78)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:554)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:864)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1665)
at java.lang.Thread.run(Thread.java:662)
2012-06-27 15:29:37,243 http-8080-4 Got 0 substitutions in map
2012-06-27 15:29:37,243 http-8080-4 sql query after substitutions: select id, stringvalue from customfieldvalue
2012-06-27 15:29:37,243 http-8080-4 Got 0 substitutions in map
2012-06-27 15:29:37,243 http-8080-4 sql query after substitutions: select id, stringvalue from customfieldvalue
3) Query test
The database settings are the same as in "jira-database-values-plugin-10300.properties" file -see 1)-:
The database query is the same as in "jira-database-values-plugin-10300.properties" file -see 1)-:
Does someone know where is my mistake ? 
Thanks for your help,
Benjamin
