jdbc - Error with connection: java.sql.SQLException: ORA-00917: missing comma -
i'm working on web application using java servlets. trying implement login, logout, , registration functionality i'm little confused on how working. here code login page:
to change template, choose tools | templates * , open template in 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 both http * <code>get</code> , * <code>post</code> methods. * * @param request servlet request * @param response servlet response * @throws servletexception if servlet-specific error occurs * @throws ioexception if 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 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 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 username , password."); } else { out.println("either username or password incorrect or not found."); out.println("please try again, or click link below 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 connection: " + sqle); out.println("<tr>error connection: " + sqle + "</tr>"); } { out.close(); } } // <editor-fold defaultstate="collapsed" desc="httpservlet methods. click on + sign on left edit code."> /** * handles http * <code>get</code> method. * * @param request servlet request * @param response servlet response * @throws servletexception if servlet-specific error occurs * @throws ioexception if i/o error occurs */ @override protected void doget(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { processrequest(request, response); } /** * handles http * <code>post</code> method. * * @param request servlet request * @param response servlet response * @throws servletexception if servlet-specific error occurs * @throws ioexception if i/o error occurs */ @override protected void dopost(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { processrequest(request, response); } /** * returns short description of servlet. * * @return string containing servlet description */ @override public string getservletinfo() { return "short description"; }// </editor-fold> }
here code registration page:
/* * change template, choose tools | templates * , open template in 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 both http * <code>get</code> , * <code>post</code> methods. * * @param request servlet request * @param response servlet response * @throws servletexception if servlet-specific error occurs * @throws ioexception if 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 valid password."); } else if (!passpwd.equals(passpwd1)) { integer registererror = integer.valueof(2); session.setattribute("registererror", registererror); out.println("your password , confirm password not match."); } else { string query2 = "insert 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 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 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 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 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 connection: " + sqle); out.println("<tr>error connection: " + sqle + "</tr>"); } { out.close(); } } // <editor-fold defaultstate="collapsed" desc="httpservlet methods. click on + sign on left edit code."> /** * handles http * <code>get</code> method. * * @param request servlet request * @param response servlet response * @throws servletexception if servlet-specific error occurs * @throws ioexception if i/o error occurs */ @override protected void doget(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { processrequest(request, response); } /** * handles http * <code>post</code> method. * * @param request servlet request * @param response servlet response * @throws servletexception if servlet-specific error occurs * @throws ioexception if i/o error occurs */ @override protected void dopost(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { processrequest(request, response); } /** * returns short description of servlet. * * @return string containing servlet description */ @override public string getservletinfo() { return "short description"; }// </editor-fold> } i figured out problem having before reading database have new problem. when type in username , password have verified present in database not redirected page want go to. when try register new user not redirected page , given error says:
error connection: java.sql.sqlexception: ora-00917: missing comma
the username , password person registering sent database program doesn't redirect appropriate page.
check driver class , url syntax.
syntax url connection
jdbc:oracle:thin:@//[host][:port]/service here working sample got 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 sys.v_$version"); try { while (rset.next()) system.out.println (rset.getstring(1)); // print col 1 } { try { rset.close(); } catch (exception ignore) {} } } { try { stmt.close(); } catch (exception ignore) {} } } { try { conn.close(); } catch (exception ignore) {} } } } there few more things suggest.
- if allowed use jsp, please go mvc design.
- as might have noticed, have duplicate code in both classes. create separate class database connection.
to find correct hostname/port/service, check tnsnames.ora file. check oraclefaq
here few more links 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
Comments
Post a Comment