Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Error with connection: java.sql.SQLException: ORA-00917: missing comma

Tags:

java

jdbc

I'm working on a Web Application using Java Servlets. I am trying to implement login, logout, and registration functionality but I'm a little confused on how to get everything working. Here is the code for the Login page:

To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package hw5package;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.UUID;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Tyler
 */
public class Login extends HttpServlet {

/**
 * Processes requests for both HTTP
 * <code>GET</code> and
 * <code>POST</code> methods.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@cci-ora02.uncc.edu:1521:class";
        String username = "teverha4";
        String password = "qwe123";

        out.println("<html>");
        out.println("<head>");
        out.println("<title>ITIS 4166 Project</title>");
        out.println("</head>");
        out.println("<body>");
        out.println("<div align='center'>");
        out.println("<h1>ITIS 4166 Project - Login Page</h1>");
        out.println("<h2>by: Tyler Everhart</h2>");

        out.println("<br />");
        out.println("<br />");
        out.println("<br />");
        out.println("<br />");

        out.println("<table width='20%' border='1' cellspacing='1' cellpadding='1'>");
        out.println("<form id='loginForm' name='loginForm' method='POST'>");
        out.println("<tr>");
        out.println("<td bgcolor='006F13' align='left' colspan='2'><b>Login</b></td>");
        out.println("</tr>");

        out.println("<tr>");
        out.println("<td bgcolor='35D551' align='left'>Username:</td>");
        out.println("<td bgcolor='35D551'>");
        out.println("<input type='text' name='username' id='username' />");
        out.println("</td>");
        out.println("</tr>");
        String passUsername = request.getParameter("username");

        out.println("<tr>");
        out.println("<td bgcolor='35D551' align='left'>Password:</td>");
        out.println("<td bgcolor='35D551'>");
        out.println("<input type='password' name='pwd' id='pwd' />");
        out.println("</td>");
        out.println("</tr>");
        String passPwd = request.getParameter("pwd");

        out.println("<tr>");
        out.println("<td bgcolor='006F13' align='center' colspan='2'>");
        out.println("<input type='submit' value='Submit' onclick='loginValidation()' /> ");
        out.println("<input type='reset' value='Reset' />");
        out.println("</td>");
        out.println("</tr>");
        out.println("</form>");
        out.println("</table>");
        out.println("<a href='Registration'>New User, Click Here to Register</a>");
        out.println("</div>");
        out.println("</body>");
        out.println("</html>");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, username, password);

        Statement statement = connection.createStatement();
        String query = "SELECT UserName, PassWord FROM UserLogin";

        ResultSet resultSet = statement.executeQuery(query);

        HttpSession session = request.getSession(true);

        while (resultSet.next()) {
            if ((passUsername.equals(resultSet.getString("UserName")) && passPwd.equals(resultSet.getString("PassWord")))) {
                String sessionID = UUID.randomUUID().toString();
                session.setAttribute("username", passUsername);
                session.setAttribute("sessionID", sessionID);
                response.sendRedirect("Categories");
            }
            else if (passUsername.equals("") || passPwd.equals("")) {
                out.println("Enter a username and password.");
            }
            else {
                out.println("Either your username or password is incorrect or could not be found.");
                out.println("Please try again, or click the link below to register.");
            }
        }
    }
    catch (ClassNotFoundException cnfe) {
        System.err.println("Error loading driver: " + cnfe);
        out.println("<tr>Error loading driver: " + cnfe + "</tr>");
    }
    catch (SQLException sqle) {
        System.err.println("Error with connection: " + sqle);
        out.println("<tr>Error with connection: " + sqle + "</tr>");
    }
    finally {            
        out.close();
    }
}

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
 * Handles the HTTP
 * <code>GET</code> method.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);
}

/**
 * Handles the HTTP
 * <code>POST</code> method.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);
}

/**
 * Returns a short description of the servlet.
 *
 * @return a String containing servlet description
 */
@Override
public String getServletInfo() {
    return "Short description";
}// </editor-fold>

}

Here is the code for the Registration page:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package hw5package;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.Random;
import java.util.UUID;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Tyler
 */
public class Registration extends HttpServlet {

/**
 * Processes requests for both HTTP
 * <code>GET</code> and
 * <code>POST</code> methods.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@cci-ora02.uncc.edu:1521:class";
        String username = "teverha4";
        String password = "qwe123";

        out.println("<html>");
        out.println("<head>");
        out.println("<title>ITIS 4166 Project</title>");
        out.println("</head>");
        out.println("<body>");
        out.println("<div align='center'>");
        out.println("<h1>ITIS 4166 Project - Registration Page</h1>");
        out.println("<h2>by: Tyler Everhart</h2>");

        out.println("<br />");
        out.println("<br />");
        out.println("<br />");
        out.println("<br />");

        out.println("<table width='25%' border='1' cellspacing='1' cellpadding='1'>");
        out.println("<tr>");
        out.println("<th bgcolor='006F13' align='left' colspan='2'>Register Account Information</th>");
        out.println("</tr>");

        out.println("<tr>");
        out.println("<td bgcolor='35D551' align='left'>Requested Username:</td>");
        out.println("<td bgcolor='35D551'>");
        out.println("<form id='enterForm' name='enterForm'>");
        out.println("<input type='text' name='requested_username' />");
        out.println("</td>");
        out.println("</tr>");
        String passUsername = request.getParameter("requested_username");

        out.println("<tr>");
        out.println("<td bgcolor='35D551' align='left'>Password:</td>");
        out.println("<td bgcolor='35D551'>");
        out.println("<input type='password' name='pwd' />");
        out.println("</td>");
        out.println("</tr>");
        String passPwd = request.getParameter("pwd");

        out.println("<tr>");
        out.println("<td bgcolor='35D551' align='left'>Confirm Password:</td>");
        out.println("<td bgcolor='35D551'>");
        out.println("<input type='password' name='pwd1' />");
        out.println("</td>");
        out.println("</tr>");
        String passPwd1 = request.getParameter("pwd1");

        out.println("<tr>");
        out.println("<td bgcolor='006F13' align='center' colspan='2'>");
        out.println("<input name='Submit' type='submit' value='Submit' /> ");
        out.println("<input type='reset' value='Reset' />");
        out.println("</td>");
        out.println("</tr>");
        out.println("</form>");
        out.println("</table>");
        out.println("</div>");
        out.println("</body>");
        out.println("</html>");

        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, username, password);

        Statement statement = connection.createStatement();

        HttpSession session = request.getSession();

        if (passPwd.equals("") || passPwd1.equals("")) {
            Integer registerError = Integer.valueOf(1);
            session.setAttribute("registerError", registerError);
            out.println("Please enter a valid password.");
        }
        else if (!passPwd.equals(passPwd1)) {
            Integer registerError = Integer.valueOf(2);
            session.setAttribute("registerError", registerError);
            out.println("Your password and confirm password does not match.");
        }
        else {
            String query2 = "INSERT INTO UserLogin (UserName, PassWord, StockAmt, TechnologyStock, UtilityStock) VALUES (?,?,?,?,?)";
            PreparedStatement stmt = connection.prepareStatement(query2);
            stmt.setString(1, passUsername);
            stmt.setString(2, passPwd);
            stmt.setInt(3, 0);
            stmt.setInt(4, 0);
            stmt.setInt(5, 0);
            stmt.executeQuery();
            session.setAttribute("sessionUsername", passUsername);
            String sessionID = UUID.randomUUID().toString();

            Random StockID1 = new Random();
            StockID1.nextInt(10);
            String insertDataSqlMicrosoft = "INSERT INTO StockInfo(StockID, UserID, Type, StockName, Owned, Price, Total) VALUES('" + StockID1 + "','" + passUsername + ",'Technology','Microsoft (MSFT)',0,32.75,0";
            PreparedStatement stmtMicrosoft = connection.prepareStatement(insertDataSqlMicrosoft);
            stmtMicrosoft.executeQuery();

            Random StockID2 = new Random();
            StockID2.nextInt(4);
            String insertDataSqlIntel = "INSERT INTO StockInfo(StockID, UserID, Type, StockName, Owned, Price, Total) VALUES('" + StockID2 + "','" + passUsername + "','Technology','Intel (INTC)',0,26.75,0)";
            PreparedStatement stmtIntel = connection.prepareStatement(insertDataSqlIntel);
            stmtIntel.executeQuery();

            Random StockID3 = new Random();
            StockID3.nextInt(9);
            String insertDataSqlDuke = "INSERT INTO StockInfo(StockID, UserID, Type, StockName, Owned, Price, Total) VALUES('" + StockID3 + "','" + passUsername + "','Utility','Duke Energy (DUK)',0,89.00,0";
            PreparedStatement stmtDuke = connection.prepareStatement(insertDataSqlDuke);
            stmtDuke.executeQuery();

            Random StockID4 = new Random();
            StockID4.nextInt(7);
            String insertDataSqlCable = "INSERT INTO StockInfo(StockID, UserID, Type, StockName, Owned, Price, Total) VALUES('" + StockID4 + "','" + passUsername + "','Utility','Time Warner Cable (TWC)',0,30.00,0)";
            PreparedStatement stmtCable = connection.prepareStatement(insertDataSqlCable);
            stmtCable.executeQuery();

            session.setAttribute("sessionID", sessionID);
            response.sendRedirect("Categories");
        }
    } 
    catch (ClassNotFoundException cnfe) {
        System.err.println("Error loading driver: " + cnfe);
        out.println("<tr>Error loading driver: " + cnfe + "</tr>");
    }
    catch (SQLException sqle) {
        System.err.println("Error with connection: " + sqle);
        out.println("<tr>Error with connection: " + sqle + "</tr>");
    } finally {            
        out.close();
    }
}

// <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
/**
 * Handles the HTTP
 * <code>GET</code> method.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);
}

/**
 * Handles the HTTP
 * <code>POST</code> method.
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    processRequest(request, response);
}

/**
 * Returns a short description of the servlet.
 *
 * @return a String containing servlet description
 */
@Override
public String getServletInfo() {
    return "Short description";
}// </editor-fold>
}

