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

This widget could not be displayed.

What is your question?

This widget could not be displayed.

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.

This widget could not be displayed.

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

This widget could not be displayed.

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

This widget could not be displayed.

What is the issue?

This widget could not be displayed.

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)

This widget could not be displayed.

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
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted Wednesday in New to Jira

Are you planning to trial, or are currently trialling Jira Software? - We want to talk to you!

Hello! I'm Rayen, a product manager at Atlassian. My team and I are working hard to improve the trial experience for Jira Software Cloud. We are interested in   talking to 20 people planning t...

270 views 5 0
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