Working with columns

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

A Column is the collection of one type of information (for example, a collection of phone numbers or job titles). A collection of Column components are managed by a StorageDataSet.

A Column object can be created explicitly in your code, or generated automatically when you instantiate the StorageDataSet subclass, for example, by a QueryDataSet when a query is executed. Each Column contains properties that describe or manage that column of data. Some of the properties in Column hold metadata (defined below) that is typically obtained from the data source. Other Column properties are used to control its appearance and editing in data-aware components.

Note: Abstract or superclass class names are often used to refer generally to all their subclasses. For example, a reference to a StorageDataSet object implies any one (or all, depending on its usage) of its subclasses QueryDataSet, TableDataSet, ProcedureDataSet, and DataSetView.

Understanding Column properties and metadata

Most properties on a Column can be changed without closing and re-opening a DataSet. However, the following properties cannot be set unless the DataSet is closed:

The UI designer will do live updates for Column display-oriented properties such as color, width, and caption. For more information on obtaining metadata, see "Obtaining metadata." For more discussion on obtaining actual data, see "Obtaining actual data."

Non-metadata Column properties

Columns have additional properties that are not obtained from metadata that you may want to set, for example, caption, editMask, displayMask, background and foreground colors, and alignment. These types of properties are typically intended to control the default appearance of this data item in data-aware components, or to control how it can be edited by the user. The properties you set in an application are usually of the non-metadata type.

Viewing column information in the Column designer

One way to view column properties information is by using the Column designer. The Column designer displays information for selected properties, such as the data type for the column, in a navigable table. Changing, or setting, a property in the Column designer makes a column persistent. The column properties can be modified in the Column designer or in the Inspector. You can change which properties display in the Column designer by clicking the Properties button.

To display the Column designer,

  1. Open any project that includes a DataSet object. In this example, select /samples/DataExpress/QueryProvider/QueryProvider.jpr from your JBuilder installation.

  2. Double-click the file QueryProvideFrame.java file and click the Design tab from the bottom of the right pane of the AppBrowser.

  3. Right-click the queryDataSet1 object in the content pane, select Activate Designer. This displays the Column designer for the data set in the Design window. The Column designer looks like this for the EMPLOYEE sample table:

To set a property for a column, select that Column and enter or select a new value for that property. The Inspector updates to reflect the properties (and events) of the selected column. For example,

  1. Select the Properties button, and select the min property to display in the Column designer.
  2. Scroll to the min column, enter today's date for the HIRE_DATE field.
  3. Press Enter to change the value.

To close the Column designer, select any UI component in the content pane, or right-click a different component, and select Activate Designer. In other words, the only way to close one designer is to open a different one.

See the topic "Ensuring data persistence" for more information on using the Column designer.

The Generate RowIterator Class button

The RowIterator Generator in the Column designer can be used to create a new RowIterator class or update an existing RowIterator class for a DataSet. It looks at the columnName property of all the Columns in the DataSet, and generates get and set methods for each column.

Selecting the RowIterator Generator button opens a dialog that provides lightweight (low memory usage and fast binding) iteration capabilities to ensure static type-safe access to columns.

The options in the RowIterator dialog have the following purposes:
RowIterator Generator dialog
Option Description
Extend RowIterator If set, the generated class will extend RowIterator. This will surface all methods in RowIterator. If this is false, a new class with a RowIterator member will be created, and which is delegated for all operations. The advantage of not extending RowIterator is that your iterator class can control what gets exposed. The advantage of extending RowIterator is that less code needs to be generated due to the fact that binder and navigation methods are inherited and do not need to be delegated to.
Remove Underscore; Capitalize Next Letter This affects how the get and set method names are generated from the columnName property of the Column. If this option is set, underscores are removed and the character following the underscore is capitalized.
Generate binder methods Generates delegator methods to call the embedded RowIterator bind methods.
Generate navigation methods Generates delegator methods to call the embedded RowIterator navigation methods.

For more information on RowIterators, see the DataExpress Component Library Reference.

Using the Column designer to persist metadata

Pressing the Persist All Metadata button in the Column designer will persist all the metadata that is needed to open a QueryDataset at run time.

The source will be changed with these settings:

JBuilder fetches metadata automatically. Because some JDBC drivers are slow at responding to metadata inquiries, you might want to persist metadata and tell DataExpress not to fetch it. With JBuilder setting this up at design time, and generating the necessary code for run time, performance will be improved.

See also: "Persisting metadata of a query"

Making metadata dynamic using the Column designer

WARNING: Pressing the Make All Metadata Dynamic button will REMOVE CODE from the source file. It will remove all the code from the property settings mentioned in the previous topic, as well as any settings of the metadata-related properties named above. However, other properties, like editMask will not be touched.

Note: To update a query after the table may have changed on the server, you must first make the metadata dynamic, then persist it, in order to use new indices created on the database table.

Viewing column information in the JDBC Explorer

