Java SQL ResultSet Exception

Question

I'm Developing a Web Application using JSF & PrimeFaces. I have two classes Inputs & DBConn. And i'm executing a SQL command like this

SQL="SELECT COUNT(*) FROM TXN_HEADER WHERE REQUEST_DATE='01-AUG-2014'"

And then in the DBConn class i call the next() method on the ResultSet object inside a while loop condition to print the result.

This works fine and gives me an output.

But when i try to call that ResultSet object to get an output using the same scenario above (using that while loop inside the Inputs class) it gives me the following exception.

java.sql.SQLException: Result set after last row
at oracle.jdbc.driver.GeneratedScrollableResultSet.getString(GeneratedScrollableResultSet.java:879)
at Inputs.commandButton(Inputs.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java:245)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:277)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
at javax.faces.component.UICommand.broadcast(UICommand.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:526)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:655)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

Then i assumed that after the first use of the next() method the cursor is still in the end though i used the next() method again in that Inputs class.

So i tried using the .beforeFirst() method before going to the 2nd while loop to reset the cursor back to the top. But it gave me the same error again.

Then i tried commenting the 1st while loop i access in the DBConn class so the cursor is at the top for sure. But then it gives me the following error.

java.sql.SQLException: ResultSet.next was not called
at oracle.jdbc.driver.GeneratedScrollableResultSet.getString(GeneratedScrollableResultSet.java:874)
at Inputs.commandButton(Inputs.java:41)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java:245)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:277)
at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
at javax.faces.component.UICommand.broadcast(UICommand.java:315)
at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:136)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:526)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:655)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1566)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1523)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)

How do i resolve this? This is my first time developing a Database Application. And sorry about these bunch of sys.prints, I used them for debugging.

These are the two classes related to this case:

Inputs Class

import java.sql.SQLException;

import javax.annotation.ManagedBean;

@ManagedBean
public class Inputs {
private String date;
private String showRes;

public String getDate() {
    return date;    
}

public void setDate(String date) {
    this.date = date;
}

public String getShowRes() {
    return showRes;
}

public void setShowRes(String showRes) {
    this.showRes=showRes;
}

public void commandButton(){

    DBConn nCon=new DBConn();

    Calculations nCalc=new Calculations();
    nCalc.setPieChartSQL(getDate());
    nCon.setSQL(nCalc.getPieChartSQL());

    System.out.println("............");
    System.out.println(nCon.getResultSet());
    System.out.println("............");
    System.out.println(nCon.getResultSet());
    System.out.println("............");

    try {
        System.out.println(nCon.getResultSet().getString(1));
        //nCon.getResultSet().beforeFirst();
        while(nCon.getResultSet().next()){
            System.out.println("++++++++++++");
            System.out.println(nCon.getResultSet().getString(1));
            System.out.println("++++++++++++");
            setShowRes(nCon.getResultSet().getString(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println("777777777777777777777");
}

}

DBConn Class

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

public class DBConn {

private String sql;
private ResultSet rs;

public ResultSet getResultSet(){

    try {
        OracleDataSource dataSource=new OracleDataSource();
        dataSource.setURL("jdbc:oracle:thin:@172.16.20.45:1521:ABS");
        dataSource.setUser("<user>");
        dataSource.setPassword("<pass>");

        OracleConnection con = (OracleConnection)dataSource.getConnection();
        con.setAutoCommit(false);

        Statement stmnt = con.createStatement();
        rs = stmnt.executeQuery(sql);
        System.out.println("-----------");
        System.out.println(rs);
        System.out.println("-----------");
        System.out.println(rs);
        System.out.println("-----------");

        while(rs.next()){
            System.out.println("*************");
            System.out.println(rs.getString(1));
            System.out.println("*************");
        }


    } catch (SQLException e) {
        e.printStackTrace();
    }

    return rs;
}

public void setSQL(String sql) {
    this.sql=sql;
}

}

Just an observation, REQUEST_DATE='01-AUG-2014' That is such an incorrect way of dealing with dates. You will be sooner running into performance issues due to implicit data conversion. If request date is date data type then use TO_DATE(01-AUG-2014). — Lalit Kumar B
– Lalit Kumar B, Commented Oct 17, 2014 at 6:53
@LalitKumarB I did this for testing purposes. I just wanted to see whether it'll work. BTW thanks for the heads up. — k9yosh
– k9yosh, Commented Oct 17, 2014 at 8:00

javatar · Accepted Answer · 2014-10-20 09:57:32Z

I think the problem is in the way you get the resultset. Each time you call nCon.getResultSet() it returns a new resultset. That means you call next method of one resultset object and calls getString(1) in another. Here is my solution.

public void commandButton(){

     DBConn nCon=new DBConn();

     Calculations nCalc=new Calculations();
     nCalc.setPieChartSQL(getDate());
     nCon.setSQL(nCalc.getPieChartSQL());

     System.out.println("............");
     System.out.println(nCon.getResultSet());
     System.out.println("............");
     System.out.println(nCon.getResultSet());
     System.out.println("............");

     try {
         ResultSet rst=nCon.getResultSet();
         rst.beforeFirst();
         while(rst.next()){
             System.out.println("++++++++++++");
             String str=rst.getString(1);
             System.out.println("++++++++++++");
             setShowRes(str);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println("777777777777777777777");
}

Shoaib Chikate · Accepted Answer · 2014-10-17 06:19:00Z

1

Your getResultMethod() is putting cursor at the end of the ResultSet. And this Exception occurs because you are not calling next() and trying to access the ResultSet in your below method

public void commandButton(){

    DBConn nCon=new DBConn();

    Calculations nCalc=new Calculations();
    nCalc.setPieChartSQL(getDate());
    nCon.setSQL(nCalc.getPieChartSQL());

    System.out.println("............");
    System.out.println(nCon.getResultSet());
    System.out.println("............");
    System.out.println(nCon.getResultSet());
    System.out.println("............");

    try {
        System.out.println(nCon.getResultSet().getString(1)); //here rs.next() should be called first
        //nCon.getResultSet().beforeFirst();
        while(nCon.getResultSet().next()){
            System.out.println("++++++++++++");
            System.out.println(nCon.getResultSet().getString(1));
            System.out.println("++++++++++++");
            setShowRes(nCon.getResultSet().getString(1));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println("777777777777777777777");
}

Solution

try{

    while(rs.next()){
         System.out.println(nCon.getResultSet().getString(1));
    }
......
}

answered Oct 17, 2014 at 6:19

Shoaib Chikate

8,97112 gold badges50 silver badges70 bronze badges

3 Comments

k9yosh Over a year ago

That's what i have done right? my getResultSet() returns rs object.

Shoaib Chikate Over a year ago

But in getResultSet() method you are iterating all the ResultSet and then returning the ResultSet with the cursor at end. So when you call nCon.getResultSet().getString(), Cursor is at end when you call, it doesn't give any output.

k9yosh Over a year ago

Yes, but then i omitted that while loop so the cursor is in the original place at the top. Then it gives me the 2nd error that i mentioned above.

Collectives™ on Stack Overflow

Java SQL ResultSet Exception

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related