CODEWITHSUNDEEP
javamysqljdbc
Jon
Jon

Reputation: 113

Unable to get value from ResultSet

I am working on a web application using Java and MySQL.

I created a method that is supposed to return an ArrayList of the respective column name based on the various tables in the database.

However, when I debugged the method, I realised the while(rs.next()) causes an error. I used this site for reference, hence I am not sure what went wrong.

This is the code. Thanks.

// Returns the the all the columns in the table
public ArrayList getColumnName(String tableName) throws SQLException {
    ResultSet rs = null;
    List<String> columnName = new ArrayList<String>();

    Statement st = null;
    Connection con = null;

    try {
        // Get a connection from the connection factory
        con = DriverManager.getConnection("jdbc:mysql://localhost:3306/information_schema", "root", "xxxx");

        // Create a Statement object so we can submit SQL statements to the driver
        st = con.createStatement();

        StringBuilder sql = new StringBuilder("SELECT column_name FROM information_schema.columns " +
                "WHERE table_schema = 'testDB' AND table_name = '");

        sql.append(tableName).append("'");

        rs = st.executeQuery(sql.toString());

        while (rs.next()) { // getting error..
            columnName.add(rs.getString("column_name"));
        }

    } catch (SQLException ex) {
        Logger.getLogger(ModificationPage.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        if (con != null || st != null) {
            st.close();
            con.close();
        }
    }

    return (ArrayList) columnName;
}

Upvotes: 1

Views: 3218

Answers (2)

MetroidFan2002
MetroidFan2002

Reputation: 29878

According to the Javadoc of 1.6 (not sure which version of Java you're using):

Throws: SQLException - if a database access error occurs or this method is called on a closed result set

It's very, very unlikely that if you actually got to the line where rs.next() was called, that a database error occurred just then. So, the most likely result is that the result set was closed.

Please alter your code to the following and see if you still get the error on the same line:

while (!rs.isClosed() && rs.next()) { // getting error..
        columnName.add(rs.getString("column_name"));
}

Also, Holy SQL Injection Attack, Batman!

Taking the raw string as you're doing and enclosing it within single quotes leads this code to have an SQL injection vulnerability. Basically all a malicious user has to do is end your query with a single quote (') and run a query of their own afterwards.

Upvotes: 2

Cristian Meneses
Cristian Meneses

Reputation: 4041

So, the exception never happens ?

A query error should be thrown at rs = st.executeQuery(sql.toString()) if that were the case, but if it make it to whileand didn't iterate, it's because of an empty resultset

Maybe you're passing wrong arguments to the query ?

Upvotes: 1

Related Questions