I am using postgres 8.2 with the 8.2.504 jdbc3 driver.
I am getting data from a untrusted source. Hence a
prepared
statement. I also need a partial match.
String query = " select * from table_foo where bar =
LIKE %?% "
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "haha");
....
This craps out when run. Try adding single quotes
before and
after the: %?%
String query = " select * from table_foo where bar =
LIKE '%?%' "
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "haha");
...
This craps out too.
A quick search of the archives doesn't shed light on
this issue. I
don't need a JDBC escape since I want to use a % char.
So how do I use LIKE within a prepared statement ? I'm
sure I'm
missing something obvious here....
Best regards,
--j
____________________________________________________________________________________
Don't pick lemons.
See all the new 2007 cars at Yahoo! Autos.
http://autos.yahoo.com/new_cars.html
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
Craps out how ?
bar = like %?%
is invalid
bar like '%?%'
is closer to the correct syntax
Dave
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
That was a typo. Sorry. I wasn't using '=' at all.
>
> bar like '%?%'
> is closer to the correct syntax
That's what I was doing. The driver gives this error:
-----------------------------
org.postgresql.util.PSQLException: The column index is
out of range: 1, number of columns: 0.
at
org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52)
at
org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:117)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.bindString(AbstractJdbc2Statement.java:2118)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1241)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setString(AbstractJdbc2Statement.java:1227)
....etc........
--------------------------------
I hacked around a bit and finally said:
.... bar like ? and foo like ?
ps.setString(1, "%" + myvalue + "%")
ps.setString(2, "%" + my_other_value + "%")
That worked but it's a bit counter-intuitive.
Best regards,
--j
____________________________________________________________________________________
Don't get soaked. Take a quick peak at the forecast
with the Yahoo! Search weather shortcut.
http://tools.search.yahoo.com/shortcuts/#loc_weather
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
That should be:
String query = "select * from table_foo where bar like ?";
PreparedStatement ps = con.prepareStatement(query);
ps.setString(1, "%haha%");
Thomas
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
LIKE '%' || ? || '%'