Can anyone explain how an SQL statement with a LIKE clause is executed properly?
Seems like it ought to be cut and dried, no pun intended!
When I run the following and set the requestor name = ?, and correctly type in the entire name, a result set (albeit abbreviated) will return.
But if I try to set the request param to LIKE I get an error of some kind, either invalid cursor state or NullPointer exception.
Here's my code.
Statement selstmt = connection.createStatement();
String preparedQuery = "SELECT DISTINCT AID, ACTIVE, REQUESTOR_NAME,REQUESTOR_EMAIL" +
" FROM CHANGE_CONTROL_USER, CHANGE_CONTROL_ADMIN " +
" WHERE REQUESTOR_NAME LIKE '%?%';";
String reqName = request.getParameter("requestor_name");
PreparedStatement prepstmt = connection.prepareStatement(preparedQuery);
prepstmt.setString(1, reqName);
ResultSet rslts = prepstmt.executeQuery();
rslts.next();
int aidn = rslts.getInt(1);
int actbox = rslts.getInt(2);
String reqname = rslts.getString(3);
String reqemails = rslts.getString(4);
It's also returning only 1 record for some reason, as I have the following:
<% while (rslts.next()) { %>
<tr class="style17">
<td><%=reqname%></td>
<td><%=reqemails%></td>
<td><%=actbox %></td>
<td><%=aidn %></td>
</tr>
<%
}
rslts.close();
selstmt.close();
%>
If I use
" FROM CHANGE_CONTROL_USER, CHANGE_CONTROL_ADMIN " +
" WHERE REQUESTOR_NAME = ?;";
it will actually spit out the name and corresponding email properly, albeit just one record like I said.
Is there some kind of escape sequence I should be using that I'm not?
And why just the one record?
Any help or direction is appreciated!
Thanks.