How can i use 'like' in PreparedStatement

[Laltu]

Hi,
can anyone plz let me know how oracle 'like' keyword can be used in a
prepared statement ?i tried like
SQL
select projectId from t_project where projectName like ?

Setting the value
pstmt.setString(1,strProjectName);
But it is not working.

 

[Adam Maass]

Hi,
can anyone plz let me know how oracle 'like' keyword can be used in a
prepared statement ?i tried like
SQL
select projectId from t_project where projectName like ?

Setting the value
pstmt.setString(1,strProjectName);
But it is not working.

How exactly is this not working? What's the error message? If there's no
error message, then what behavior are you seeing? What do you expect to see?

-- Adam Maass

 

[Thomas Hawtin]

can anyone plz let me know how oracle 'like' keyword can be used in a
prepared statement ?i tried like
SQL
select projectId from t_project where projectName like ?

IIRC, in general you can't as the right hand side of LIKE is essentially
code, and may be compiled with the rest of the statement.

To get around it, you can either:
o Use a number of statements.
o Process the results procedurally in PL/SQL.
o Process the results in on the client side.
o Dynamic SQL (for the foolhardy).
o A combination of the above.

Be very, very careful with dynamic SQL. It appears that a good
proportion of dynamic SQL in production on live servers allow injection
attacks.

Tom Hawtin

 

[Roedy Green]

Setting the value
pstmt.setString(1,strProjectName);
But it is not working.

this may be relevant:
http://forum.java.sun.com/thread.jspa?threadID=592281&tstart=120

 

[karlheinz klingbeil]

Laltu schrub am Sonntag, 28. August 2005 07:20
folgendes:

Hi,
can anyone plz let me know how oracle 'like' keyword
can be used in a prepared statement ?i tried like
SQL
select projectId from t_project where projectName
like ?

Setting the value
pstmt.setString(1,strProjectName);
But it is not working.

This should work, but have you considered that LIKE
uses some kind of pattern matching ?
For example if you just put the String 'project1'
into ?, then LIKE will only find the 'project1' and
not 'project11'.
You have to use wildcards with like, because it doesn't
make sense if you don't.
Search for '%' if you want to see all projects, or
'project%' if you want to see all that starts with the
word 'project'

 

[Raymond DeCampo]

IIRC, in general you can't as the right hand side of LIKE is essentially
code, and may be compiled with the rest of the statement.

Tom,

Are you sure about this? Can you reference some documentation?

Thanks,
Ray

 

[Thomas Hawtin]

Are you sure about this? Can you reference some documentation?

I think what I was actually thinking about was using expressions that
are dependent on the row being considered. I wouldn't be surprised to
see variations between databases in this area.

Tom Hawtin

 

[Raymond DeCampo]

I think what I was actually thinking about was using expressions that
are dependent on the row being considered. I wouldn't be surprised to
see variations between databases in this area.

Tom Hawtin

I see (I think). Considering

SELECT * FROM Foo WHERE bar LIKE ?

You are aware of no problems with replacing ? with a string literal,
e.g. "%xyzzy%". But you would expect problems with an expression, e.g.
"lower(quux) || '%'".

Is this what you had in mind?

Thanks,
Ray

 

[Laltu]

Thanks you all for replying.I have got a workaround for my problem

 

[Roedy Green]

I have got a workaround for my problem

I figure if you ask a question, and you finally figure out what works
you are under a moral obligation to spill the beans in case someone
else later has a similar problem.