Usq sql within a user macro


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>
    SELECT DISTINCT id AS 'Location ID', address AS 'Adress Full' FROM Address WHERE street like '%strasse%' LIMIT 10;


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>

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.

Got it. Thanks!

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Dec 18, 2018 in Confluence Cloud

Happy holidays from our team to yours!

Hi Community!  2018 was filled with changes for our team, both big and small, and we've taken a lot of time to both celebrate our wins and recognize areas of improvement. One thing that we're a...

473 views 3 18
Read article

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