It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Problem with executing a procedure through kepler database custom field

Hi.

We are tightening our database permissions for security reasons. Therefore we want to substitute all the selects in the kepler database fields to procedures.

However, when trying to execute a procedure inside a kepler custom fields we get no data and following error pops out in logs:

2014-04-10 12:40:52,809 ajp-bio-8009-exec-85 ERROR ******** 760x1549579x1 1ip0t0b ******** /secure/QuickEditIssue!default.jspa [databasecf.querydb.impl.QueryDatabaseImpl] Could not execute SQL query >>EXEC procedurename<< on database DATABASENAME.
java.sql.SQLException: Could not find stored procedure 'procedurename'.

When we execute the same procedure in management studio, using the same user that jira has defined in context.xml to connect to the database, we get all the data from the procedure.

When we use select in kepler, eveything works fine.

What may be wrong? Does Kepler supports exec sql command?

1 answer

1 accepted

1 vote
Answer accepted

Hi,

You cannot use directly the result of the stored procedure.

You have to use a temporary table in your sql query for the dbcf field:

CREATE TABLE #temp(COLUMN_NAME varchar(50)); INSERT INTO #temp EXEC procedurename; SELECT * FROM #temp; DROP table #temp;

You will put COLUMN_NAME as column for the dbcf field.

You can find more details on using temporary table in MS Sql Server here:

http://www.sqlteam.com/article/temporary-tables

If you have less than 100 rows in your table you can also use table variables, as described in the same link.

Hope this helps.

Thank you very much, we will try this asap!

PS.

Keep up the great work with all the Kepler plugins! They're awesome, I can't imagine Jira without them :)

----edit----

Temp table works like a charm :)

Temp table works like a charm :)

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted yesterday in Jira

Upcoming Enterprise releases targeting Q3 2019: Jira Software 8.5 & Jira Service Desk 4.5

Since launching the Jira Software 8.0 and Jira Service Desk 4.0 platform releases in February, many of you have been asking when the next Enterprise release will be so you can take advantage of the m...

284 views 3 7
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