CODEWITHSUNDEEP
javamysqlloopsresultset
Kyle Walker
Kyle Walker

Reputation: 579

MySQL - Iterating over columns in a result set

I'm working on a project where a user can assemble the components of a yoga class. It's spread across several files and thus too large to put it all here. The trouble I'm having is in one method where I need to iterate over the horizontal columns of a result set that is returning only ONE ROW from a MySQL database.

I understand that I have to position the cursor on the first row of the result set (which I believe I am doing). Since I have only one row in the result set (my variable is rset), I should be using rset.next() only one time, correct? And then I should be able to use a simple loop to iterate over each column and append the value to my String Builder. I want to skip the first column and append each subsequent value until the loop reaches columns with null values. I cannot find why my code keeps returning a "Before start of result set" exception.

Can anyone spot anything wrong?

I'll post the method as well as the method called by this method. (I posted this in a different question, but I believe the nature of my question has changed, so I'm re-posting this with a different title.)

    // Query that returns the poses within a specific section
public String listPosesInSection(String tableName, String sectionName) {
    String strList;
    StringBuilder strBuilderList  = new StringBuilder("");
    // Run the query
    try {
        statement = connection.createStatement();
        // Query will collect all columns from one specific row
        rset = statement.executeQuery("SELECT * FROM " + tableName + " WHERE " + tableName + "_name = '" + sectionName + "'");
        rset.next();  
        System.out.println("Column count is " + countColumnsInTable(tableName));
        for (int i = 2; i <= countColumnsInTable(tableName); i++) {// First value (0) is always null, skip first column (1)
            System.out.println("test");
            strBuilderList.append(rset.getString(i) + "\n"); // This is line 126 as indicated in the exception message
            }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    strList = strBuilderList.toString();
    return strList.replaceAll(", $",""); // Strips off the trailing comma
}

// Method for getting the number of columns in a table using metadata
public int countColumnsInTable(String sectionType) {
    int count = 16;
    try {
        statement = connection.createStatement();
        rset = statement.executeQuery("SELECT * FROM " + sectionType);
        rsMetaData = rset.getMetaData();
        count = rsMetaData.getColumnCount();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return count;
}

And here is the first part of the exception message:

Column count is 26
java.sql.SQLException: Before start of 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.getStringInternal(ResultSetImpl.java:5773)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5693)
at YogaDatabaseAccess.listPosesInSection(YogaDatabaseAccess.java:126)
at YogaSectionDesigner$5.actionPerformed(YogaSectionDesigner.java:231)

Upvotes: 0

Views: 8881

Answers (3)

prvn
prvn

Reputation: 916

rset is a ResultSet object

i think you are using the same ResultSet object in listPosesInSection and also in countColumnsInTable

so what is happening here is in listPosesInSection rset holds a result and you have also move the cursor but again in countColumnsInTable you are using the same rset so it is overwritten ie it holds a new result now, you are getting the number of columns but since it holds a new result now the cursor will be before the 1st record so use different Resultset object in countColumnsInTable

Upvotes: 1

NPKR
NPKR

Reputation: 5496

try this

 public String listPosesInSection(String tableName, String sectionName) {
        String strList;
        StringBuilder strBuilderList  = new StringBuilder("");
        // Run the query
        try {
            statement = connection.createStatement();
            // Query will collect all columns from one specific row
            rset = statement.executeQuery("SELECT * FROM " + tableName + " WHERE " + tableName + "_name = '" + sectionName + "'");
            while (rset.next()){
            System.out.println("Column count is " + countColumnsInTable(tableName));
            for (int i = 2; i <= countColumnsInTable(tableName); i++) {// First value (0) is always null, skip first column (1)
                System.out.println("test");
                strBuilderList.append(rset.getString(i) + "\n"); 
                }
             }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        strList = strBuilderList.toString();
        return strList.replaceAll(", $",""); // Strips off the trailing comma
    }

Upvotes: 1

NilsH
NilsH

Reputation: 13821

Looks to me like you're re-using the rset between your two methods. So when countColumnsInTable has completed, the rset variable is pointing to a different result set than it was before, in listPosesInSection. And that result set has not been advanced with next, hence the error message you're getting. You probably want to assign it to a local ResultSet within that method instead.

public int countColumnsInTable(String sectionType) {
    int count = 16;
    try {
        Statement statement = connection.createStatement();
        ResultSet rset = statement.executeQuery("SELECT * FROM " + sectionType);
        ResultSetMetaData rsMetaData = rset.getMetaData();
        count = rsMetaData.getColumnCount();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    // Remember to clean up
    return count;
}

Upvotes: 4

Related Questions