Java

Java RSS

Need help with SQL embedded in Java

•

Join Date: Sep 2007

Posts: 4

Reputation: mat111 is an unknown quantity at this point

Solved Threads: 0

mat111

Offline

Newbie Poster

Need help with SQL embedded in Java

Sep 10th, 2007

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'...".

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
ResultSet rs=st.executeQuery("Select * from Employees where Last Name="+request.getParameter("LastName"));//'st' is the Statement object.
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.

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
String LastName=request.getParameter("LastName");
System.out.println(LastName);//'request' is the object of HttpServletRequest
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;

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
ResultSet rs=st.executeQuery("Select * from Employees where Last Name="+LastName);
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

Last edited by mat111; Sep 10th, 2007 at 2:57 am.

•

Join Date: Feb 2006

Posts: 2,385

Reputation: masijade has much to be proud of

Solved Threads: 252

masijade

Offline

Nearly a Posting Maven

Re: Need help with SQL embedded in Java

Sep 10th, 2007

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.

Java Programmer and Sun Systems Administrator

----------------------------------------------

Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it.
--Brian Kernighan

•

Join Date: Sep 2007

Posts: 4

Reputation: mat111 is an unknown quantity at this point

Solved Threads: 0

mat111

Offline

Newbie Poster

Re: Need help with SQL embedded in Java

Sep 11th, 2007

•

Originally Posted by 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.

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.

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
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);
		}
	}   
}
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

•

Join Date: Feb 2006

Posts: 2,385

Reputation: masijade has much to be proud of

Solved Threads: 252

masijade

Offline

Nearly a Posting Maven

Re: Need help with SQL embedded in Java

Sep 11th, 2007

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
ResultSet rs=st.executeQuery("Select * from Employees where LastName='elm'");
ResultSet rs=st.executeQuery("Select * from Employees where LastName='elm'");

should be

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
ResultSet rs=st.executeQuery("Select * from Employees where LastName='" + elm + "'");
ResultSet rs=st.executeQuery("Select * from Employees where LastName='" + elm + "'");

PS as follows:

 Help with Code Tags 
 Java Syntax (Toggle Plain Text) 
PreparedStatement ps = conn.prepareStatement("Select * from Employees where LastName=?")
ps.setString(1, elm);
ResultSet rs = ps.executeQuery();
PreparedStatement ps = conn.prepareStatement("Select * from Employees where LastName=?")
ps.setString(1, elm);
ResultSet rs = ps.executeQuery();

Last edited by masijade; Sep 11th, 2007 at 10:01 am. Reason: Double open parens (oops)

•

Join Date: Aug 2007

Posts: 80

Reputation: lookof2day is an unknown quantity at this point

Solved Threads: 10

lookof2day lookof2day is offline

Offline

Junior Poster in Training

Re: Need help with SQL embedded in Java

Sep 11th, 2007

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();