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.
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
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.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
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).|