CODEWITHSUNDEEP
javajdbc
Le_Master
Le_Master

Reputation: 157

Updating database after updating resultset

I have the below code to get data from a database and update it. How ever, result set is getting updated but, I don't see the changes in the database. Even after I say commit(), database is not getting updated. What am I missing here?

  Connection connect = ConnectionManager.getInstance().getConnection();
            Statement st = connect.createStatement();

            st.execute("Select * from ...");
            ResultSet rs = st.getResultSet();
            ResultSetMetaData md = rs.getMetaData() ;
            rs.absolute(1);
            rs.updateObject("StepName","ABCD" ); 

            connect.commit();
            //connect.close();

EDIT:

Now, i have the below method, but I get the error as indicated.

            rs.updateString("StepName", "ABCD");
            rs.updateRow();
            //rs.updateObject("StepName","ABCD" );

Error: com.jnetdirect.jsql.u: sp_cursor: The cursor identifier value provided (0) is not valid.

OK I have it working now. Below is the way I had to change. Working solution:

Connection connect = ConnectionManager.getInstance().getConnection();
             Statement st = connect.createStatement(
                        ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);

            st.execute("Select * from ...");
            ResultSet rs = st.getResultSet();
            String queuename = "apples";    
            String updatedname = "bananas";

            while(rs.next())
            {

                if(rs.getString(3).equals(queuename))
                {   
                rs.updateString(3, updatedname );
                System.out.println("updated: " + rs.getString(3));
                rs.updateRow();
                }else
                {
                    System.out.println(rs.getRow() + ": " +  rs.getString(3));
                }
            }

            connect.commit();
            rs.close();
            connect.close();

        }

Upvotes: 2

Views: 1464

Answers (3)

BruceRudd
BruceRudd

Reputation: 126

When you make your call to createStatement you need to use the overload that allows you to specify an updatable resultset.

Here is an example if found: http://www.xyzws.com/Javafaq/how-to-use-updatable-resultset-in-jdbc/179

I think it's possible that not all driver\databases support this operation.

Upvotes: 1

Ajk_P
Ajk_P

Reputation: 1874

rs.absolute(1); might be not be moving the cursor. Try: rs.next();

You can try a straight up SQL statement:

StringBuffer updates = new StringBuffer();
updates.append("UPDATE table_name ");
updates.append("SET StepName = ? );
updates.append("WHERE id = ?");

Statement updateStatement = connect.createStatement(updates.toString());
updateStatement.setString(1, "BlaBla");
updateStatement.setInt(2, 143);

updateStatement.executeUpdate();

Upvotes: 1

Eran
Eran

Reputation: 393781

If you look at the spec of updateObject :

Updates the designated column with an Object value. The updater methods are used to update column values in the current row or the insert row. The updater methods do not update the underlying database; instead the updateRow or insertRow methods are called to update the database.

rs.updateObject alone doesn't update the database. Try adding a call to rs.updateRow() after updateObject.

Upvotes: 3

Related Questions