Function Sequence Error - resultset

Hi
  I'm using VAJ 3.5 with access as backend. In my application I'm getting records from database and displaying it in the JSP. I'm populating my resultset with "Select * from table". In session bean I'm reading records from result set in groups of 10. My first fetch of 10 records works fine. when i do a next for fectching the next set of records I get the following exception java.sql.SQLException: [Microsoft][ODBC Driver Manager] Function sequence error at rs.next().
Could someone tell me whatz wrong.
thanx
vinitha
rViniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StillUnAwareCommented:
You must be not checking if rs.next() return null. And if it does return null, the next call to method will throw an exception
0
rViniAuthor Commented:
no rs is not null
0
tikiliainenCommented:
Do you use the database connection for anything else in between fetching the two chunks? If you do this then the result set will be pointing to something garbled. You really shoud read in all of the result set into a data structure in one go and close the result set to be 100% sure it is not getting garbled halfway through. To be 99% sure it is not getting garbled through the use of the same connection, go over the code again and ensure you make no calls to the database in between reads or close the result set before you finish reading from it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mayank SAssociate Director - Product EngineeringCommented:
>> rs.next() return null

It will either return true or false, nothing else. And if rs itself is null, it will throw a NullPointerException.

Vinitha,

Could you post some code? Are you, by any chance, scrolling to a wrong position (or doing an extra scroll in a loop)? Make sure you are using your Connection and Statement properly.
0
rViniAuthor Commented:

I'm including part of my code....hope its more clear

The method populateResultset  is used for populating the result set and its returned to populateGeneric(int iPrevLast, int iNextStart) function.


public java.sql.ResultSet populateResultset(String strTableName, String strFields, String strCondition )
{
      try
      {
            String strQuery;
            java.sql.Connection con = DriverManager.getConnection("jdbc:odbc:DrugDB");
            stmt = con.createStatement();
        
            
            if (strCondition.equalsIgnoreCase("NONE"))
            {
                 strQuery = "Select " + strFields + " from " + strTableName ;
            }
            else
               strQuery = "Select " + strFields + " from " + strTableName + " where " + strCondition;

          ResultSet rs = stmt.executeQuery(strQuery);

            return rs;
       
      }
        catch(Exception e)
        {
                      return null;

        }
        
}






public boolean populateGeneric(int iPrevLast, int iNextStart) {

      // This method populates the values for generic bean
      
      try  
      {

          java.util.Vector vGenericId = new java.util.Vector();
           java.util.Vector vGenericName = new java.util.Vector();

            if (iPrevLast == 0) // populate the result set - first call to session bean    
            {
                   rs =      populateResultset("Generic_master", "*","NONE","Generic_Name");
            }

            
          
            boolean bFlag = rs.next();  // Exception is thrown here
      
// read the resultset
// select only 10 records
//            while (bFlag)
            if (bFlag == true)
            {
            for ( int i = iPrevLast; i < iNextStart ; i++)
            {
                int iGenId = rs.getInt(1);
                Integer Itemp = new Integer(iGenId) ;
                String str = Itemp.toString(iGenId);

                    String strGenName = rs.getString(3);
                vGenericId.addElement(Itemp);
                vGenericName.addElement(strGenName);
                     bFlag = rs.next();
                  if (bFlag == false)
                        i = iNextStart;
                  
            }
            }
            setVGenericId(vGenericId);   //methods in session bean for storing genericid and name
            setVGenericName(vGenericName);
            stmt.close();
            return true;

      }
      catch (Exception eException)
      {
            eException.printStackTrace();
             return false;
      }
   
}


This works fine when i first call the session bean ie populating the resultset and getting the first 10 records. In the next cycle the servlet calls these methods for the next set of 10 (iPrevLast and iNextStart are populated with 10 and 20), and it fails in boolean bFlag = rs.next(); statment. And in between there is no other calls to the resultset.


Servlet code :

if (sview == null)
   {
      Context initial = new InitialContext();  
      Object objref = initial.lookup("ViewPkg/sView");  
      shome = (sViewHome)PortableRemoteObject.narrow(objref,sViewHome.class);
      sview = shome.create();
   }
      
// Call method to get vectors
      boolean bflag =  sview.populateGeneric(iPrevLast,iNextStart);

       vTemp = sview.getVGenericId();  
       vTempGName = sview.getVGenericName();
0
Mayank SAssociate Director - Product EngineeringCommented:
For a start, remove: >> stmt.close();

from your populateGeneric () code.

Also, I guess that you have the con declared inside the method and the stmt, rs declared at class level - you should have all at the same level. And after using up your ResultSet, you should close the statement and connection. But for the time being, just see if removing that line helps.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rViniAuthor Commented:
Thanks !! that was the culprit statement, it was closing the resultset. !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.