Loading Profile...
Scaffolding and DB Queries
Has anyone successfully used the {sql} plugin to populate Scaffolding tags with data? For example, (and this does not work),
{list-data:dynamicList}
{sql}
select concat('{list-option}', foo, '{list-option}') FROM bar WHERE...
{sql}
{list-data}
basically i'm just trying to stick some query results into a scaffolding call.
thanks a lot.
{list-data:dynamicList}
{sql}
select concat('{list-option}', foo, '{list-option}') FROM bar WHERE...
{sql}
{list-data}
basically i'm just trying to stick some query results into a scaffolding call.
thanks a lot.
I’m hopeful
1
person has this question
I have this question, too!
Tell me when someone answers.
The more people who ask this question, the more it gets noticed.
The more people who ask this question, the more it gets noticed.
The company marked this question as answered.
-
Inappropriate?I think the simplest way to pull this off would be to pipe it though the Replace and Render plugin. Eg:
{replace-and-render}
{replace-item:%items%}
{sql}
select concat('{list-option}', foo, '{list-option}') FROM bar WHERE...
{sql}
{replace-item}
{replace-body}
{list-data:dynamicList}
%items%
{list-data}
{replace-body}
{replace-and-render}
1 person says
this answers the question
1 Comment Add a comment
This answers the question
-
{replace-and-render}, good stuff. works like a charm. one does need to set a '|table=false' parameter to the {sql} macro or you get a wacky IllegalGroupError. Thank you very much. -
Inappropriate?Hi everybody,
In the other way, is it possible to query the database to output scaffold data.
Is there a special table or view reserve to scaffold data?
select * from SCAFFOLDDATA where dynamicList .....
will I get a table with the field inside...
Not sur where and how the data are send after the save button because of the bandana store....
Thanks for help.
I’m silly
-
Inappropriate?Hi Michael,
More details on where the data is stored is here:
http://community.customware.net/custo...
Hope that helps!
-
Inappropriate?Can someone help me with this?
This is my code and it only returns:
instead of the expected drop down list.
Code:
{replace-and-render}
{replace-item:%items%}
{sql:dataSource=abm-au|table=false}
select '{list-option}' + rtrim(customertitle) + '{list-option}' from customers where status like 'a%'
{sql}
{replace-item}
{replace-body}
{list-data:dynamicList}
%items%
{list-data}
{replace-body}
{replace-and-render}
What am I doing wrong?
I can confirm the SQL code is working fine (SQL Server).
We're on Confluence 3.3
Thanks for your help!
Raf
I’m frustrated
-
Inappropriate?I think you have to concat the 'list-option' and value from table. otherwise, list-data wont know what to handle when you inject the replace-body.
something like:
select concat(concat('{list-option}', foo),'{list-option}') FROM ... (oralce exemple)
hope this helps.
Michael
I’m confident
-
Inappropriate?I think the 'concat' syntax would produce basically the same output as in his example. But definitely try it to see if it helps.
Otherwise, have you tried just running the {sql} macro outside the replace-and-render to see what it produces? I'm not sure where you've tested the SQL statement - in Confluence, or directly on the server. There could be a syntax issue that only occurs in the {sql} macro. -
Inappropriate?Thanks Michael and David for your replies.
Soon after my post, I got a one of those moments when eveything seems so clear ;-)
I managed to get this to work by doing the following:
1) create a user macro (called abm-au_customers) for the sql query:
[x] Macro has body
Macro generates wiki markup
{sql:dataSource=abm-au|table=false}
select '{list-option}' + rtrim(customertitle)+ ' ('+ rtrim(customercode)+ ')' + '{list-option}'
from customers
where status like 'a%'
order by customertitle
{sql}
2) create another user macro (called dropdown) for the replace-and-render code
[x] Macro has body
Macro generates wiki markup
{replace-and-render}
{replace-item:%items%}
{$param0}
{replace-item}
{replace-body}
{list-data:dynamicList}%items%{list-data}
{replace-body}
{replace-and-render}
Note: $param0 let me choose which data source to use for my drop down list (ie it could be any queries similar to the abm-au_customers above).
Usage:
{dropdown:abm-au_customers}
I hope this will else anyone trying to do the same and will save them some time (I certainly spend way more time than I should on this).
Cheers,
Raf
I’m super happy!
-
Inappropriate?sounds pretty cool. until now i was using a combination of sql-query, replace-and-render and run macro.
i should try yours...
here is an exemple with the 'three combination' :
{replace-and-render}{replace-item:%spacekey%}
{sql-query:datasource=CONFLUENCE|table=false} select concat(spacekey,'::') from spaces{sql-query}
{replace-item}
{replace-body}
{run:replace=report::?Select:select::%spacekey%}
{sql-query:dataSource=CONFLUENCE|table=true}
select sp.spacekey,sp.spacename, concat(per.permgroupname, per.permusername) as PermName, per.PERMTYPE
from SPACES sp, SPACEPERMISSIONS per
where sp.spaceid=per.spaceid and sp.spacekey like UPPER('%$report%')
order by sp.spacename
{sql-query}
{run}
{replace-body}
{replace-and-render}
i also spend some time on this!!!
Michael
I’m amused
EMPLOYEE
