Usq sql within a user macro

[Marc Giesmann]

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!

[Bob Swift · Accepted solution]

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>

[Marc Giesmann]

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?

[Bob Swift]

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.

[Marc Giesmann]

Got it. Thanks!