JDBC returns an empty ResultSet (rs.isBeforeFirst() == true) although the table isn't empty

Question

I am trying to complete a DB access method for my Web Service. The service and the DB access methods work fine for all other tables in the DB, but this one particular method does not. When I query the DB, the ResultSet always returns empty (meaning isBeforeFirst() == true).

After many tries I cut my query to a simple SELECT * FROM VIDEOS to see if the problem is some difference between the data I entered and the data I use in my query, but even this simple query to select all items in the table didn't return any result.

This is the method I use to pull info from the DB:

public static Object[] getVideo(String phonenum, String timeStamp)
{
    Connection c = null;
    Statement stmt = null;
    Object[] result = null;
    try
    {
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:lineappDB.db");
        c.setAutoCommit(false);
        System.out.println("Opened database successfully");
        stmt = c.createStatement();
        String query = String.format("SELECT * FROM VIDEOS");
        ResultSet rs = stmt.executeQuery(query);

        // If no data was found
        if (rs.isBeforeFirst())
        {
            rs.close();
            stmt.close();
            c.close();
            return null;
        } else
        {
            result = new Object[6];
            while (rs.next())
            {
                result[0] = rs.getInt(1);
                result[1] = rs.getString(2);
                result[2] = rs.getString(3);
                result[3] = rs.getString(4);
                result[4] = rs.getString(5);
                result[5] = rs.getInt(6);
            }
        }
        rs.close();
        stmt.close();
        c.close();
    } catch (Exception e)
    {
        try
        {
            if (!c.isClosed())
            {
                c.commit();
                c.close();
            }
        } catch (Exception ex)
        {
            System.err.println(ex.getClass().getName() + ": " + ex.getMessage());
            return null;
        }
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        return null;
    }
    System.out.println(String.format("Succesfully pulled from DB - %s %s", result[1], result[2]));
    return result;
}

Any help will be very appreciated.

CLARIFICATION EDIT: The method is part of a web service that pulls a path of a certain video from the DB, to be sent to a client. The videos are uploaded by clients and are then stored in the filesystem, and their paths are stored in the DB itself.

Once I see the DB works, I will replace SELECT * FROM VIDEOS with SELECT * FROM VIDEOS WHERE PHONENUM = '%s' AND DATETIME = '%s'", phonenum, timeStamp so that the query pulls the exact item I need.

Answer 1

isBeforeFirst() returns true if the next call to next() will put the cursor on the first row of the ResultSet. In other words, any successful query that has data, once executed, will produce a ResultSet with isBeforeFirst() returning true.

Just remove that block from your code, and have the rs.next() loop deal with potentially empty ResultSets:

try
{
    Class.forName("org.sqlite.JDBC");
    c = DriverManager.getConnection("jdbc:sqlite:lineappDB.db");
    c.setAutoCommit(false);
    System.out.println("Opened database successfully");
    stmt = c.createStatement();
    String query = String.format("SELECT * FROM VIDEOS");

    result = new Object[6];
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next())
    {
        result[0] = rs.getInt(1);
        result[1] = rs.getString(2);
        result[2] = rs.getString(3);
        result[3] = rs.getString(4);
        result[4] = rs.getString(5);
        result[5] = rs.getInt(6);
    }
} 
/* catch and finally snipped for clarity of the answer */