WHERE clause using LIKE....

[2001 JI Open Discussion]

By: Uniq - uniq_2002

WHERE clause using LIKE....

2002-09-25 03:16

Hi,

 

i have sql :

<queryString><![CDATA[sELECT * FROM "LEVEL_TABEL" WHERE $P{selectBy} = ($P{selectText} || '%') ORDER BY $P!{order1}]]></queryString>

 

and in my jsp:

parameters.put("selectBy", ""Code"");

parameters.put("selectText","1");

parameters.put("order1", ""Code"");

 

i am use database postgresql.

 

when i'll try to give value selectText with "" .

my query will get result (all data will show in pdf)

 

but when i'll to give some value like i write in my jsp and the pdf will appear, i get this message box :

"This file cannot be opened because it has no pages"

so the pages show in blank screen.

and no one error in my server.

 

When i'll try the query in my database i get the result.

 

I am sorry, if my problem to simple for u. But i dont get solution.

 

so... i am wrong with my sql or my jsp??

 

For any help, i am very appreciate..:-)

 

 

regards,

 

uniQ

 

 

 

By: Teodor Danciu - teodord

RE: WHERE clause using LIKE....

2002-09-26 07:16

 

Hi,

 

What you don't seem to understand is that those

two following SQL queries are totally different

queries from the JDBC point of view, and that we

cannot supply the table column name as

a PreparedStatement parameter.

 

SELECT * FROM "LEVEL_TABEL" WHERE Column1 = 'value%' ORDER BY Column1

 

SELECT * FROM "LEVEL_TABEL" WHERE Column2 = 'value%' ORDER BY Column2

 

I can only point you to carefully read the JDBC

documentation to see what you can or cannot

supply as a parameter to a PrepareStatement

object.

 

JasperReports uses a PreparedStatement object

to execute queries and the $P{} parameters are

transformed into PreparedStatement parameters

when the query is executed.

 

Now, lets get back to your query.

If the table column changes dynamically, either in

the WHERE clause ar the ORDER BY clause,

you have to totally give up using the $P{}

parameters.

Because you will have to construct the query using

only $P!{} parameters (note the '!' character).

 

The $P!{} parameters will not be transformed into

normal PreparedStatement parameters, but will be

used to replace portions of the SQL query

at runtime, before constructing the

PreparedStatement that mentioned.

 

The only restriction that I see is that for the

moment, you cannot combine $P{} parameters with

$P!{} parameters in the same query.

You have to decide what type or parameter you want or have to use and stick to it.

 

So, this query of yours would probably look like this:

 

<queryString><![CDATA[sELECT * FROM "LEVEL_TABEL" WHERE $P!{selectBy} = '$P{selectText}%' ORDER BY $P!{order1}]]></queryString>

 

I hope this helps.

Teodor

 

 

 

By: Uniq - uniq_2002

RE: WHERE clause using LIKE....

2002-09-26 19:15

hi teodord,

 

thanks for the answer...:-)

i am late to replaying 'cause i got solution yesterday.

change $P{selectBy} with $P!{selectBy}.

 

the error message is make me confused, because its not says that i got problem with query sql.

 

thanks...

 

 

regards,

 

 

uniQ