Sometimes it’s useful to prompt the user for data before launching a query. Sometimes you want a search field and let the user lookup for information. Play SQL 1.2.0 introduces User Parameters.
How to configure your macro for a User Parameter
To proceed, insert the macro on a Confluence page. The configuration screen will open:
The dialog displays with the SQL field alone by default. To add an option, click the drop-down “More Options”. In our case, select User Parameter. A line with three items will appear:
- Replacement Parameter: The exact string you want to replace in your SQL query. It must start with the dollar sign “$”.
- Label: The label of the question for the user.
- Default Value: The field will be pre-filled with this value.
After deciding of the name of your parameter, e.g. “$search”, write your SQL query:
SELECT category category, short_desc description FROM pg_settings WHERE UPPER(short_desc) LIKE UPPER( '$search' )
Save your macro and your page. In view mode, there will be a field above the table:
The user can enter a value and submit the query.
One of the main challenges in SQL is how to prevent the user from entering data which would break the query. If the user enters a quote, says “planner’s estimate”, you will want to escape the character in the query.
Play SQL escapes strings by default. That means, it will replace ‘$search’ with ‘planner”s estimate’, with a duplicate single quote, so that the SQL considers it as a simple string.
Inserting a litteral
In other cases, you want to insert the user’s SQL as-is. For example the user can enter a full WHERE condition:
SELECT * FROM CUSTOMERS WHERE $!userCondition
The name of the variable starts with an exclamation mark:
Using JDBC parameters
It is also possible to use JDBC parameters. The vendor’s driver is in charge of replacing the question mark with its value. In this case, all variable names are question marks:
All user data is inserted as strings: If you require a number, your query will have to transform it.
Making dynamic pages
Thanks to this feature, your pages can dynamically prompt the user for values and display related results.