Prepared Statements and Oracle functions:
Consider this code:
String searchText ="someInputText";
StringBuffer sqlstmt =new StringBuffer();
sqlstmt.append("SELECT ID_FIELD FROM ");
sqlstmt.append("MY_TABLE WHERE NAME_FIELD ");
sqlstmt.append("like INITCAP('%" + searchText + %')");
PreparedStatement ps = con.prepareStatement(sqlstmt.toString());
This works fine like this, but when I replace the searchText in the sqlstmt to ? it doesn't work:
sqlstmt.append("like INITCAP('?')");
PreparedStatement ps = con.prepareStatement(sqlstmt.toString());
ps.setString(1,"%" + searchText +"%");
How do you use the PreparedStatement with a
function like INITCAP where you need to use a
dynamic ? in the sql statement?
Thanks,
Rob.
[1079 byte] By [
rdare] at [2007-9-18]
I think I have done it. Not with INITCAP but with different Oracle functions. But it was a year ago. I am trying to remember it. Kindly try this piece of code.
//code
PreparedStatement stmt = conn.prepareStatement("select * from emp where ename like initcap(?)");
stmt.setString(1,user);
Please reply if it works,
Cheers
Sekar.
Very easy to miss this.
Don't put the percentages IN the SQL statement.
Append the percentages to your string.
Like this:
String searchText = "%" + "someInputText" + "%";
StringBuffer sqlstmt = new StringBuffer();
sqlstmt.append("SELECT ID_FIELD FROM ")
.append("MY_TABLE WHERE NAME_FIELD ")
.append("like INITCAP(?)");
PreparedStatement ps = con.prepareStatement(sqlstmt.toString());
ps.setString(1, searchText);
resultSet = ps.executeQuery();
Wish I saw this earlier for you.
