Locating data

Database application development is a feature of JBuilder Professional and Enterprise. Distributed application development is a feature of JBuilder Enterprise.

A basic need of data applications is to find specified data. This topic discusses the following two types of locates:

Locating data with a JdbNavField

The dbSwing library includes a JdbNavField component that provides locate functionality in a user-interface control. The JdbNavField includes an incremental search feature for String type columns. Its columnName property specifies the column in which to perform the locate. If not set, the locate is performed on the last column visited in the JdbTable.

If you include a JdbStatusLabel component in your application, JdbNavField prompts and messages are displayed on the status label.

The /samples/DataExpress/LocatingData subdirectory of JBuilder includes a finished example of an application that uses the JdbNavField under the project name LocatingData.jpr. This sample shows how to set a particular column for the locate operation as well as using a JdbComboBox component to enable the user to select the column in which to locate the value. The completed application looks like this:

To create this application,

  1. Create a new application by following "Retrieving data for the tutorials". This step enables you to connect to a database, read data from a table, and view and edit that data in a data-aware component.

    Check the screen shot of the running application (shown above) for the approximate positioning of components.

  2. Add a JdbNavField from the More dbSwing tab of the component palette to the UI designer. Set its dataSet property to queryDataSet1.

  3. Add a JdbComboBox from the dbSwing tab of the component palette to the UI designer.

  4. Set the items property for jdbComboBox1 to the column name values EMP_NO, FIRST_NAME, and LAST_NAME.

  5. Select the Events tab of the Inspector. Select the itemStateChanged() event for jdbComboBox1, and double-click its value field. A stub for the itemStateChanged() event is added to the source, and the cursor is positioned for insertion of the following code, which allows the user to specify the column in which to locate data.

    void jdbComboBox1_itemStateChanged(ItemEvent e) {
    jdbNavField1.setColumnName(jdbComboBox1.getSelectedItem().toString());
    jdbNavField1.requestFocus();
    }
    
    This code tests for the a change in the JdbComboBox. If it determines that a different column value is selected, the columnName property for the JdbNavField is set to the column named in the JdbComboBox. This instructs the JdbNavField to perform locates in the specified Column. Focus is then shifted to the JdbNavField so that you can enter the value to search for.

  6. Add a JdbTextArea component from the dbSwing tab. Place it next to the JdbComboBox component in the UI designer. Set its text property so that the user knows to select a column on which to locate data, for example, Select the column in which to search. Then type the value you want to locate. Look at messages on the status label for search instruction..

    Alternatively, if you want to locate only in a particular Column, you could set the JdbNavField component's columnName property to the DataSet column on which you want to locate data, for example, LAST_NAME.

  7. Add a JdbLabel from the dbSwing tab. Place it next to jdbNavField1. Set its text property to: Value to locate.

    Note: See the screen shot of the running application earlier in this section for additional instructional text.

  8. Run the application.

When you run the application, you'll notice the following behavior:

Locating data programmatically

This section explores the basics of locating data programmatically as well as conditions which affect the locate operation.

When programmatically locating data:

  1. Instantiate a DataRow based on the DataSet you want to search. If you don't want to search on all columns in the DataSet, create a "scoped" DataRow (a DataRow that contains just the columns for which you want to specify locate values). (See "Locating data using a DataRow".)
  2. Assign the values to locate in the appropriate columns of the DataRow.

  3. Call the locate(ReadRow, int) method, specifying the location options you want as the int parameter. Test the return value to determine if the locate succeeded or failed.

  4. To find additional occurrences, call locate() again, specifying a different locate option, for example, Locate.NEXT or Locate.LAST. See Locate class variables for information on all the Locate options.

The core locate functionality uses the locate(ReadRow, int) method. The first parameter, ReadRow, is of an abstract class type. Normally you use its (instantiable) subclass DataRow class. The second parameter represents the locate option and is defined in Locate variables. The Locate class variables represent options that let you control where the search starts from and how it searches, for example with or without case sensitivity. (For more information on locate options, see "Working with locate options".) If a match is found, the current row position moves to that row. All data-aware components that are connected to the same located DataSet navigate together to the located row.

The Locate() method searches within the current view of the DataSet. This means that rows excluded from display by a RowFilterListener are not included in the search.

