Update

From Apache OpenOffice Wiki
Jump to: navigation, search



An update is the modification of a column value in the current row. Suppose the price of orange juice is lowered to 0.99. Using the example above, the update would look like this:

  stmt.executeUpdate("UPDATE SALES SET PRICE = 0.99" +
      "WHERE SALENR = 4");

The following code fragment shows another way to accomplish the same update, this time using SDBC:

  rs.last();
  XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class, rs);
  updateRow.updateFloat(2, (float)0.99);

Update operations in the SDBC API affect column values in the row where the cursor is positioned. In the first line, the ResultSet rs calls last() to move the cursor to the last row where the column NAME has the value Orange juice. Once the cursor is on the last row, all of the update methods that are called operate on that row until the cursor is moved to another row.

The second line changes the value of the PRICE column to 0.99 by calling updateFloat(). This method is used because the column value we want to update is a float in Java programming language.

The updateXXX() methods in com.sun.star.sdbc.XRowUpdate take two parameters: the number of the column to update and the new column value. There are specialized updateXXX() methods for each data type, such as updateString() and updateInt(), just like the getXXX methods discussed above.

At this point, the price in rs for Orange juice is 0.99, but the price in the table SALES in the database is still 1.50. To ensure the update takes effect in the database and not just the result set, the com.sun.star.sdbc.XResultSetUpdate method updateRow() is called. Here is what the code should look like to update rs and SALES:

  rs.last();
 
  XRowUpdate updateRow = (XRowUpdate)UnoRuntime.queryInterface(XRowUpdate.class, rs);
  updateRow.updateFloat(2, (float)0.99);
  XResultSetUpdate updateRs = (XResultSetUpdate)UnoRuntime.queryInterface(XResultSetUpdate.class, rs);
 
  // update the data in DBMS
  updateRs.updateRow();

If the cursor is moved to a different row before calling updateRow(), the update is lost. The update can be canceled by calling cancelRowUpdates(), for instance, the price should have been 0.79 instead of 0.99. The cancelRowUpdates() has to be invoked before invoking updateRow(). The cancelRowUpdates() does nothing when updateRow() has been called. Note that cancelRowUpdates cancels all the updates in a row, that is, if there were more than one updateXXX method in the row, they are all canceled. The following code fragment cancels the update to the price column to 0.99, and then updates it to 0.79:

  rs.last();
 
  updateRow.updateFloat(2, (float)0.99);
  updateRs.cancelRowUpdates();
  updateRow.updateFloat(2, (float)0.79);
  updateRs.updateRow();

In the above example, only one column value is updated, but an appropriate updateXXX() method can be called for any or all of the column values in a single row. Updates and related operations apply to the row where the cursor is positioned. Even if there are many calls to updateXXX methods, it takes only one call to the method updateRow() to update the database with all changes made in the current row.

To update the price for beef as well, move the cursor to the row containing that product. The row for beef immediately precedes the row for orange juice, so the method previous() can be called to position the cursor on the row for Beef. The following code fragment changes the price in that row to 10.79 in the result set and underlying table in the database:

  rs.previous();
 
  updateRow.updateFloat(2, (float)10.79);
  updateRs.updateRow();

All cursor movements refer to rows in a ResultSet object, not to rows in the underlying database. If a query selects five rows from a database table, there are five rows in the result set with the first row being row 1, the second row being row 2, and so on. Row 1 can also be identified as the first row, and in a result set with five rows, row 5 is the last.

The order of the rows in the result set has nothing to do with the physical order of the rows in the underlying table. In fact, the order of the rows in a database table is indeterminate. The DBMS keeps track of which rows were selected, and it makes updates to the proper rows, but they may be located anywhere in the table physically. When a row is inserted, there is no way to know where in the table it was inserted.

Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages