Query column as a parameter

Hi everyone,

I would like to ask if there is a way to pass a column name that is to be selected within a query with a parameter. Something like:

SELECT :ColumnName FROM jiraissues...

I've tried that but seems that given the fact that the parameters are strings, they get passed as such and I end up with the string being returned, not the column value.

I need to be able to retrieve a specific column value for the user (letting the user decide which one serves him), so using just a simple JIRA Issues macro with one field only won't do, it returns a formated table with the title of the field and I just need the value.

Is it possible with the pocket query? Thanks a lot for any help.

2 answers

1 accepted

This widget could not be displayed.

Hi Marketa,

I just implemented a new feature in PocketQuery to make this work. Here you can find a release candidate version of PocketQuery that I want to ask you to test if it's working for your use case.

There is now a new parameter type "Constant". The parameters in the statement of this type will be preprocessed by PocketQuery before the prepared SQL statement is passed to the JDBC libraries. This enables substitution in the SELECT and FROM clauses.

Example Query:

SELECT :NameColumn, Population
FROM Country
WHERE Continent = :Continent

In the "Parameter Types" input I entered: NameColumn=Constant

Looking forward to your feedback! If this solves your issue, the new feature will become part of the next PocketQuery version very soon!

Regards, Felix

Hi Felix,

sorry for the delay, it's been some crazy time. It's fantastic! Works like a charm:) I don't know how to thank you. Problem resolved. I prepare all the stuff in a user macro, feed the final JQL to PocketQuery macro inside and get out one field of an issue given by issue key inserted by the user. Perfect. Thanks so much!

Thank you Marketa! I'll incorporate your change in PocketQuery 2.1 which will be released some time in February. If you haven't already - would you consider leaving a review for PocketQuery at the marketplace? Let us know if you have any further questions smile

Absolutely, already done and you deserve every star there is! Thank you so much for everything.

Thanks for the awesome review Marketa!!

This widget could not be displayed.

Hi Marketa,

This is currently only possible with workarounds in the template. The problem is that the replacements in the SQL query will only be processed for actual parameters after the FROM clause. This is a security mechanism of the underlying Spring SQL libraries.

But there might be workarounds for you. You could select all columns and render only columns of interested in your template code. You could add a "dummy" sql parameter in the query such that users have to enter a name for the column of interest. Does that sound like a good workaround for you? In that case I could also provide you an example.

Regards, Felix (Scandio)

Hi Felix,

 

I've thought already about this thing but in my case it is not a good idea, if even doable. The user basically wants to retrieve only one field (one row x one column) and I never know which one, so including all of the basic fields plus all of the custom fields as well (talking JIRA issue fields), I would end up with like 100 colums from which only 1 is needed. Immagine 20 queries like that on 1 page, which can happen, and it will be heavy lime a tanker.

Thanks for the feedback! I will dig into this issue and search for a better workaround during the next days.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Aug 22, 2018 in Marketplace Apps

How a Marketplace app tech team is achieving gender diversity

Hello! My name is Genevieve Blanch, and I'm the Marketing Manager at RefinedWiki, creators of apps to give teams the tools to customize Atlassian platforms. Currently, 44% of the tech team at Re...

464 views 3 17
Read article

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