This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Getting SQL Error and not sure why. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting SQL Error and not sure why." Watch "Getting SQL Error and not sure why." New topic
Author

Getting SQL Error and not sure why.

Scott Updike
Ranch Hand

Joined: Feb 16, 2006
Posts: 92
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

Joined: Mar 02, 2006
Posts: 196
put a space in the string after table_name
Scott Updike
Ranch Hand

Joined: Feb 16, 2006
Posts: 92
Ugh. That worked!

Thanks for your help.
Scott
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Getting SQL Error and not sure why.
 
Similar Threads
Data Access Object Design Questions
Problem using regular expressions on a SQL where clause to get field names and values
How to use httpsession with dao factory
JSP-JDBC
Auto Commit option fails to work