How to write SQL with dynamic parameter

[tinhlh]

Hi Every body!

Please help me to write a dynamic query. for example user want to report about cities, and if he input the city name 'London', the query is:

 

SELECT * FROM CITIES WHERE NAME ='London';;

 

But when he left the city name blank, the query must be:

 

SELECT * FROM CITIES;

 

thanks in advance!

[Sukumesh]

Hi,

 

Use if condition to Build the Query,

 

I mean (($F{NAME})==null?"SELECT * FROM CITIES;":"SELECT * FROM CITIES WHERE NAME ='London';

 

Regards

Suresh

[tinhlh]

Hi!

thanks to Sukumesh but can you example some code in .jrxml file? i'm trying your idea but I'm not sure about JRXML Syntax, and can you illustrate some code.

thanks again

TinhLH

[chetan_33]

Sukumesh

that Doesn't help completely

 

Check this query of mine

http://www.jasperforge.org/index.php?option=com_joomlaboard&func=view&id=626&catid=8#626

[xsabatea]

Hi!

 

for this purpose I use a sort of “sql-injectionâ€. It works for me because the used parameter is generated by an application, not by a user.

 

Your query:

Select * from countries where city=’London’ and …

 

My stuff:

 

Select * from countries where ( city=$P!{CITY} ) and …

 

CITY is a string param.

 

When you want to filter by city, CITY = “a city nameâ€

When you want to skip the filter CITY = “ ‘anything’ or 1=1 “

 

Maybe there’s a better solution but this works… ;)

 

Regards

 

Viersa

[sona123]

I am using below syntax for getting records when user will not enter anything in prompt input ,total information to be displayed

 

select*from mysql.responsetable where Response=$P{Please select response} or 1=1;

 

Please suggest me that would be great help

[maartenstachanov.com]

SELECT * FROM City WHERE name LIKE "%"; (is equivilent to SELECT * FROM City;)

SELECT * FROM City WHERE name LIKE "London";

[maartenstachanov.com]

shivasgk
Wrote:

I am using below syntax for getting records when user will not enter anything in prompt input ,total information to be displayed   select*from mysql.responsetable where Response=$P{Please select response} or 1=1;   Please suggest me that would be great help

You could replace "=" with "LIKE"  and have a default value for the $P{Please select response} = "%"

 

Disadvantage people can also write stuff like "Lon%" which would result in other cities that start with "Lon" aswell.

[sona123]

thank you for reply maartenalbers,I am new to jaspersoft my requirement is I need to display all records when user does not give any input in prompt.

 

Please suggest/advice would b appreciated.

[maartenstachanov.com]

set the default value for the Parameter to "%" (I thought I mentioned it...)

Change the "=" sign to LIKE and you get what I said.

[sona123]

sorry maartenalbers I am not able to get you

I changed parameter default value expression to $P{Please select response}="%"

then in report query i changed to select*from mysql.responsetable where Response Like $P{Please select response};

 

 

Please suggest/advice would b appreciated, am I doing anything wrong!!!!!!!! what you said to me........

 

 

 

[cbarlow3]

I hate all these "dynamic SQL" solutions.  I see them all the time, and they just look more like "SQL injection" to me.  I gather from this board that people use them all the time, but I avoid them like the plague.  Why not just have a straightforward query like this:

SELECT
  CITIES.NAME,  /* I also do not like * instead of explicit field names */
  CITIES.POPULATION,
  CITIES.REGION
FROM
  CITIES
WHERE
  $P{CITY} IS NULL OR
  CITIES.NAME=$P{CITY}

That way your user is only entering a city name, not SQL (worst possibility) or something that triggers one set of SQL or another to be executed.  There are several advantages.  Also, if you are deploying these with JasperServer, and you have a fairly finite list of cities in a table, you could define a query and input control that would let the user pick one (or more--depends what you want to allow them to do) cities from a dropdown list instead of typing them in.  You could, for example define a query called CityQuery that reads SELECT CITIES.NAME FROM CITIES ORDER BY CITIES.NAME (this assumes that city names alone are unique enough to select from...you really need to also grab state name or some such solution).  You could then define an input control called CityList that is either a single select from query or multi select from query and points to CityQuery.  Your main query could then have a LIST parameter called $P{CityList} (instead of a String parameter called $P{CITY}) and your main query looks like this now:

SELECT
  CITIES.NAME,
  CITIES.POPULATION,
  CITIES.REGION
FROM
  CITIES
WHERE
  $X{ IN, CITIES.NAME, CityList }

That last part isn't a typo: in the $X{} syntax, it's understood that the third argument is a parameter name, so you don't bother surrounding it with $P{}.  One really nice feature of using the list input control is that it automatically seems to behave the way you've indicated you want it to behave:  where if you don't pick ANYTHING, it's the same as picking EVERYTHING.  You would actually have to code it differently to make nothing mean nothing, which is pretty convenient most of the time, since in most cases selecting nothing doesn't really make a lot of sense, does it?

Another great thing about this query/list combo is that if you think it might be a pretty common need, you can define folders in the repository where you put useful queries and useful input controls and then just LINK to the input control instead of building it from scratch each time.  I use this technique with almost every report I write (although not for city selection--I'm in financial software and use it to let the user filter a report by share type, loan type, branch, etc.).  My data source is defined within the "organization", so any queries like this have to be in a folder that is within the organization (not in "Public"), and any input controls that use such a query also need to be in a folder in the organiation. I also have a few single or multi-select input controls defined in Public, but instead of using a query, they use a hardcoded list of values (also defined in a Public folder) that don't depend on the database information.

I hope at least some of these ideas prove useful to you.

Carl