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 three 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 By clicking on , the link, it will redirect redirects to the child page's text information. The link is a querystring 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 VIewView:
The Groovy Script:
| Code Block | ||||
|---|---|---|---|---|
| ||||
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:
| Code Block |
|---|
<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>
|
| Info |
|---|
|