0

I am fetching two columns from a table in MySql using preparedStatement in Java.The query is valid and returns rows if I execute it on mySql DB directly. but for this method call, ResultSet has values "false" on each ResultSet.next() call. Below is the code:

private ResultSet fetchFriends(int lActProfId) throws SQLException {
        // TODO Auto-generated method stub
        String selectRelationQry= "SELECT friend_id, type FROM fs_relationship WHERE profile_id =?";
        System.out.println("---- In method FetchAndInsertInWall()->fetchFriends - Qry: "+selectRelationQry);
        pst = lCon.prepareStatement(selectRelationQry);
        pst.setInt(1, lActProfId);
        System.out.println("lActProfId: "+ lActProfId );
        ResultSet res3 = pst.executeQuery();
        System.out.println("---- temp--- In method FetchAndInsertInWall()->fetchFriends query has no Results");
        if(!res3.next()){
            System.out.println("---- In method FetchAndInsertInWall()->fetchFriends query has no Results");
        } else {System.out.println("Elements in res3: "+res3.next() +  res3.next() + res3.next());
        }
        pst = null;
        return res3;
    }

I am getting following output on console:

---- In method FetchAndInsertInWall()->fetchFriends - Qry: SELECT friend_id, type FROM fs_relationship WHERE profile_id =?
lActProfId: 5002
---- temp--- In method FetchAndInsertInWall()->fetchFriends query has no Results
Element in res31: false
----> WallInsertions Failed for Uid: 5002
java.sql.SQLException: Illegal operation on empty result set.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:855)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2710)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2851)
    at com.fs.javadbact.FetchDataAndInsertInWall.fetchAndInsertInWall(FetchDataAndInsertInWall.java:64)
    at com.fs.javadbact.ConsumerTest.run(ConsumerTest.java:86)
    at java.lang.Thread.run(Unknown Source)
Jan 12, 2014 3:34:57 AM com.fs.javadbact.ConsumerTest run

Please help me how to resolve this and get proper ResultSet with correct data.

AbhiBlr
  • 1
  • 1
  • You have a `system.out` saying no results before you ask, and besides you are using `.next` three times later – nachokk Jan 11 '14 at 22:36
  • I don't get what you want ?! you couldnt get any value from your db which would eventually cause an error by clicking next() (iterating in your results) if you don't have any – Yehia Awad Jan 11 '14 at 22:38
  • If the exact same query, with the same argument, returns rows in your db browsing tool, then it means that either the db browsing tool is returning rows that have been inserted but not committed yet, or that the code doesn't use the same database/schema as the database browsing tool. – JB Nizet Jan 11 '14 at 22:42
  • What is the sense in `res3.next() + res3.next() + res3.next()`? That code does not make any sense. It only advances the resultset for three **rows** without actually fetching any data (also: are you sure that your result does contain at least 4 rows?) –  Jan 11 '14 at 22:58
  • Thanks for comments. Sorry If my question was not clear enough. I am still at learning stage of java so may have some mistake in code. I think I found the cause of problem, I have used multiple ResultSets in the same connection. I tried it with multiple preparedStatements on same connection but its still failing when I access 1st ResultSet after fetching 2nd ResultSet. Kindly guide through this. – AbhiBlr Jan 12 '14 at 17:10

2 Answers2

0

I think you've forgotten to declare PreparedStatement before pst variable
write :
PreparedStatement pst = lCon.prepareStatement(selectRelationQry);
rather than : 

pst = lCon.prepareStatement(selectRelationQry);
mounaim
  • 1,132
  • 7
  • 29
  • 56
0

Use getString() instead of next() to get the values of the row.

If your result has less than three rows you will exhaust the resultset. I do not know if the next() is required to work after returning false once.

Thorbjørn Ravn Andersen
  • 73,784
  • 33
  • 194
  • 347