Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Getting SQL Error and not sure why.

 
Scott Updike
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm getting a SQL error in MySQL where I try and build a where clause dynamically (from user suplied fields in web page) and append this where clause string to the existing query in my prepareStatement function call.


String srchCriteria = request.getParameter("searchCriteria");
String srchValue = request.getParameter("searchValue");

//build a dynamic where clause based on user supplied values from web page.

if (srchCriteria.equals("BA Contact")) {
whereClause = "where ba_contact = ?";
} else if (srchCriteria.equals("Priority")) {
whereClause = "where priority = ?";
} else if (srchCriteria.equals("Stage")) {
whereClause = "where stage = ?";
} else if (srchCriteria.equals("Product Line")) {
whereClause = "where product_line = ?";
} else {
whereClause = "";
}

Connection conn1 = (Connection) getServletContext().getAttribute("dbconn");
PreparedStatement stmt = conn1.prepareStatement("select * from table_name" + whereClause);
stmt.setString(1,srchValue);
ResultSet rslt = stmt.executeQuery();
Result myResult = ResultSupport.toResult(rslt);
request.setAttribute("results",myResult);
RequestDispatcher view = request.getRequestDispatcher("main.jsp");
view.forward(request, response);


Everything seems to work (values are getting passed correctly), but I get the following error message from Tomcat when I enter 'High' as the srchValue:

...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'High''
...

It seems to think that I'm wrapping my srchValue in a single quote in front and a double in the back.

Does anyone have any suggestions? If more information is needed, let me know.

Thanks,
Scott
 
Martin Simons
Ranch Hand
Posts: 196
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
put a space in the string after table_name
 
Scott Updike
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ugh. That worked!

Thanks for your help.
Scott
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic