MS SQL Query to show data in database

Hi,

 

I have a custom field that imports data from another database. I need to find out what the data looks like in JIRA. Are 0 stored as Null or 0?

I have tried with out success to get the information out of JIRA which is on a MySQL DB.

Can someone share the query to achieve this?

Many Thanks

Kevin

 

3 answers

This widget could not be displayed.
Alexey Matveev Community Champion Dec 28, 2017

Hello,

Null's are not stored at all.

Yup.  If a custom field does not have a value, there is no row in customfieldvalue at all. 

It would be a very good idea to forget about using the database - the API will give you better answers, more easily.

This widget could not be displayed.

Hi Kevin,

I believe the answer to your question is going to be NULL.  Depending on what information you're looking for, here is the database schema for JIRA.

You can use it to create queries and determine how the database is layed out.  In addition, at the top of that article is a jar file depending on whether you're using JIRA 5,6 or 7 to generate schema information for the JIRA database.  Here is the one for JIRA 7: jira-schema-diagram-generator-plugin-1.1.0.jar.

Cheers,

Branden

Alexey Matveev Community Champion Dec 28, 2017

@Branden McElveen

I believe that null values for custom fields are not stored in the database otherwise the database would be much larger in size.

This widget could not be displayed.

Every issue has an entry in the JIRAISSUE table with its own numeric id.

Likewise, every custom field is represented as a row in the CUSTOMFIELD table with its id.

There's an intermediate table between both of those tables named CUSTOMFIELDVALUE, each of which rows contains one issue id, one custom field id, and the stored value:

Screenshot_20171228-213833.png

That said, NULL values simply do not have a row in the CUSTOMFIELDVALUE table.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Aug 06, 2018 in Jira Service Desk

A is for Activate: Share your top Jira Service Desk onboarding tips for new users!

Hi, everyone! Molly here from the Jira Service Desk Product Marketing Team :).  In the spirit of this month's  august-challenge, we're sourcing stories of Jira Service Desk activation fro...

586 views 25 15
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you