Jump to content
Changes to the Jaspersoft community edition download ×

Using parameters with SQL IN clauses problem


Recommended Posts

By: Jim Piersol - jimbo-69

Using parameters with SQL IN clauses problem

2003-11-05 07:58

I am wanting to use an SQL query that has an IN clause in it. Something like:

 

select * from table1 where col1 IN ($P{parm1})

 

I then set the default value for $P{parm1} to something like "A, B"

 

This always returns an empty report, even though the SQL statement run manually works correctly.

 

If I change the defaultValue to include just one item, as in "A", then it will run the report.

 

Am I doing something wrong, or is this just a bug ?

 

Is there an easy way to get Jasper to show you the exact Query it will run AFTER parameter substitution ?

 

Thanks.

 

 

 

 

By: Fred Welland - fwelland

RE: Using parameters with SQL IN clauses prob

2003-11-05 08:54

I can't answer directly if the is a 'widget' in JR to snoop queries...But there are widget to do this at the JDBC level.

 

I have used p6spy (http://www.p6spy.com/) with JR to do someting similar....It also works with many different app servers too... Very usefull tool to trouble shoot prepared statements and the like.

 

-Fred

 

 

 

 

 

By: Jim Piersol - jimbo-69

RE: Using parameters with SQL IN clauses problem

2003-11-05 10:29

Using p6spy, I found I had to format the parameter value to something like: "A','B". This resulted in a correctly formatted SQL statement that will run perfectly manually,

 

select * from table1 where col1 IN ('A', 'B')

 

But Jasper will still not return any results and nothing except EmptyReport exception is written to STDOUT...

 

Any help would be appreciated.

 

 

 

 

By: Ryan Fruit - rfruit

RE: Using parameters with SQL IN clauses problem

2003-11-11 10:44

Hey jimbo,

 

The reason the query isn't working is because Jasper Reports is treating the $P{parm1} as an element of a prepared statement so when it substitutes in the parm1 the query it's really running is ... col1 IN (''A', 'B'') since it thinks the String is one argument.

 

Anyway the fix is easy. To do a simple String insert into an SQL query (not using prepared statement method) simple use a ! . Like so:

 

select * from table1 where col1 IN ($P!{parm1})

 

That should fix your problem.

 

 

 

 

 

By: Ryan Fruit - rfruit

RE: Using parameters with SQL IN clauses problem

2003-11-11 10:51

I case I was clear enough in the about post - the parm1 parameter should contain the String " 'A','B' ". Like this in Java:

 

String parm1 = " 'A', 'B' ";

 

Using the ! method the String will be dropped in and create the SQL you need:

 

select * from table1 where col1 IN ('A', 'B')

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...