Transform SQL into JQL?

[Lukas Behncke]

Hello everyone,

I wanted to create a advanced search but at the end I fail everytime at the JQL. 

Therefore I found a free Plug-In (https://marketplace.atlassian.com/apps/1213501/sql-jql-driver-transform-jql-into-sql?hosting=server&tab=support) but i'm not sure if this one can transform from SQL to JQL. 

Or is there any other option to do this?

Thanks and have a nice day!

Lukas

[Nic Brough -Adaptavist-]

JQL and SQL are incompatible, not the same thing at all, so there is no "convert".  Plus, using SQL to read a Jira database is a bloody nightmare, and is the worst possible way to  report on Jira, you really should forget it and use Jira properly.

Could you explain where you are stuck on the advanced search?

[Lukas Behncke]

Ah okay thanks for this information.

We use JIRA and JIRA-SD in our company.

And we should now report those JIRA-SD's that had a longer resolution time than 30 Days....


Maybe you have an idea :'D

BR
Lukas

[Pablo Beltran]

Please, Nic, according to the Marketplace guidelines you should identify yourself as an employee of a company that is a direct competitor of our apps. Thanks in advance

[Nic Brough -Adaptavist-]

You first.

I had not noticed the changes to Atlassian ID had removed the identification.  Frankly, I don't care, I identify myself in the text as an Adaptavist any time I think I might mention the company products, I err on the side of caution, and I never post blatant (and useless) adverts, unlike some.

[Muhammad Ramzan_Atlassian Certified Master_]

Right now there is no way to convert SQL into JQL , the above plugin you mentioned , i used it before its not about converting SQL to JQL, what they are doing is, for each session they are creating local H2 db , where you can run you queries as SQL queries but its not the actual database of the jira.

[Lukas Behncke]

Hey Muhammad,

thanks for the answer. 
So it could be used as a "workaround" for the moment?

Or do you have another idea how I could solve this Problem?

BR

Lukas

[Pablo Beltran]

Hi Muhammad,

some clarifications to your comments (from the author's app :-)

its not about converting SQL to JQL

it can transform SQL into JQL too.

or each session they are creating local H2 db

It's an in-memory instance shared by all the users (multitenant architecture). Therefore it is not local either.

you can run you queries as SQL queries but its not the actual database of the jira

It's ok.

[Muhammad Ramzan_Atlassian Certified Master_]

Can you please explain  how? I ised this plugin but its totally different.

What my understanding is 

You are creating  a local db which h2 ,here someone can write queries but as per the questions context, someone cannot run a sql query as jql or jql query on sql server

[Muhammad Ramzan_Atlassian Certified Master_]

If really its working as you explained then its really usefull but plesse correct me if i am wrong

[Pablo Beltran]

Converting a SQL into a SQL is quite straightforward as mentioned below:

https://kintosoft.atlassian.net/wiki/x/yoAU

SQL+JQL is a H2 in-memory database which uses the H2 API to plug tables.

All the tables are empty (no data) and when a user runs a query against any of them, the H2 API invokes the Jira Java API to populate the table with the records, so it looks like a physical table.

With the H2 API is possible using a multitenant architecture: the same table shared by all the users, but each user reading their data isolated from the rest of the users. 

There is a physical H2 instance (local) that is used under some circumstances for caching purposes. Perhaps this brought some confusion if you saw it.... but the main and unique instance is in-memory.