Seeing Changes in Result Sets

From Apache OpenOffice Wiki
Jump to: navigation, search



When data is modified in a ResultSet object, the change is always visible immediately. That is, if the same query is re-executed, a new result set is produced based on the data currently in a table. This result set reflects the earlier changes.

If the changes made by you or others are visible while the ResultSet object is open, is dependent on the DBMS, the driver, and the type of ResultSet object.

With a SCROLL_SENSITIVE ResultSetType object, the updates to column values are visible. As well, insertions and deletions are visible, but to ensure this information is returned, use the com.sun.star.sdbc.XDatabaseMetaData methods.

The amount of visibility for changes can be regulated by raising or lowering the transaction isolation level for the connection with the database. For example, the following line of code, where con is an active Connection object, sets the connection's isolation level to READ_COMMITTED:

  con.setTransactionIsolation(TransactionIsolation.READ_COMMITTED);

With this isolation level, the ResultSet object does not show changes before they are committed, but it shows changes that may have other consistency problems. To allow fewer data inconsistencies, raise the transaction isolation level to REPEATABLE_READ. Note that the higher the isolation level, the poorer the performance. The database and driver also limited what is actually provided. Many programmers use their database's default transaction isolation level. Consult the DBMS manual for more information about transaction isolation levels.

In a ResultSet object that is SCROLL_INSENSITIVE, changes are not visible while it is still open. Some programmers only use this type of ResultSet object to get a consistent view of the data without seeing changes made by others.

The method refreshRow() is used to get the latest values for a row straight from the database. This method is time consuming, especially if the DBMS returns multiple rows refreshRow() is called. The method refreshRow() can be valuable if it is critical to have the latest data. Even when a result set is sensitive and changes are visible, an application may not always see the latest changes that have been made to a row if the driver retrieves several rows at a time and caches them. Thus, using the method refreshRow() ensures that only up-to-date data is visible.

The following code sample illustrates how an application might use the method refreshRow() when it is critical to see the latest changes. Note that the result set should be sensitive. If the method refreshRow() with a SCROLL_INSENSITIVE ResultSet is used, refreshRow() does nothing. Getting the latest data for the table SALES is not realistic with these methods. A more realistic scenario is when an airline reservation clerk needs to ensure that the seat he is about to reserve is still available.

  XStatement stmt = con.createStatement();
 
  XPropertySet xProp = (XPropertySet)UnoRuntime.queryInterface(XPropertySet.class, stmt);
  xProp.setPropertyValue("ResultSetType",new java.lang.Integer(ResultSetType.SCROLL_SENSITIVE));
  xProp.setPropertyValue("ResultSetConcurrency", new java.lang.Integer(ResultSetConcurrency.READ_ONLY));
 
  XResultSet rs = stmt.executeQuery("SELECT NAME, PRICE FROM SALES");
 
  XRow row = (XRow)UnoRuntime.queryInterface(XRow.class, rs);
 
  rs.absolute(4);
 
  float price1 = row.getFloat(2);
  // do something ...
  rs.absolute(4);
  rs.refreshRow();
  float price2 = row.getFloat(2);
  if (price2 != price1) {
      // do something ...
  }
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages