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

This widget could not be displayed.

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 Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published yesterday in Confluence

Add-on evaluation with confluence templates

Atlassian market place contains number of Apps/Addons which improves the capability of out of the box Atlassian products. It is good to follow a plugin evaluation process before install add-ons. So t...

44 views 6 3
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