Usq sql within a user macro

Hi!

I am trying to call the sql macro within another macro, but I don't understand how to prevent confluence from escaping the characters.

 

## Macro title: My Macro
## Macro has a body: N
## Body processing: Selected body processing option
## Output: Selected output option
##
## Developed by: My Name
## Date created: dd/mm/yyyy
## Installed by: My Name
## This is an example macro
## @param sqlsearch:title=Name|type=string|required=true|desc=SQL Searchterm
<ac:structured-macro ac:name="sql">
    <ac:parameter ac:name="dataSource">dbname</ac:parameter>
    <ac:parameter ac:name="output">wiki</ac:parameter>
    <ac:rich-text-body>
    SELECT DISTINCT id AS 'Location ID', address AS 'Adress Full' FROM Address WHERE street like '%strasse%' LIMIT 10;
    </ac:rich-text-body>
</ac:structured-macro>

 

But confluence insists on escaping several characters:

Fehler beim Rendern des Makros 'sql': 
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an 
error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 
''Location ID', address AS 'Adress 
Full' FROM Address WHERE s' at line 1

 

Is there a better way to call the sql-statements from another macro?

 

Thanks in advance!

1 answer

1 accepted

You need to use CDATA. Best to go to a page and construct your macro(s) there and then copy the XHTML source. For instance:

<ac:structured-macro ac:macro-id="f9f4ee50-2e49-463a-8ab5-79cd170f1450" ac:name="sql-query" ac:schema-version="1">
    <ac:parameter ac:name="dataSource">testDS</ac:parameter>
    <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
    <ac:plain-text-body><![CDATA[select 'xxx']]></ac:plain-text-body>
  </ac:structured-macro>

Thank you!

 

Incredibly obvious, but didn't think of CDATA before. Just to clarify: Why did you change the output type from wiki to INLINE? My SQL statement adds columns via wiki-markup. Is there a better practice?

That is NOT the output parameter for the macro (it is an Atlassian setting). It can be omitted, I just copied the XHTML from a sample page.

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Kesha Thillainayagam
Posted Friday in Confluence

We want to hear how your non-technical teams are using Confluence!

Hi Community! Kesha (kay-sha) from the Confluence marketing team here! Can you share stories with us on how your non-technical (think Marketing, Sales, HR, legal, etc.) teams are using Confluen...

280 views 11 10
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