^SQL();
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.

Parameters

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!)

Examples:

^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/>");

Output:

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.