Placeholders

also called parameter markers, are used to indicate values in an SQL query that will be supplied later, before the statement is executed.

There are four input types:

• Integer - A simple number
• Form - Form fields begin with "form:".
• Query results - Query results begin with "query1:" through "query4:". Query results are populated with data from prior queries. So when the second query is initiated, it can used the results returned by query1. When query 5 is initiated it can use the results from queries 1 through 4.
• String - Anything else is a string

Example:

Query: select * from some_table where some_field = ? and some_other_field < ?

Placeholder Parameters:
query1:pageId
form:field1
78
^macrothatreturnsastring;

In this example the first question mark will contain the field value of pageId in query1, while the second question mark will contain the form variable "field1". The third question mark will be a number, and the forth questionmark will be the string the macro returns, (and it will be propertly surrounded with quotes)

Place one Placeholder Parameter on each line.  Leading and trailing whitespace will be trimmed from each parameter.

Optional Parameters

Using the FormParam macro, you can optionally allow query parameters to be included.

Use the following in your Placeholders:

 ^If(^FormParam(g);,^FormParam(g);,"%");
^If(^FormParam(g);,^FormParam(g);,"%");

Then in your query use something like the following:

SELECT *
FROM table
WHERE
 field1 LIKE concat('%',?,'%') AND
 field2 LIKE concat('%',?,'%')

Replacing URL encoded values

If you need to use a form parameter that includes values that have been URL encoded, you can make use of the REPLACE function in MySQL. For example, to replace '%20' with actual spaces use the follow format:

REPLACE(?,'%20',' ')

Prequery statements

are sql statements executed before the real query. You can use prequery statements for instance to set variables that you want to use in the real query. For example:

set @myVariable := 1

The prequery statements are separated from each other by returns and cannot use placeholders. You can use macro's within the prequery statements, however. Please note that prequery statements are only visible in the query they belong to and that you can only use statements that are allowed by the database link (for example, 'set' is not allowed for the WebGUI database link).

Query

This is a standard SQL query. If you are unfamiliar with SQL then you'll likely not want to use this wobject.

A question mark ? in the query represents a placeholder. Note that the ? is not enclosed in quotation marks, even when the placeholder represents a string.

The keywords that are allowed are defined in the database link properties. The allowed keywords for the WebGUI database are SELECT, DESCRIBE and SHOW.

Macros

You can embed Macros in the query if you enable pre-process marcos in the SQLReport.

E.g. -

Query statement:

SELECT * from table where column = ^FormParam(column_name); 

Notes

• remember if you use more than one query, your custome template must account for each. (must have a for loop for each query)
• EVERY ? requires a placeholder. even if the same value is being used multiple times in the sql statement.
• if you are going to use hasNest in the report template, it needs to be called in a rows_loop, even if you don't want to print out anything in that top query.
• Because webgu's databse only allows  SELECT, DESCRIBE and SHOW, you can't use SET in the Prequery field.
• revisiondate in webgui's database is a bigint (Times in the database are stored in MySQL format, in UTC.), and can be converted using  FROM_UNIXTIME(assetData.revisionDate)
• urls stored in the database do not have forward slashes in front of them, you need to remember to add that.
• assetData.assetSize is stored in bytes. Divide by 1024 if you want kB.