2 Replies - 1815 Views - Last Post: 06 April 2012 - 11:03 AM

#1 pgeric  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-February 12

Servlet Error when performing SQL Statement

Posted 06 April 2012 - 08:30 AM

I keep getting a java.sql.SQLException with the message [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression. I am using Apache Tomcat 5.5 and from the output log I keep on the receiving the error above. I honestly do know what is going wrong.

Here is the code for my servlet

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

public class EmployeeAddressQuery extends HttpServlet
{
    //driver initalization
    private static String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
    private static String URL = "jdbc:odbc:userId";
    private static String URL2= "jdbc:odbc:employeeAddress";

    

    //Create the connection object
    private static Connection connect;
    private static Connection connection2;
    //Create the statement object
    private static Statement statement;
    private static Statement statement2;
    private boolean badConnection = false;
    private String errorType = "FirstValue";
    public void init(ServletConfig config) throws ServletException
    {
        super.init(config);
        try
        {
            //Declare the driver and connect to the database
            Class.forName(DRIVER).newInstance();

            //Connection
            connect = DriverManager.getConnection(URL);
            statement = connect.createStatement();
            //connection2 = DriverManager.getConnection(URL2);
        }
        catch(Exception e)
        {
            System.out.println("ERROR: Problem with the database connection");
            e.printStackTrace();
            connect = null;
            badConnection = true;
            errorType = "Connection Error";
        }
    }

    //do post method
    public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
    {
        ResultSet results = null;
        final String BEGIN_HTML = "<html>\n"+
            "<head>\n"+
            "<title>Employee Address Query</title>\n"+
            "</head>\n"+
            "<body>\n"+
            "<ceneter>\n";

        final String END_HTML = "\n </center>\n" +
            "</body>\n"+
            "</html>";

        //String variables for the fields
        String userName, password, firstName, lastName;
        boolean good = true;

        //Retrieve the parameters posted to the server
        userName = req.getParameter("id");
        password = req.getParameter("pword");
        lastName = req.getParameter("lname");
        firstName = req.getParameter("fname");

        //Initalize the output writer to send HTML back to the client
        PrintWriter out = res.getWriter();
        res.setContentType("text/html");

        //if the user does leave a field blank display a message
        if(userName.equals(""))
        {
            out.print("<h3>Please press the back button and enter a valid userID</h3>" + END_HTML);
            out.close();
            return;
        }
        else if(password.equals(""))
        {
            out.print("<h3>Your password does not match</h3>" + END_HTML);
            out.close();
            return;
        }
        else if(lastName.equals(""))
        {
            out.print("<h3>Your last name was not found</h3>" + END_HTML);
            out.close();
            return;
        }
        else if(firstName.equals(""))
        {
            out.print("<h3>Your first name is not in the system</h3>" + END_HTML);
            out.close();
            return;
        }
        else if(badConnection)
        {
            out.print("<h3>Could not connect to the database</h3>\n" + errorType + END_HTML);
            //close the stream
            out.close();
            return;
        }

        try
        {
            
            //get a Statement object            
            statement = connect.createStatement();

            //Create a SQL SELECT Query for the info provided            
            String query = "SELECT ID, Password FROM UserId WHERE ID = '" + userName + "' + AND Password = '" + password + "'";
            //Execute the statement            
            results = statement.executeQuery(query);
            
        }
        catch(Exception e)
        {
            System.err.println("ERROR: Problem with selecting entry");
            e.printStackTrace();
            good = false;
        }

        if(good)

            try
            {
                
                //get a statemnet object
                statement2 = connection2.createStatement();

                //Create an SQL SELECT query from the data provided
                String info2 = "" + lastName + "','" + firstName + "FROM address";
                statement2.execute("SELECT" + info2);
                out.print(BEGIN_HTML + results + END_HTML);
            }
            catch(Exception e)
            {
                System.err.println("ERROR: your information is wrong");
                e.printStackTrace();
                good = false;
        }
        
    }
    public void destroy()
    {
        try
        {
            connect.close();
            connection2.close();
        }
        catch(Exception e)
        {
            System.out.println("Problem closing the database");
        }
    }
}




The code for the HTML page
<html>  <!--a form that is used to query a database on a server using a servlet-->
	<head>
		<title>RRC BIT Servlet Assignment</title>
	</head>
	<body bgcolor="cyan">
		<form action= "http://localhost:8080/servlet/EmployeeAddressQuery" method="post" >
			<h1>RRC BIT Servlet Assignment</h1><br />
			<h3><u>UserId Information</u></h3><hr />
			Enter your Userid:   <input type="text" name="id" size="20"><br />
			Enter your password: <input type="password" name="pword" size="20"><br />
			<br />
			<h3><u>Query Information</u></h3><hr>
			Enter the Employee Last Name:<input type="text" name="lname" size="20"><br />
			Enter Employee First Name:   <input type="text" name="fname" size="20"><br />
			<br />
			Click Here to submit Query   <input type="submit" value="Find Employee">
		</form>
	</body>
</html>



Is This A Good Question/Topic? 0
  • +

Replies To: Servlet Error when performing SQL Statement

#2 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1155
  • View blog
  • Posts: 2,535
  • Joined: 05-May 05

Re: Servlet Error when performing SQL Statement

Posted 06 April 2012 - 08:51 AM

String info2 = "" + lastName + "','" + firstName + "FROM address";
statement2.execute("SELECT" + info2);



The tokens after the SELECT keyword are column names, not column values.

This post has been edited by blackcompe: 06 April 2012 - 08:52 AM

Was This Post Helpful? 0
  • +
  • -

#3 pgeric  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 10
  • Joined: 11-February 12

Re: Servlet Error when performing SQL Statement

Posted 06 April 2012 - 11:03 AM

Thanks I solved my problem!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1