CODEWITHSUNDEEP
javamysqljdbc
Sami Majdoub
Sami Majdoub

Reputation: 13

saving resultset to String

I'm learning MySQL and Java, and i'm trying to create a method that takes the current string in the database and changes it to something else.

My code looks like this:

public void changeStatus(int taskID) throws SQLException 
{
    try {
        // 1. Get a connection to database
        myConn = DriverManager.getConnection(dbUrl, dbUser, dbPass);

        // 2. Create a statement
        myStmt = myConn.prepareStatement("select 'status' from tasks where id=?");

        myStmt.setInt(1, taskID);

        myRs = myStmt.executeQuery();
        String statusString = myRs.getString("status");


        if (statusString != null)
        {
            myStmt = myConn.prepareStatement("update tasks set status=? where id=?");
            if (statusString.equals("New"))
            {
                myStmt.setString(1, "In Process");
                myStmt.setInt(2, taskID);
                myStmt.executeUpdate();
            }
            else if (statusString.equals("In Process"))
            {
                myStmt.setString(1, "Done");
                myStmt.setInt(2, taskID);
                myStmt.executeUpdate();
            }
            else if (statusString.equals("Done"))
            {
                myStmt.setString(1, "New");
                myStmt.setInt(2, taskID);
                myStmt.executeUpdate();
            }
        }
    }
    catch (Exception exc) {
        exc.printStackTrace();
    }
    finally {
        if (myRs != null) {
            myRs.close();
        }

        if (myStmt != null) {
            myStmt.close();
        }

        if (myConn != null) {
            myConn.close();
        }
    }
}

It says that i get java.sql.SQLException: Before start of result set. I think that the problem is in this line:

             String statusString = myRs.getString("status");

What do i have to change to make it work?

Upvotes: 1

Views: 731

Answers (2)

singhakash
singhakash

Reputation: 7919

You have to move the cursor to the next position before fetching value.

myRs.next();
String statusString = myRs.getString("status");

From the docs

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

Upvotes: 1

Jens
Jens

Reputation: 69440

You have to call myRs.next() before reading from the resultset.

Or better first check if you have an entry in your resultset:

if (myRs.hasNext()){
  myRs.next()
  String statusString = myRs.getString("status");
}

And there is an other problem in your query:

myStmt = myConn.prepareStatement("select 'status' from tasks where id=?");

you have to remove the single quote arround the column name status. It must be:

 myStmt = myConn.prepareStatement("select status from tasks where id=?");

Upvotes: 0

Related Questions