Help understanding SQL Plugin syntax within macro

Jim Lancaster June 28, 2012

I apologize in advance for the inane question, but I have been unable to figure this out on my own. I know it has to be something simple, but I am stuck. Any help would be greatly appreciated.

We have a space called "advancedfixtures". If I create a blank page and insert the following Wiki markup in it, the results are correctly displayed as a table:

{sql:datasource=Vipre}select * from kaseya_combined where groupname like '%advancedfixtures%' {sql}

However, if I try to move this code to a macro and insert the macro in a Wiki page, I get nothing back:

## @noparams
{sql:datasource=Vipre}
select * from kaseya_combined where groupname like '%advancedfixtures%' 
{sql}

Ideally, I would like to create a generic macro that can be used in all spaces, where the spacekey gets inserted into the SQL query:

## @noparams
#set($spacekey= $paramspacekey)
## Check for valid space key, otherwise use current
#if (!$spacekey)  
   #set ($spacekey=$space.key)
#end
{sql:datasource=Vipre}
select * from kaseya_combined where groupname like '%$spacekey%' 
{sql}

Thanks in advance,

Jim Lancaster
Dallas, TX

3 answers

0 votes
Jim Lancaster June 29, 2012

Good suggestion, but that had no effect.

0 votes
Bob Swift OSS (Bob Swift Atlassian Apps)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 29, 2012

In defining your user macro, choose

  1. no macro body
  2. Output format: wiki markup

If you have this, then put showSql=true in the macro to see the sql that is actually being used for the query.

Jim Lancaster June 29, 2012

I thought a screen shot of the user macro definition might be helpful:

Jim Lancaster June 29, 2012

No change. The wiki markup works, the macro doesn't. In the first screenshot below, note that the macro (JDL SQL Test) is inserted above the Wiki markup. In the next screenshot, you can see the macro gets interpretted as a text string, and the Wiki markup generates the correct table.

Jim Lancaster June 29, 2012

Nothing changed. To my untrained eye, it appears that the macro code that does not begin with '#' is being interpreted or converted to text. Could the problem be related to calling the sql macro from within my 'jdl-sql-test' macro?

Here is the current macro code:

## @noparams
#set($spacekey= $paramspacekey)
## Check for valid space key, otherwise use current
#if (!$spacekey)  
   #set ($spacekey=$space.key)
#end
{sql:datasource=Vipre|output=wiki|showSql=true}
select * from kaseya_combined where groupName like '%advancedfixtures%'
{sql}

Bob Swift OSS (Bob Swift Atlassian Apps)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 29, 2012

The key is in your macro definition use: Output format: wiki markup

Jim Lancaster July 1, 2012

Am I using the wrong syntax for output format? I have "output=wiki" in both the screenshot above and the code example.

Jim

Bob Swift OSS (Bob Swift Atlassian Apps)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 1, 2012

I am talking about your user macro definition.

Jim Lancaster July 1, 2012

How should I modify the 9 lines of macro code above? I can't find anything in the documentation--not even some example code.

Can you point me to something/anything where I could at least see a list of the possible commands and their use? I feel like I'm banging my head on a wall. And I hate bothering people with stupid questions I could answer for myself if I had access to documentation and/or example code.

Jim Lancaster July 1, 2012

A-ha, I think I've found a possible reason for our inability to communicate. The link below contains a discussion about a similar issue. In version 3.5, there is a drop down that allows you to specify the output format: HTML or Wiki. This drop down does not appear in version 4.2.

http://community.customware.net/customware/topics/i_cannot_seem_to_be_able_to_use_set_data_from_within_a_user_macro_and_have_it_work_on

Bob Swift OSS (Bob Swift Atlassian Apps)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
July 1, 2012

Ok, so that is why we are having trouble communicating. Yes, that is exactly what I was talking about. For 4.x, Atlassian seems to have removed that capability for new macros, although it is still there for existing macros. I have no idea why it was removed. I could not find documentation (https://confluence.atlassian.com/display/DOC/Writing+User+Macros) on how to accomplish the same. You probably need to render it. I would suggest asking Atlassian to respond to this question (use poke Atlassian).

0 votes
Matthew J. Horn
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
June 29, 2012

Does the macro work if you take out the "like" clause? In other words, can we rule out the '%xxx%' syntax as the source of the issue?

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events