The JDBC Explorer is an all-Java, hierarchical database browser that also allows you to edit data. It presents JDBC-based meta-database information in a two-paned window. The left pane contains a tree that hierarchically displays a set of databases and its associated tables, views, stored procedures, and metadata. The right pane is a multi-page display of descriptive information for each node of the tree.

To display the JDBC Explorer, select Tools|JDBC Explorer from the JBuilder menu.

JDBC Explorer

When a database URL is opened, you can expand the tree to display child objects. Columns are child objects of a particular database table. As in the figure above, when the Column object is selected for a table, the Summary page in the right pane lists the columns, their data type, size, and other information.

Select a column in the left pane to see just the information for that field, as in the figure below.

JDBC Explorer

For more information on using the JDBC Explorer, see its online help.

Optimizing a query

Setting column properties

You can set Column properties through the JBuilder visual design tools or in code manually. Any column that you define or modify through the visual design tools will be persistent.

Persistent columns

A persistent column is a Column object which was already part of a StorageDataSet, and whose persist property was set to true before data was provided. If the persist property is set after data is provided, you must perform another setQuery command with a new queryDescriptor for the application to recognize that the columns are persistent. A persistent Column allows you to keep Column property settings across a data-provide operation. A persistent column does not cause the data in that column of the data rows to freeze across data provide operations.

Normally, a StorageDataSet automatically creates new Column objects for every column found in the data provided by the data source. It discards any Column objects that were explicitly added previously, or automatically created for a previous batch of data. This discarding of previous Column objects could cause you to lose property settings on the old Column which you might want to retain.

To avoid this, mark a Column as persistent by setting its persist property to true. When a column is persistent, the Column is not discarded when new data is provided to the StorageDataSet. Instead, the existing Column object is used again to control the same column in the newly-provided data. The column matching is done by column name.

Any column that you define or modify through the visual design tools will be persistent. Persistent columns are discussed more thoroughly in "Ensuring data persistence. You can create Column objects explicitly and attach them to a StorageDataSet, using either addColumn() to add a single Column, or setColumns() to add several new columns at one time.

When using addColumn, you must set the Column to persistent prior to obtaining data from the data source or you will lose all of the column's property settings during the provide. The persist property is set automatically with the setColumns method.

Note: The UI designer calls the StorageDataSet.setColumns() method when working with columns. If you want to load and modify your application in the UI designer, use the setColumns method so the columns are recognized at design time. At run time, there is no difference between setColumns and addColumn.

Combining live metadata with persistent columns

During the providing phase, a StorageDataSet first obtains metadata from the data source, if possible. This metadata is used to update any existing matching persistent columns, and to create other columns that might be needed. The metaDataUpdate property of the StorageDataSet class controls the extent of the updating of metadata on persistent columns.

Removing persistent columns

This section describes how to undo column persistence so that a modified query no longer returns the (unwanted) columns in a StorageDataSet.

When you have a QueryDataSet or TableDataSet with persistent columns, you declare that these columns will exist in the resulting DataSet whether or not they still exist in the corresponding data source. But what happens if you no longer want these persistent columns?

When you alter the query string of a QueryDataSet, your old persistent columns are not lost. Instead, the new columns obtained from running the query are appended to your list of columns. You may make any of these new columns persistent by setting any of their properties.

Note: When you expand a StorageDataSet by clicking its expand icon in the content pane, the list of columns does not change automatically when you change the query string. To refresh the columns list based on the results of the modified query, double click the QueryDataSet in the content pane. This executes the query again and appends any new columns found in the modified query.

To delete a persistent column you no longer need, select it in the content pane and press the Delete key, or select the column in the Column designer and click the Delete button on the toolbar. This causes the following actions:

To verify that a deleted persistent column is no longer part of the QueryDataSet, double-click the data set in the content pane. This re-executes the query and displays all the columns in the resulting QueryDataSet

Using persistent columns to add empty columns to a DataSet

On occasion you may want to add one or more extra columns to a StorageDataSet, columns that are not provided from the data source and that are not intended to be resolved back to the data source. For example, you might

In such cases, you can explicitly add a Column to the DataSet, before or after providing data. The columnName must be unique and cannot duplicate a name that already exists in the provided data. Additionally, if you will be providing data after adding the Column, be sure to mark the Column persistent so that the Column is not discarded when new data is provided.

To add a column manually in source code, follow the instructions in "Creating Columns explicitly in code."

To add a column manually using the JBuilder visual design tools:

  1. Follow the first 3 steps in "Setting Column properties using JBuilder's visual design tools" to obtain the metadata into the columns listed in the content pane. (You can skip the steps for providing data if you want to add columns to an empty DataSet.)
  2. Select <new column>. This option appears at the bottom of the list of columns.
  3. In the Inspector, set the columnName, making sure that it is different from existing column names.
  4. Set any other properties as needed for this new column.

JBuilder creates code for a new persistent Column object and attaches it to your DataSet. The new Column exists even before the data is provided. Because its name is dissimilar from any provided column names, this Column is not populated with data during the providing phase; all rows in this Column have null values.

Controlling column order in a DataSet

When a StorageDataSet is provided data, it

This means that: