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

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>