I figured out the problem that I was having before with reading from the database only to now have a new problem. When I type in a username and password that I have verified are present in the database I am not redirected to the page that I want to go to. Also when I try to register a new user I am not redirected to the page and am given an error that says:

Error with connection: java.sql.SQLException: ORA-00917: missing comma

The username and password for the person registering are sent to the database but the program doesn't redirect you to the appropriate page.

like image 536
tyeverhart11 Avatar asked Oct 24 '22 06:10

tyeverhart11


1 Answers

Check your driver class and URL syntax.

Syntax for URL connection is

 jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE

Here is a working sample I got from OracleFAQ.

import java.sql.*;
class Conn {
  public static void main (String[] args) throws Exception
  {
   Class.forName ("oracle.jdbc.OracleDriver");

   Connection conn = DriverManager.getConnection
     ("jdbc:oracle:thin:@//localhost:1521/orcl", "scott", "tiger");
                        // @//machineName:port/SID,   userid,  password
   try {
     Statement stmt = conn.createStatement();
     try {
       ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
       try {
         while (rset.next())
           System.out.println (rset.getString(1));   // Print col 1
       } 
       finally {
          try { rset.close(); } catch (Exception ignore) {}
       }
     } 
     finally {
       try { stmt.close(); } catch (Exception ignore) {}
     }
   } 
   finally {
     try { conn.close(); } catch (Exception ignore) {}
   }
  }
}

There are few more things I would like to suggest.

  • If you are allowed to use JSP, please go for MVC design.
  • As you might have noticed, you have duplicate code in both your classes. So create a separate class for database connection.

To find the correct hostname/port/service, check tnsnames.ora file. Check OracleFAQ

Here are few more links to get you started:

http://docs.oracle.com/cd/B13789_01/java.101/b10979/getsta.htm

http://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html

http://www.oracle.com/technetwork/indexes/downloads/index.html

like image 145
John Avatar answered Nov 15 '22 01:11

John