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

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 two macros from these three apps:

Run Self-Service Reports for Confluence - Run with a user form and parameters macro
Scripting for Confluence - Groovy macro
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. When clicked on, the link it will redirect to the child page's text information. The link is a querystring 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

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>