Using Parameters in SQL Query

book

Article ID: 223555

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

How to pass the parameters in a SQL Query.

Environment

Release : 15.8.1

Component :Jaspersoft

Resolution

You can use parameters in SQL queries to filter records in a where condition or to add/replace pieces of raw SQL or even to pass the entire SQL string to execute.

In the first case the parameters act as standard SQL parameters. For example:
SELECT * FROM ORDERS WHERE ORDER_ID = $P{my_order_id}
In this example the my_order_id parameter contains the ID of the order to be read. This parameter can be passed to the report from the application running it to select only a specific order. Please note that the parameter here is a valid SQL parameter, meaning that the query can be executed using a prepared statement like:


SELECT * FROM ORDERS WHERE ORDER_ID = ?
and the value of the parameter my_order_id is then passed to the statement.


In this query:
SELECT * FROM ORDERS ORDER BY $P!{my_order_field}

my_order_field cannot be treated as an SQL parameter. JasperReports considers this parameter a placeholder
(note the special syntax $P!{}) is replaced with the text value of the parameter.
Using the same logic, a query can be fully passed using a parameter. The query string would look like:
$P!{my_query}


A query can contain any number of parameters. When passing a value using the $P!{} syntax, the value of the parameter is taken as is, the user is responsible of the correctness of the passed value (SQL escaping is not performed by JasperReports in this case). When using a parameter in a query, a default value must be set for the parameter to allow Jaspersoft Studio to execute the query to retrieve the available fields.