Hey Andreas -- so if you're saying that after solving your SQL exception you are
not returning any records from the database then I would look at the sql
statement itself.
1. have you tried hardcoding a sql statement that you know works from a sqlplus
command line?
2. have you tried adding a semi-colon to the end of your sql statement?
rs = stmt.executeQuery("SELECT username, password FROM Lecturers
WHERE username = '+ username +' AND password = '+ password +'; " );
^^^
Hope this helps!
Dave Dumaresq ([EMAIL PROTECTED]) 604-599-2120
Programmer/Analyst , IET Kwantlen University College BC, Canada
Andreas <[EMAIL PROTECTED]> on 12/02/1999 06:13:02 AM
Please respond to "A mailing list for discussion about Sun Microsystem's Java
Servlet API Technology." <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
cc: (bcc: David Dumaresq/kwantlen/Ca)
Subject Servlet problem with result from querying an
: Access db.
Hi there,
although a novice, I am trying to develop a room booking system using
servlets and JDBC. Currently I am trying to get my LoginServlet to work.
This system is a project from a university so things like multiuser access
and connection pools are not of my interest at the moment.
The loginServlet I developed is part of the following:
1. First the user accesses an HTML form in order to provide Login and
Password. He/she then submits the query that is sent via GET to the servlet
in order to authenticate user access via the database connection.
2. Here comes the servlet that opens a connection with the db and executes a
query that checks a table with the user details, username/password. If it
finds ANY row in the db table then user access is granted and the user is
redirected to another URL that accomodates my booking form(which is still
under construction).
3. If on the other hand there are no matches from the table the user is
thrown an Html error message(from within servlet via out.println()s..).
My problems started when I was getting SQL exception messages notifying me
of something wrong in my query statement. I solved that but now everytime I
run the servlet via JWS1.1.3 I always get the Html error message mentioned
above even if in the Login form I provide the exact user details from the
database.
Just to add some more info on the problem, I am using:
- JavaWebServer1.1.3 to run/accomodate servlets and to store Html files in
public_html
- everything runs locally on WIN98 machine
- Java2 SDK
- javax.servlet API
- standard java.sql db connectivity classes once my db code is simple..
The following code is my servlet code as a whole:
import java.io.*;
import java.util.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class LoginServlet extends HttpServlet {
static String username = "";
static String password = "";
public void doGet(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
username = req.getParameter("username"); //via req parameter grab username
(textfield)input from form
password = req.getParameter("password"); //via " " " password
(textfield)input " "
//set the output stream that goes back to the browser to HTML
res.setContentType("text/html");
//get a handle to the writer going back to the client
PrintWriter out = res.getWriter();
//Create a connection object
Connection con = null;
//Create a statement object
Statement stmt = null;
//Create a ResultSet object to encapsulate retrieved data
ResultSet rs = null;
try {
//Load the jdbc:odbc bridge/driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Get a connection to the Access database
con = DriverManager.getConnection("jdbc:odbc:RoomBookDB", "andreas",
"stellatos");
//Create a statement object that is used for querying the database
stmt = con.createStatement();
//Execute SQL query and get a result set
rs = stmt.executeQuery("SELECT username, password FROM Lecturers
WHERE username = '+ username +' AND password = '+ password +'" );
//If the query returns ANY ROW from the db, then grant access, if
not, show error message
if(rs.next()) {
res.sendRedirect("http://andreas:8080/BookingForm.html"); }
else {
out.println("<HTML>");
out.println("<HEAD><TITLE>Login Error</TITLE></HEAD>");
out.println("<BODY>");
out.println("<P>Error Message!</P>");
out.println("<P>Your PASSWORD or USERNAME is incorrect. Please return
to the Login Screen to re-enter your details.</P>");
out.println("</BODY></HTML>");
}
}
//Handle a series of exceptions
catch(ClassNotFoundException e) {
out.println("Couldn't load the database driver: " + e.getMessage());
}
catch(SQLException e) {
out.println("SQLException caught: " + e.getMessage());
}
finally {
//Close the database connection
try {
if (con != null) con.close();
}
catch(SQLException ignored) { }
}
}
}
What I am worried about now is whether the reDirection code I have used is
wrong, but still, it's very simple:
if match in the db then
go to URL:....BookingForm
else
throw error
I have been getting some help so far but is it possible to have a collective
view(!) from the SERVLET-INTEREST mailing list? I'm quite desperate now as I
am trying to continue writing my other servlet(s) and this problem keeps
persisting.. What am I doing wrong? I have also attached the file Login.html
to check the source even though I think there's nothing wrong with it.
Thanks,
Andreas Stellatos
Title: Online Booking Form - Login Screen
For further info please contact the administrator: [EMAIL PROTECTED]
