My FeedDiscussionsHeadless CMS
New
Sign in
Log inSign up
Learn more about Hashnode Headless CMSHashnode Headless CMS
Collaborate seamlessly with Hashnode Headless CMS for Enterprise.
Upgrade ✨Learn more

ResultSet is not null yet resultset.next() returns false Ask

Default profile photo
Anonymous
·Feb 10, 2018

I'm trying to search tables based on a certain criteria. The SQL query returns the correct result(1 row) when executed in Oracle PL SQL Developer. In JDBC, resultset is not null yet resultset.next() returns false.

Code:

public static ArrayList<SearchRecord> searchRecords(Connection conn, int orderID) throws SQLException {

System.out.println("Order_id: " + orderID); //The correct orderID is passed
ArrayList<SearchRecord> searchResult = null;
SearchRecord searchRecord = null;
PreparedStatement search_pstm = null;
ResultSet search_rs = null;

if (conn != null) {
    // This query is successfully executed in SQL Developer and outputs a row
    String search_sql = "select d.PPS_NUM, a.BRC_ORD_ID, PROJECT_NUM, LICENSE_NUM, EXPIRY_DATE, FIRST_NAME, LAST_NAME, ADDRESS1, ADDRESS2, CITY, PROVINCE, POSTAL_CODE from VD_SHIP_AOR a, VD_BRC_ORD b, VD_CONSOL_VALID c, VD_SHIP d where a.brc_ord_id = b.brc_ord_id and b.CONSOL_VALID_ID= c.consol_valid_id and a.SHIP_ID = d.SHIP_ID and d.ORD_ID = b.ORD_ID and d.PPS_NUM = ?";
    search_pstm = conn.prepareStatement(search_sql);
    search_pstm.setInt(1, orderID);
    search_rs = search_pstm.executeQuery();
    searchResult = new ArrayList<SearchRecord>();

    if (search_rs != null) {
        System.out.println("Not null"); //Prints this
        System.out.println(search_rs.isClosed()); //Prints false
        System.out.println(search_rs.getRow()); //Prints 0

        while (search_rs.next()) { //Does not enter the while loop, why?
            System.out.println("Inside while rs.next");
            //store resultset's data into arraylist
        }
    }
}
return searchResult;

But the existing solutions are not working for me, please help me with this.

Thanks