How to migrate groovy, WIKI Markup, RUN and sql?

Hi There,

I would like to have drop down list which contains user name, when it is selected and hit the submit button, it should display the details of selected user with editable cells and a update button.

Here is my code:

 

{groovy|output=wiki}

 

import com.atlassian.renderer.v2.RenderMode
def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA)
def userSql = "select ad_user,'::' from AC_users WHERE Deleted=0 AND ad_user<>'' ORDER BY ad_user ASC"
def userSqlMacro = "{sql-query:datasource=testDS|table=false} ${userSql} {sql-query}"
def user = subRenderer.render(userSqlMacro, context, renderMode)

def runMacro = """
{run:id=mic3|autoRun=true|replace=u1::?User:select::::${user}}
{wiki} 
{sql-query:datasource=testDS|output=wiki}
		select 
	  	+'{run:id=c' + CONVERT(varchar(10),ref)
		+ '|titleRun=Update|replace=g1::'+ad_user+':group, g2:'+CONVERT(varchar(10),ref)+':ref, p3:'+first_name+':first_name, g4:'+last_name+':last_name, g5:'+email+':email, g6:'+region+':region, g7:'+Dept+':Dept}'
		+     '{sql:dataSource=testDS|showSql=true}'
		+       'UPDATE AC_users set first_name ='+CHAR(39)+'$p3'+CHAR(39)+', last_name ='+CHAR(39)+'$g4'+CHAR(39)+', email ='+CHAR(39)+'$g5'+CHAR(39)+', region ='+CHAR(39)+'$g6'+CHAR(39)+', Dept ='+CHAR(39)+'$g7'+CHAR(39)+' where ref = ' + CONVERT(varchar(10),ref) 		
		+     '{sql}'
		+  '{run}'
		FROM AC_users WHERE ad_user = '$u1' ORDER BY initials
{sql-query}
{wiki}
{run}
"""
out.println(runMacro)

 

{groovy}

 

Out put should be some thing like below which I have made some progress already.

here I would like to have drop down before..

Capture.PNG

7 answers

What is your question?

I would like to say Thanks for your quick response first. Basically I will have a drop down list which contain user name, then will select a value from list and hit submit button, then based on the selected value it should display the result inside text fields which can be editable, once it is updated with latest info & hit the update button, then information should be saved in the database. Please do let me know if you need more info on this.

So, you have implemented this. Do you have a particular problem or something that doesn't work as expected?

I have implemented above interface in another section, now I would like to migrate the same with groovy along with dropdown. To create that interface I have used WIKI Markup, SQL and RUN plugin, but now this has to be merged with wiki markup and Groovy, I believe that it is causing the issue

What is the issue?

I'm unable to pass parameter from groovy to WIKI Markup (i.e selected value), unable to generate the interface that I have attached earlier and below error is occurring groovy: Error running script - Groovy runtime exception Error information groovy.lang.MissingPropertyException: No such property: p3 for class: DS_47480997_1417092209365 org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:50) org.codehaus.groovy.runtime.callsite.PogoGetPropertySite.getProperty(PogoGetPropertySite.java:49) org.codehaus.groovy.runtime.callsite.AbstractCallSite.callGroovyObjectGetProperty(AbstractCallSite.java:231) DS_47480997_1417092209365.run(DS_47480997_1417092209365:7) org.swift.confluence.script.GroovyMacro.evaluate(GroovyMacro.java:174)

You are using a groovy string (double quoted string) and therefore groovy is trying to evaluate $p3 as a groovy variable. To make it a literal, you need to escape the $ sign: \$p3

Thanks Bob, it's fixed up to some extent. However I'm facing issues while updating the record. When I update some thing and hit the update will update the parameter as it is, instead of changes I have made. Please find below my SQL query: UPDATE AC_users SET first_name = '$p3', last_name = '$p4', email = '$p5', Dept = '$p6', region = '$q1' WHERE ref=8 Code : import com.atlassian.renderer.v2.RenderMode def renderMode = RenderMode.suppress(RenderMode.F_FIRST_PARA) def choiceSql = "select ad_user,'::' from AC_users WHERE deleted = 0 AND ad_user is not null ORDER BY ad_user ASC" def choiceMacro = "{sql-query:datasource=testDS|table=false} ${choiceSql} {sql-query}" def choices = subRenderer.render(choiceMacro, context, renderMode) def runMacro = """ {run:id=dynamic|titleRun=Select|replace=g2::hidden value:Hidden description:hidden, s1::?User:select::${choices}} {sql-query:datasource=testDS|output=wiki|showSql=true} select '{run:id=c'+CONVERT(varchar(10),ref)+'|titleRun=Update|replace=g1::'+ad_user+':group, p3:'+first_name+':first_name, p4:'+last_name+':last_name, p5:'+email+':email, p6:'+Dept+':Dept, q1:'+region+':region}' + '{sql:dataSource=testDS|showSql=true}UPDATE AC_users SET first_name = '+CHAR(39)+'\$p3'+CHAR(39)+', last_name = '+CHAR(39)+'\$p4'+CHAR(39)+', email = '+CHAR(39)+'\$p5'+CHAR(39)+', Dept = '+CHAR(39)+'\$p6'+CHAR(39)+', region = '+CHAR(39)+'\$q1'+CHAR(39)+' WHERE ref='+CONVERT(varchar(10),ref)+'{sql}' FROM AC_users WHERE ad_user = '\$s1' {sql-query} {run} """ out.println(runMacro)

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Sep 18, 2018 in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

27,124 views 2 7
Join discussion

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