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

[SKR]

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

[Bob Swift]

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

[SKR]

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)

[SKR]

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)

[Bob Swift]

What is the issue?

[SKR]

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

[Bob Swift]

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

[SKR]

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.

[Bob Swift]

What is your question?