Jerry Rankin
asked on
java.sql.SQLException: After end of result set
Gentlemen,
You guys were great the other night in helping me isolate the main problem with the move of my website and getting to the bottom of the problem with the db. I now have only the following error when I try to look up a user:
500 Servlet Exception
java.sql.SQLException: After end of result set
at org.gjt.mm.mysql.ResultSet .checkRowP os(Unknown Source)
at org.gjt.mm.mysql.ResultSet .getString (Unknown Source)
at org.gjt.mm.mysql.ResultSet .getString (Unknown Source)
at _java._roundrockonline._us ers._check _0user_0lo gin__jsp._ jspService (/java/rou ndrockonli ne/users/c heck_user_ login.jsp: 31)
at com.caucho.jsp.JavaPage.se rvice(Java Page.java: 75)
at com.caucho.jsp.Page.subser vice(Page. java:506)
at com.caucho.server.http.Fil terChainPa ge.doFilte r(FilterCh ainPage.ja va:182)
at com.caucho.server.http.Inv ocation.se rvice(Invo cation.jav a:315)
at com.caucho.server.http.Run nerRequest .handleReq uest(Runne rRequest.j ava:346)
at com.caucho.server.http.Run nerRequest .handleCon nection(Ru nnerReques t.java:274 )
at com.caucho.server.TcpConne ction.run( TcpConnect ion.java:1 39)
at java.lang.Thread.run(Threa d.java:536 )
You guys were great the other night in helping me isolate the main problem with the move of my website and getting to the bottom of the problem with the db. I now have only the following error when I try to look up a user:
500 Servlet Exception
java.sql.SQLException: After end of result set
at org.gjt.mm.mysql.ResultSet
at org.gjt.mm.mysql.ResultSet
at org.gjt.mm.mysql.ResultSet
at _java._roundrockonline._us
at com.caucho.jsp.JavaPage.se
at com.caucho.jsp.Page.subser
at com.caucho.server.http.Fil
at com.caucho.server.http.Inv
at com.caucho.server.http.Run
at com.caucho.server.http.Run
at com.caucho.server.TcpConne
at java.lang.Thread.run(Threa
You're trying to access a ResultSet after you have already scrolled through it, perhaps. You're not at the correct position in your ResultSet. What type of ResultSet is it? Can you scroll back to the first record and then start scrolling again?
rs = stmt.executeQuery ( "some query" ) ;
while ( rs.next () )
System.out.println ( rs.getString ( "someColumn" ) ) ;
while ( rs.next () ) // -> will throw exception
....
String str = rs.getString ( "someColumn" ) ; // -> will also throw exception
while ( rs.next () )
System.out.println ( rs.getString ( "someColumn" ) ) ;
while ( rs.next () ) // -> will throw exception
....
String str = rs.getString ( "someColumn" ) ; // -> will also throw exception
ASKER
I get this error when I input a username (email address) and password for signing into the site for current users. If I put in a name or login that does not exist, I get the proper message that says try again. I can also add new without problem. This only pops up when I attempt to login with a valid name.
Can you post the code? You seem to be accessing at an invalid position when resultSet.next () is true. Either a wrong row or a wrong column (are you using column-index or column-names?).
ASKER
This is the code. Line 31 is marked.
<%@ page language="Java" contentType="text/html" %>
<%@ page import="java.util.*,java.s ql.*" %>
<% roundrockonline.connect.Co nnectionPo ol pool = roundrockonline.connect.Co nnectionPo ol.getInst ance();
String username="",password="",pa ss="";
int user_id=0;
Connection conn=pool.getConnection();
if(request.getParameter("u sername")! =null){
username=request.getParame ter("usern ame");
}
if(request.getParameter("p assword")! =null){
password=request.getParame ter("passw ord");
}
Statement stmt = conn.createStatement();
Statement stmt1 = conn.createStatement();
ResultSet rs1;
ResultSet rs = stmt.executeQuery("SELECT * from user_login where email_id='"+username+"'");
while(rs.next())
pass = rs.getString("password");
if(conn!=null) pool.returnConnection(conn );
if(pass.equals(password)){
session=request.getSession (true);
session.setMaxInactiveInte rval(1800) ;
line 31::: session.setAttribute("emai l_id",rs.g etString(" email_id") );
%>
<jsp:forward page="user_home.jsp" >
<jsp:param name="msg" value="Welcome to Customer section." />
</jsp:forward>
<%}
else{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<%}%>
<%@ page language="Java" contentType="text/html" %>
<%@ page import="java.util.*,java.s
<% roundrockonline.connect.Co
String username="",password="",pa
int user_id=0;
Connection conn=pool.getConnection();
if(request.getParameter("u
username=request.getParame
}
if(request.getParameter("p
password=request.getParame
}
Statement stmt = conn.createStatement();
Statement stmt1 = conn.createStatement();
ResultSet rs1;
ResultSet rs = stmt.executeQuery("SELECT * from user_login where email_id='"+username+"'");
while(rs.next())
pass = rs.getString("password");
if(conn!=null) pool.returnConnection(conn
if(pass.equals(password)){
session=request.getSession
session.setMaxInactiveInte
line 31::: session.setAttribute("emai
%>
<jsp:forward page="user_home.jsp" >
<jsp:param name="msg" value="Welcome to Customer section." />
</jsp:forward>
<%}
else{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<%}%>
ASKER
Again, as before, this is code that worked for a couple of years prior to moving my site to new host!
The problem is actually exactly what I said - you are scrolling out of the ResultSet and then accessing it. Notice your while loop. It ends at:
>> pass = rs.getString("password");
You will keep reading into pass from rs. You need to put braces to access the same row-position in rs.getString ( "email_id" ) ;
while(rs.next())
{ // -> BRACE ADDED
pass = rs.getString("password");
if(conn!=null) pool.returnConnection(conn );
if(pass.equals(password)){
session=request.getSession (true);
session.setMaxInactiveInte rval(1800) ;
session.setAttribute("emai l_id",rs.g etString(" email_id") );
Put a closing brace for it too.
>> if(conn!=null) pool.returnConnection(conn );
I don't know why you have that line. Maybe you can remove it.
>> pass = rs.getString("password");
You will keep reading into pass from rs. You need to put braces to access the same row-position in rs.getString ( "email_id" ) ;
while(rs.next())
{ // -> BRACE ADDED
pass = rs.getString("password");
if(conn!=null) pool.returnConnection(conn
if(pass.equals(password)){
session=request.getSession
session.setMaxInactiveInte
session.setAttribute("emai
Put a closing brace for it too.
>> if(conn!=null) pool.returnConnection(conn
I don't know why you have that line. Maybe you can remove it.
ASKER
Where should the close bracket be placed?
else{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<% } } %> // --> HERE - ADDED
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<% } } %> // --> HERE - ADDED
ASKER
You are the greatest! I cannot believe that I can get such GREAT help at the drop of a hat!!!!!! I wish I had knowledge to pass back to you guys that help all of us out here!!
That worked... I don't understand what happened that the code worked before and not now....????
That worked... I don't understand what happened that the code worked before and not now....????
What happened before was:
while ( rs.next () )
pass = rs.getString ( "...." ) ;
- the while loop ended here and the ResultSet moved out of position.
Outside the while loop, you are accessing rs.getString ( "email_id" ) at a wrong cursor position.
If you put a brace:
while ( rs.next () )
{
pass = ....
..
..
rs.getString ( "email_id" ) ; // still accesses the same cursor-position of rs
}
while ( rs.next () )
pass = rs.getString ( "...." ) ;
- the while loop ended here and the ResultSet moved out of position.
Outside the while loop, you are accessing rs.getString ( "email_id" ) at a wrong cursor position.
If you put a brace:
while ( rs.next () )
{
pass = ....
..
..
rs.getString ( "email_id" ) ; // still accesses the same cursor-position of rs
}
ASKER
1 problem...now when I enter a user name and password that does not exist I don't get to the screen that says to Please enter a valid Username and Password...I simply get a blank screen...sorry
Post the updated code.
ASKER
<%@ page language="Java" contentType="text/html" %>
<%@ page import="java.util.*,java.s ql.*" %>
<% roundrockonline.connect.Co nnectionPo ol pool = roundrockonline.connect.Co nnectionPo ol.getInst ance();
String username="",password="",pa ss="";
int user_id=0;
Connection conn=pool.getConnection();
if(request.getParameter("u sername")! =null){
username=request.getParame ter("usern ame");
}
if(request.getParameter("p assword")! =null){
password=request.getParame ter("passw ord");
}
Statement stmt = conn.createStatement();
Statement stmt1 = conn.createStatement();
ResultSet rs1;
ResultSet rs = stmt.executeQuery("SELECT * from user_login where email_id='"+username+"'");
while(rs.next()){
pass = rs.getString("password");
//if(conn!=null) pool.returnConnection(conn );
if(pass.equals(password)){
session=request.getSession (true);
session.setMaxInactiveInte rval(1800) ;
session.setAttribute("emai l_id",rs.g etString(" email_id") );
%>
<jsp:forward page="user_home.jsp" >
<jsp:param name="msg" value="Welcome to Customer section." />
</jsp:forward>
<%}
else{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<%}}%>
<%@ page import="java.util.*,java.s
<% roundrockonline.connect.Co
String username="",password="",pa
int user_id=0;
Connection conn=pool.getConnection();
if(request.getParameter("u
username=request.getParame
}
if(request.getParameter("p
password=request.getParame
}
Statement stmt = conn.createStatement();
Statement stmt1 = conn.createStatement();
ResultSet rs1;
ResultSet rs = stmt.executeQuery("SELECT * from user_login where email_id='"+username+"'");
while(rs.next()){
pass = rs.getString("password");
//if(conn!=null) pool.returnConnection(conn
if(pass.equals(password)){
session=request.getSession
session.setMaxInactiveInte
session.setAttribute("emai
%>
<jsp:forward page="user_home.jsp" >
<jsp:param name="msg" value="Welcome to Customer section." />
</jsp:forward>
<%}
else{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<%}}%>
Does it enter the else part?
<%}
else{
System.out.println ( "Invalid password. " ) ; // DOES THIS GET PRINTED - just check
%>
<%}
else{
System.out.println ( "Invalid password. " ) ; // DOES THIS GET PRINTED - just check
%>
ASKER
yes that shows up in the stderr.log....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
boolean isValid = false;
while(rs.next()){
pass = rs.getString("password");
//if(conn!=null) pool.returnConnection(conn );
if(pass.equals(password)){
session=request.getSession (true);
session.setMaxInactiveInte rval(1800) ;
session.setAttribute("emai l_id",rs.g etString(" email_id") );
isValid = true;
}
else{
isValid = false;
}
}
if (isValid)
{
%>
<jsp:forward page="user_home.jsp" >
<jsp:param name="msg" value="Welcome to Customer section." />
</jsp:forward>
<%
}
else
{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<%
}
%>
This would help you even when there is no users found.
while(rs.next()){
pass = rs.getString("password");
//if(conn!=null) pool.returnConnection(conn
if(pass.equals(password)){
session=request.getSession
session.setMaxInactiveInte
session.setAttribute("emai
isValid = true;
}
else{
isValid = false;
}
}
if (isValid)
{
%>
<jsp:forward page="user_home.jsp" >
<jsp:param name="msg" value="Welcome to Customer section." />
</jsp:forward>
<%
}
else
{
%>
<jsp:forward page="user_login.jsp" >
<jsp:param name="msg" value="Please enter a valid Username and Password." />
</jsp:forward>
<%
}
%>
This would help you even when there is no users found.
The other way would be to use a flag and to do it outside the while loop by checking the flag.
Oh sorry, Muruga has also done that.
ASKER
It works....use the code from above and replaced my code with the use of Muruga's code.
How do you want me to post the points? Mayankeagle has helped me through all of this and his code works too.
How do you want me to post the points? Mayankeagle has helped me through all of this and his code works too.
If you want to give Muruga some points, you can post a question "Points for mmuruganandam" and allot some points to that question. When he posts a comment on it, you can accept it as answer. Be sure to give a link to this question on that one, and also post a comment on this one once you have done that.