Page tree
Skip to end of metadata
Go to start of metadata

 

Posted on 11/01/2013 for the release of Play SQL 1.2.0

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.

User Parameters - Overview

The user can enter a search criteria.

How to configure your macro for a User Parameter

To proceed, insert the macro on a Confluence page. The configuration screen will open:

Add User Parameters to a query

Add User Parameters to a query

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:

User Parameter Prompt - Detail

The user can enter a value and submit the query.

Escaping

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:

Start your variable with “$!” to insert a litteral

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:

Put a question mark as a variable name to let JDBC replace the value

Put a question mark as a variable name to let JDBC replace the value

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.

Support information

  • HSQL (tick)
  • Postgres (tick)
  • Oracle (tick)
  • No labels