Problem in Wilcard Search (%) with Prepared Statement
Hello Everyone,
Im trying to search using the like operator within a prepared statement. The program compiles , but when I execute it I get an empty result set.
I tried the same query in Query Analyser in SQL Server 2000 and it worked flawlessly.
I searched on this forum also and I read recommendations suggesting
to put the ? in like'?'
This gives me an error.
Ive tried all options I could get my hands on ,now I request you all to please provide me some guidance.
Thankyou
The code snippet is as follows :
String sDay="%"+startDay+"%";
ps= con.prepareStatement("Select * from Testdb where days like ? ");
ps.setString(1,sDay);
As I said earlier, this query returns and empty result set.
Thankyou once again.
[906 byte] By [
DT1a] at [2007-11-26 20:36:22]
# 1
You can't, at least AFAIK. The API, in any case, does not provide for it (at least according to the interface docs). A specific Driver may allow for something like this, but then you would have to call a Driver/DB/Platform dependent method to accomplish this.
The problem is, setString (and setObject using a String, AFAIK) will escape all characters that have sepcial meaning in SQL, so that they no longer have special meaning. Which means that your query will then be searching for a literal %, or literal _, rather than their wildcard meaning.
# 3
If you can't find a vendor specific solution, then switch to a Statement rather than a PreparedStatement. That means you will have to escape special characters in the String yourself before building the Statement, and watch out for injection attacks, but it can be done.
Like I said, if you can't find a vendor specific solution in their Driver (which of course means that you cannot, then change DBs without changing that code), then you must use a Statement.
# 4
Its next to impossible for me to switch to Statement from prepared S. because the Query has got way to many conditions and its next to impossible to handle all those in a statement.
I had originally started out with statement itself but then I switched to prepared statement as it was getting unmanageable.
I'll try finding a work around whereby I split the query into two parts using the manageable part and the Wilcard search in the statement and the other unmanageable part in the PS.
Else I will again have to attempt that. I had also had a feeling this was affecting it.
Thanks for the help.
If anyone has some solution please post it .
Thank you.
DT1a at 2007-7-10 1:29:36 >
