Member Avatar for mat111

Hello all,

Am new here, so please bear with me - if i dont get some things right!

Okie Dokie. Lets get started.

I was working with Tomcat/Servlet/ODBC.

I created a table in MS Access. Created the Connection, Statement, query and everything. I want to retrieve the information from the table depending on the search criterion(like for ex.in the html page I send the Last Name, catch that in servlet, and then use that to get the required data from the table). Now here's my problem, when i write the following, the Tomcat container gives errors-saying "...Syntax Error(missing operator) in query expression 'Last name=mat'...".

ResultSet rs=st.executeQuery("Select * from Employees where Last Name="+request.getParameter("LastName"));//'st' is the Statement object.

I initially thought the problem was in catching the parameter(but it wasn't so,) 'cause i did the following, and i got the output in the container console.

String LastName=request.getParameter("LastName");
System.out.println(LastName);//'request' is the object of HttpServletRequest

ok, now since i got the Parameter read in my servlet from the html page, i tried the following;

ResultSet rs=st.executeQuery("Select * from Employees where Last Name="+LastName);

but again the result was no different from the earlier....

please help suggest what i can possibly do....

is there something i am missing?

thanx for reading,

Mat

  • 3 Contributors
  • 7 Replies
  • 85 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by mat111
Member Avatar for masijade

The problem is quoting. The "last name" should be quoted in the SQL with single quotation marks ('). To avoid errors like this in the future (and to prevent SQL injection attacks), use PreparedStatement and its setString method. See the API for more information.

commented: good advice +3
Member Avatar for mat111

The problem is quoting. The "last name" should be quoted in the SQL with single quotation marks ('). To avoid errors like this in the future (and to prevent SQL injection attacks), use PreparedStatement and its setString method. See the API for more information.

Hello Masijade,

Thanx for replying. As you said, I did the following, but now the output is "Record not found"! I post the code for kind reference.

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

/**
 * Servlet implementation class for Servlet: SelectStatement
 *
 */
 public class SelectStatement extends javax.servlet.http.HttpServlet implements javax.servlet.Servlet {
    /* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#HttpServlet()
	 */
	 Connection con;
	 
		
	/* (non-Javadoc)
	 * @see javax.servlet.Servlet#destroy()
	 */
	public void destroy() {
		// TODO Auto-generated method stub
				
		try
		{
			if(con!=null)
			{
				con.close();
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}   	
	
	/* (non-Java-doc)
	 * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	
		try
		{
		response.setContentType("text/html");
		
		PrintWriter pw=response.getWriter();
		Statement st=con.createStatement();
		
		pw.println("<html>");
		pw.println("<body bgcolor='green'><center>");

		
		String elm=request.getParameter("EmpLastName");
		System.out.println(elm);
		
		ResultSet rs=st.executeQuery("Select * from Employees where LastName='elm'");

		ResultSetMetaData rsmd=rs.getMetaData();
		int count=rsmd.getColumnCount();

		System.out.println(count);
		
		if(rs.next())
		{
			pw.println("<h2>Employee Details.</h2>");
			pw.println("<table border=1 cellpadding=3 cellspacing=0>");
			pw.println("<tr>");
			
			for(int i=1;i<=count;i++)
				pw.println("<th align=right width=100>"+rsmd.getColumnName(i)+"</th>");

			    pw.println("</tr>");
				pw.println("<tr>");
				
			for(int i=1;i<=count;i++)
				pw.println("<td align=right width=100>"+rs.getString(i)+"</td>");
				
		}
		else
			pw.println("<h3>Record not found!!</h3>");
		
		pw.println("</center></body></html>");
		pw.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
	}  	  	  	  
	
	/* (non-Javadoc)
	 * @see javax.servlet.GenericServlet#init()
	 */
	public void init(ServletConfig config) throws ServletException {
		// TODO Auto-generated method stub
				
		try{
						
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url="Jdbc:Odbc:MyEMP"; 
			con=DriverManager.getConnection(url);

			System.out.println("-----------------------");
			System.out.println("Connection Establish!");
		}
		catch(Exception e)
		{
			System.out.println(e);
		}
	}   
}

Appreciate any replies.

And btw, you asked to use PreparedStatement. Could you write a code snippet? As I understand, PreparedStatement interface is generally used for inserting or updating data into the table, but not for retrieving?

-regds,

Mat

Member Avatar for masijade 
ResultSet rs=st.executeQuery("Select * from Employees where LastName='elm'");

should be

ResultSet rs=st.executeQuery("Select * from Employees where LastName='" + elm + "'");

PS as follows:

PreparedStatement ps = conn.prepareStatement("Select * from Employees where LastName=?")
ps.setString(1, elm);
ResultSet rs = ps.executeQuery();
Member Avatar for lookof2day

You can also use JDBC 2.0 enhancements. Try the following code for inserting.
ResultSet rs = statement.executeQuery("Select * from Employees");
rs.moveToInsertRow();
rs.updateString("LastName","mat111");
rs.updateString("FirstName","mat111");
rs.insertRow();
rs.moveToCurrentRow();

There are updateXXX() methods for different data types available. Plus it makes the code more readable. The same updateXXX() methods can also be used to update rows. You would only require to move to the row to be updated.

you can fire an update query as:
rs = statement.executeQuery("SELECT EMP_NAME FROM Employees where EMP_CODE=" + empCode );
int row = rs.getRow(); // returns the absolute value of row to be updated.

rs.absolute(row);//move the cursor to the row to be updated.
rs.updateString("LastName","mat111");
rs.updateString("FirstName","mat111");
rs.updateRow();//notice the difference.
rs.moveToCurrentRow();

Member Avatar for masijade

That would be true, if he were, in this instance, interested in doing any updates.

Member Avatar for mat111

Thanx jade bro........that was something.....

Member Avatar for mat111

thanx lookof2day....for your time man!

take it ez.

Mat

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.