Example for Read querystring parameter using run variable and use it in sql query

Owned by Kinnera
Last updated: Sept 06, 2022
2 min read

This article helps to read the query string parameters using the pre-defined run variables and their value in the groovy script containing the SQL query to get the desired SQL output.

Instructions

Requires three macros from these three apps:

  1. Run Self-Service Reports for Confluence - Run with a user form and parameters macro.
  2. Scripting for Confluence - Groovy macro.
  3. SQL for Confluence - SQL query in wiki format, used in the groovy script.

Requirement:

Consider two pages, main and child. The main page consists of text with links. By clicking on the link, it redirects to the child page's text information. The link is a query string parameter, i.e., ?id=1, based on the key-value, the child page will render the groovy script and present the SQL output accordingly.

Main Page:

The query string parameter is of a label name. Click on it → 


Child Page:

While re-directing to the child page, the $request_string variable will render the query string, i.e., ?Name=image from the browser URL. Then we have the groovy script to trim the indexes and get the value, i.e., image. This value then be used in the groovy script, which will then render and give the output as shown as below:

The Edit View:

The Groovy Script:

import com.atlassian.renderer.v2.RenderMode	
def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
def startIndex = "$request_string".indexOf("=")
def requestParam = "$request_string".substring(startIndex+1,"$request_string".length()) 

println("\n")
println("only Value:"+requestParam)

def var1 = "{sql-query:dataSource=Docker 06|table=true}  SELECT * From LABEL where name = '" + requestParam +"' {sql-query}"	
println(var1)


The Storage format:

<p class="auto-cursor-target">
  <br/>
</p>
<p>
  <br/>
</p>
<ac:structured-macro ac:macro-id="e759cb65-4758-4c1b-ac25-5b64f712c50e" ac:name="run" ac:schema-version="1">
  <ac:parameter ac:name="autoRun">true</ac:parameter>
  <ac:parameter ac:name="hideRun">true</ac:parameter>
  <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
  <ac:rich-text-body>
    <p class="auto-cursor-target">$request_string</p>
    <ac:structured-macro ac:macro-id="0e6513ac-d60c-4d11-8dc2-54d0e08b1411" ac:name="groovy" ac:schema-version="1">
      <ac:parameter ac:name="output">wiki</ac:parameter>
      <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
      <ac:plain-text-body><![CDATA[import com.atlassian.renderer.v2.RenderMode	
def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
def startIndex = "$request_string".indexOf("=")
def requestParam = "$request_string".substring(startIndex+1,"$request_string".length()) 

println("\n")
println("only Value:"+requestParam)

def var1 = "{sql-query:dataSource=Docker 06|table=true}  SELECT * From LABEL where name = '" + requestParam +"' {sql-query}"	
println(var1)]]></ac:plain-text-body>
    </ac:structured-macro>
    <p class="auto-cursor-target">
      <br/>
    </p>
  </ac:rich-text-body>
</ac:structured-macro>
<p>
  <br/>
</p>
<p class="auto-cursor-target">
  <br/>
</p>
<p class="auto-cursor-target">
  <br/>
</p>
<p class="enh-settings hidden">{}</p>
  • This works with all the latest versions