WHERE clause using LIKE....

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