Power Database Fields FAQ

Owned by No Reply
Last updated: Sept 30, 2021 by Jonathan Muse

Contents

What should I do if I installed an incompatible version?

Power Database Fields PRO™ 4.0 is compatible with Jira 7.X.

If you have installed DBCF 3.0.x on Jira 7.x or DBCF 3.1.x / DBCF 4.0.x on Jira 6.x, perform the following steps:

  1. Uninstall SIL Engine™.
  2. Uninstall Power Database Fields PRO™.
  3. Install the version of Power Database Fields PRO™ that is compatible with your Jira.

  4. SIL Engine™ should get updated automatically and should now have the correct version as well.

    After you uninstall SIL Engine™, some plugins might remain disabled, so you might need to re-enable them manually.

I have a large DB with about 30K records and I experience performance issues

Power Database Fields PRO™ app was not designed for dealing with large amount of data. Sometimes, our users reported issues when more than ~10k records were retrieved by the sql queries. This doesn't mean that the app will not work with more than 10K records, it's just that you might experience certain issues with performance and data retrieval.

We plan to enhance the app to take care of such cases as well.

I have a huge list in my database and it takes some time to display options

Power Database Fields PRO™ runs an SQL query every time when a user tries to pull up a screen that contains one of the custom fields. The performance of the custom field cannot exceed the performance of the query when run directly from the database. So, if you have a query that takes 10 seconds to run when running from the database console it will take 10 seconds to run for the custom field. So for instance if you have 5 thousand users who are running a 10-second query every time they go to create a new issue or edit an issue this might have an impact on the performance of the database and increase the response time of Jira when it is running its own queries against the database. This would be true of any database plugin custom field, not just those developed by Cprime.

There are some design changes that could be used to mitigate the performance risks. For example, you could run the custom fields from a database cluster that is separate from the main Jira database. Also, using normalization on the tables and indexes targeted to the way the custom fields will be searching the tables will increase the performance of the custom fields.

Database custom fields don't work on Jira 7.X

After updating to Jira 7, some user experienced the following error (Error rendering 'databaseinput') when using SQL Server databases or the custom fields simply didn't work when using PostgreSQL databases.

 Step-by-step guide for SQL Server databases
  1. Download the latest jdbc driver for sql server (at this moment it is sqljdbc4.2)  
  2. Extract the archive and copy the .jar file (sqljdbc42.jar) to lib folder, from your JIRA

  3. Update the resource from context.xml accordingly. It should look like this:

    <Resource name="CONNECTION_NAME"
    auth="Container"
    type="javax.sql.DataSource"
    username="USERNAME"
    password="PASSWORD"
    driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
    url="jdbc:sqlserver://SERVER:PORT;DatabaseName=DATABASE_NAME"/>

  4. Restart Jira.

 Step-by-step guide for Postgres databases
  1. Download the latest jdbc driver for Postgres (at this moment it is postgresql-9.4-1204.jdbc4)  

  2. Copy the .jar file (postgresql-9.4-1204.jdbc4.jar) to lib folder, from your JIRA

  3. Restart Jira.

The same problem might occur for other databases as well (Oracle, MySQL, hsql). In this case, follow the same steps, updating the driver and restarting the Jira instance.

Starting with version 4.0.0, you no longer need to change the resource in the context.xml file and can add the resource directly from the UI, via the Datasources page.

I experience difficulties using special characters

When users of the Power Database Fields PRO™ use another language than English and it contains special characters, some translation might happen at the JDBC driver level. To avoid this, ensure that the translation between char codes doesn't happen.

 Step-by-step guide
  1. Go to the conf folder of your Jira and edit the context.xml.

  2. Modify the resource url which defines your data source like this: "useUnicode=yes" and "characterEncoding=UTF-8".

    url="jdbc:DRIVER://IP:PORT/DATABASE?useUnicode=yes&amp;characterEncoding=UTF-8"

  3. Restart your Jira.

How can I use a custom field value in the SQL query?

Most of Power Database Fields PRO™ users are usually using some custom fields in their queries to filter the data returned from the database. We're assuming that you have already created a database custom field at this point.

 Step-by-step guide
  1. Go to the Database Custom Field page.
  2. In the SQL query, usually in the WHERE clause, use the custom field id (e.g. customfield_12345) or the standard fields between braces. You can find the list of standard fields and more details about dynamic query on the Dynamic Query (SQL) page.

Note that this only works for single value Custom Fields. If you need to use multiple value CF, there is a recipe for it here: Using Database custom fields queries: IN clauses