Username Password
Bookmark and Share

SQL Macro

A one-line SQL report that can be used on the WebGUI database or any database with which you have established a database link. Sometimes you just need to pull something back from the database quickly. This macro is also useful in extending the SQL Report wobject. It uses the numeric macros (^0; ^1; ^2; etc) to position data and can also use the ^^rownum; macro just like the SQL Report wobject.


  1. SQL statement to be executed
  2. Format of output for query results
  3. Database Link ID
    (optional—default is WebGUI database link '0'; note that database link must be configured to allow macro access!)


^SQL("select count(*) from users","There are ^0; users on this system.");

^SQL("select userId,username from users order by username","<a href='^/;?op=viewProfile&uid=^0;'>^1;</a><br />");

If there are more numeric macros than selected columns in the SQL report the extra macros will be discarded.

The rownum macro, ^rownum;, begins counting at 1.

This Macro may be nested inside other Macros if the text returned does not contain commas or quotes.

Note: that macro is not enabled by default and should be manually enabled in  /data/WebGUI/etc/xxx.conf by adding  "SQL" : "SQL" line to "macros"  area.


Another Example:

^SQL("select p.fieldName, v.value from metaData_values v, metaData_properties p where p.fieldId = v.fieldId and v.assetId = '<tmpl_var assetId>' order by p.fieldName","<b>^0;</b> ^1;<br/>");


Author First Name Barbara
Author Last Name Nussbaum
Publication Date 03/16/06
Publication Type Merchants Of Vision


An example using a MySQL function to format a string:

Users enter a start time into the dataform fields hours and minutes. Sometimes, they enter the minutes without a leading 0, or just leave the field blank completely. To display the time "correctly" I used:

^SQL("SELECT LPAD(<tmpl_var minutes>,2,0) FROM DUAL","^0;");

This will pad any minutes entered with zeros so they always display with 2 digits.

LPAD is a standard MySQL function and DUAL is a psuedo-table that is part of all MySQL databases.

Keywords: database link dual macro sql

Search | Most Popular | Recent Changes | Wiki Home
© 2023 Plain Black Corporation | All Rights Reserved