Jira Database Values Plugin, Is there a special syntax when doing a select in jira database? -eg.: jira.table, public.table-

[Benjamin Dekens]

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

[Nic Brough -Adaptavist- · Accepted solution]

There's nothing wrong with your SQL as far as I can tell. It looks to me like the database plugin is not able to read the correct tables.

I don't know if the plugin can use HSQL, or if it allows empty passwords, or if it's something else though

[Benjamin Dekens]

Yes I agree, the log said "Table not found in statement [...". I understood this plugin have been designed to fetch data from an external database to populate a jira custom field. But I am wondering if this plugin is able to fetch data from jira native database, and how..

Are you using this plugin to fetch data from jira to a customfield?

[Nic Brough -Adaptavist-]

Yes, but not with HSQL - it's pretty pointless testing against HSQL here because you do not want to use HSQL in live.

[Benjamin Dekens]

Yes it is pointless for a production system. But in a first step I want to be sure jira works for all the functionnalities I need to implement, and then I will switch to an external database. Because I thought the difference between HSQL native database and an external one is only at a fiability level but from what you say it also impacts functionnalities.

So I will switch to another database before to continu my tests! And I am glad to know that Jira Database Custom Field works with Jira queries.

Thanks for your help Nic

[Nic Brough -Adaptavist-]

It's not quite that - Jira is database agnostic and doesn't care what the database is. There is no SQL in Jira, it hands it all over to a database connector which can translate it from an application request into SQL appropriate for the database behind it. There's no impact on functionality in Jira - you can easily rip out HSQL and swap it for MySQL, Oracle, etc.

The database values plugin is not agnostic, it has to be given SQL for the specific database it's running against. That means that even if you managed to get it to work with HSQL, it might not actually work with whatever database you end up with.

This is about the only time I don't say "doing dev/test against HSQL is fine", because the database does matter in this case! If you were tinkering with almost anything else (reporting plugins, new custom fields, workflows, versions of Java, integration with other systems, etc) HSQL would be fine.

Note that because of this, you need to make a note in any support docs you've got to emphasise that the database DOES matter. Just in case your client decides to swap their core supported databases or an upgrade changes something,

[Benjamin Dekens]

Ok so:

• Jira doesn't care of the database type
• JDVPlugin cares about the database type

So clearly I need to know -and to install- the database type -Postgre, MySQL, Oracle, and so on- I will use before to continu any test for this plugin.

When you answered to my question in your second comment:

"Are you using this plugin to fetch data from jira to a customfield?

-Yes, but not with HSQL"

What is your database type?

You also said:

"The database values plugin is not agnostic, it has to be given SQL for the specific database it's running against"

My reflexion is the following:

1) I understand there is a special syntax depending on what database type you are using

2) but I assume Jira tables and fields remain the same -customfiledvalues, jiraissue, ..-, cause it depends on Jira, not on the used database

3) I used HSQL Database Manager to test my SQL queries in Jira database -see screenshot above, in my initial post-. How do you do to test your sql queries against Jira, is it the good way to proceed?

4) The only differences I see between two different database types, at the plugin level are in these 4 lines:

# The database connection parameters
database.driver=
database.user=
database.password=
database.connection.url=

But in fact there is also a specific query syntax.

5) Taking the example of your database type, do you have an example of a simple sql query against a Jira table, for example if I want to select the field "stringvalue" of the "customfieldvalue" table from Jira database. To see the syntax.

"Note that because of this, you need to make a note in any support docs you've got to emphasise that the database DOES matter. Just in case your client decides to swap their core supported databases or an upgrade changes something,"

Very good tip, I take note

[Nic Brough -Adaptavist-]

Database depends on the client's install (I have more than one system to look after). I'm on a mix of MySQL, PostGreSQL and Oracle. I tend to work in MySQL mostly though, as that's what's lying around on my Dev machine at home, and it's what my main client (who use the database plugin) are working with.

Yes, you're right about the language - it's all in the execution of the language, and to some extent, the server settings. Even case-sensitivity can bit you when moving between databases.

Spot about the table names as well - it really is the phrasing of the queries that changes, but if you look in any Jira database, you'll find the same tables, doing the same things, with the same data, irrespective of the type of database.

I don't use HSQL to test this stuff because it's not going to be that useful in the long run, and I've never bothered to try to get it to work because it's pointless when I've got a more-like-live database to hand already. So I can't help you there.

As for a MySQL example - "select stringvalue from customfieldvalue ;". You might find it's case sensitive, and some setups won't demand the ; on the end, but the core standard SQL is fine (as is your SQL in the screenshots you gave earlier)

[Benjamin Dekens]

Todo list : beware of the casesensitivity, the ';', the SQL syntax in general, and change to another database as soon as possible.

I will try with MySQL. Hope it will work! Thanks Nic

[Benjamin Dekens]

Feedback: I switched the native Jira HSQLDB to a MySQL database and the Jira Database Values Plugin is now working.

Thanks again Nic.

[Benjamin Dekens]

2012-07-03 15:30:02,419 http-8080-3 No project cache for InternalDefaultProjectKey
2012-07-03 15:30:02,419 http-8080-3 Got 0 substitutions in map
2012-07-03 15:30:02,419 http-8080-3 sql query after substitutions: select * from public.jiraissue where pkey like '${public.project.key}'
2012-07-03 15:30:02,450 http-8080-3 SQL query: select * from public.jiraissue where pkey like '${public.project.key}'
2012-07-03 15:30:02,450 http-8080-3 Table not found in statement [select * from public.jiraissue]
java.sql.SQLException: Table not found in statement [select * from public.jiraissue]
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)

Maybe my problem is trivial but i cannot resolve it.. Is there a special syntax when doing a select in jira database? Thanks

[Benjamin Dekens]

Using this query "select * from JIRA.jiraissue where pkey like '${jira.project.key}'" my project 'PMOT' is found but "JIRA" is an invalid schema name. The “dbconfig.xml” file -see screenshot above- swos that the schema name is "PUBLIC" but when trying the same syntax with "public" instead of "jira" the project is kind of bypassed.

Syntax with "jira":

2012-07-03 15:33:00,401 http-8080-2 Got 0 substitutions in map
2012-07-03 15:33:00,401 http-8080-2 sql query after substitutions: select * from JIRA.jiraissue where pkey like '${jira.project.key}'
2012-07-03 15:33:00,401 http-8080-2 Got 0 substitutions in map
2012-07-03 15:33:00,401 http-8080-2 sql query after substitutions: select * from JIRA.jiraissue where pkey like '${jira.project.key}'
2012-07-03 15:33:00,401 http-8080-2 No project cache for PMOT
2012-07-03 15:33:00,401 http-8080-2 Got 0 substitutions in map
2012-07-03 15:33:00,401 http-8080-2 sql query after substitutions: select * from JIRA.jiraissue where pkey like '${jira.project.key}'
2012-07-03 15:33:00,432 http-8080-2 SQL query: select * from JIRA.jiraissue where pkey like 'PMOT'
2012-07-03 15:33:00,432 http-8080-2 invalid schema name: JIRA in statement [select * from JIRA.jiraissue]
java.sql.SQLException: invalid schema name: JIRA in statement [select * from JIRA.jiraissue]

Syntax with "public":

2012-07-03 15:30:02,388 http-8080-3 Setting linked fields: []
2012-07-03 15:30:02,388 http-8080-3 Setting jql queries: {}
2012-07-03 15:30:02,419 http-8080-3 Got 0 substitutions in map
2012-07-03 15:30:02,419 http-8080-3 sql query after substitutions: select * from public.jiraissue where pkey like '${public.project.key}'
2012-07-03 15:30:02,419 http-8080-3 Got 0 substitutions in map
2012-07-03 15:30:02,419 http-8080-3 sql query after substitutions: select * from public.jiraissue where pkey like '${public.project.key}'

[Benjamin Dekens]

Step 1), I forgot to add "search.type=0" in the .properties file but it don't change the results. ("Table not found in statement" and no data in the customfield select list.

Best regards,

Benjamin.