The view of the DataSet can be sorted or unsorted; if it is sorted, the locate() method finds matching rows according to the sort sequence.

To locate a null value in a given column of a DataSet, include the column in the DataRow parameter of the locate() method but do not assign it a value.

Tip: If the locate() method fails to find a match when you think it should succeed, check for null values in some columns; remember that all columns of the DataRow are included in the search. To prevent this, use a "scoped" DataRow containing only the desired columns.

Locating data using a DataRow

A DataRow is similar to a DataSet in that it contains multiple Column components. However, it stores only one row of data. You specify the values to locate for in the DataRow.

When the DataRow is created based on the same located DataSet, the DataRow contains the same column names and data types and column order as the DataSet it is based on. All columns of the DataRow are included in the locate operation by default; to exclude columns from the locate, create a "scoped" DataRow that contains only specified columns from the DataSet. You create a "scoped" DataRow using either of the following DataRow constructors:

Both the DataRow and the DataSet are subclasses of ReadWriteRow. Both inherit the same methods for manipulation of its contents, for example, getInt(String), and setInt(String, int). You can therefore work with DataRow objects using many of the same methods as the DataSet.

Working with locate options

You control the locate operation using locate options. These are constants defined in the com.borland.dx.dataset.Locate class. You can combine locate options using the bitwise OR operator; several of the most useful combinations are already defined as constants. Four of the locate options (FIRST, NEXT, LAST, and PRIOR) determine how the rows of the DataSet are searched. The CASE_INSENSITIVE and PARTIAL) options define what is considered a matching value. The FAST constant affects the preparation of the locate operation.

You must specify where the locate starts searching and which direction it moves through the rows of the DataSet. Choose one of the following:

If one of these constants is not specified for a locate operation, a DataSetException of NEED_LOCATE_START_OPTION is thrown.

To find all matching rows in a DataSet, call the locate() method once with the locate option of FIRST. If a match is found, re-execute the locate using the NEXT_FAST option, calling the method with this locate option repeatedly until it returns false. The FAST locate option specifies that the locate values have not changed, so they don't need to be read from the DataRow again. To find all matching rows starting at the bottom of the view, use the options LAST and PRIOR_FAST instead.

The CASE_INSENSITIVE option specifies that string values are considered to match even if they differ in case. Specifying whether a locate operation is CASE_INSENSITIVE or not is optional and only has meaning when locating in String columns; it is ignored for other data types. If this option is used in a multi-column locate, the case sensitivity applies to all String columns involved in the search.

The PARTIAL option specifies that a row value is considered to match the corresponding locate value if it starts with the first characters of the locate value. For example, you might use a locate value of "M" to find all last names that start with "M". As with the CASE_INSENSITIVE option, PARTIAL is optional and only has meaning when searching String columns.

Multi-column locates that use PARTIAL differ from other multi-column locates in that the order of the locate columns makes a difference. The constructor for a scoped, multi-column DataRow takes an array of column names. These names need not be listed in the order that they appear in the DataSet. The PARTIAL option applies only to the last column specified, therefore, control over which column appears last in the array is important.

For a multi-column locate operation using the PARTIAL option to succeed, a row of the DataSet must match corresponding values for all columns of the DataRow except the last column of the DataRow. If the last column starts with the locate value, the method succeeds. If not, the method fails. If the last column in the DataRow is not a String column, the locate() method throws a DataSetException of PARTIAL_SEARCH_FOR_STRING.

Locates that handle any data type

Data stored in DataExpress components are stored in Variant objects. When data is displayed, a String representation of the variant is used. To write code that performs a generalized locate that handles columns of any data type, use one of the setVariant() methods and one of the getVariant() methods.

For example, you might want to write a generalized locate routine that accepts a value and looks for the row in the DataSet that contains that value. The same block of code can be made to work for any data type because the data stays a variant. To display the data, use the appropriate formatter class or create your own custom formatter.

Column order in the DataRow and DataSet

While a Column from the DataSet can only appear once in the DataRow, the column order may be different in a scoped DataRow than in the DataSet. For some locate operations, column order can make a difference. For example, this can affect multi-column locates when the PARTIAL option is used. For more information on this, see the paragraph on multi-column locates with the PARTIAL option earlier in this topic.