Working with Columns

A Column is the collection of one type of information, for example, a collection of phone numbers, or job titles, and so on. 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 controls.

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.

Metadata and how it is obtained

Metadata is information about the data. Examples of metadata are column name, table name, whether the column is part of the unique row id or not, whether it is searchable, its precision, scale, and so on. This information is typically obtained from the data source. The metadata is then stored in properties of Column components for each column in the StorageDataSet.

When you obtain data from a data source (a process referred to as providing), and stored it in one of the subclasses of StorageDataSet , you typically obtain not only rows of data from the data source, but also metadata.

For example, the first time that you ask a QueryDataSet to perform a query, it typically runs two queries: one for metadata discovery and the second for fetching rows of data that your application displays and manipulates. Subsequent queries performed by that instance of QueryDataSet only do row data fetching. A query may be re-executed if rowIds need to be added later. The QueryDataSet component then creates Column objects automatically as needed at runtime. One Column is created for each column in the provided data. Each Column then gets some of its properties from the metadata, such as columnName, tableName, rowId, searchable, precision, scale, and so on.

You can suppress the initial metadata query to increase performance if your program already knows what the necessary metadata is. This is described in Suppressing the initial metadata query.

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 controls, or to control how it can be edited by the end user. The properties you set in an application are usually of the non-metadata type.

The following section describes how to set metadata and non-metadata properties in code manually or though the JBuilder visual design tools.

Setting column properties

You can set Column properties through the JBuilder visual design tools or in code manually.

Setting Column properties using JBuilder's visual design tools

The Component Inspector allows you to easily work with Column properties. To set Column properties:
  1. Open (or create) a project that contains a StorageDataSet that you want to work with. If you are creating a new project, add any associated components the StorageDataSet requires, for example, for a QueryDataSet, verify that a Database component exists in the application and all properties are correctly set.
    Tip: You can click the Test button on the Query property editor to test your query and its connection to the Database.
  2. Open the UI Designer by selecting the Frame container object and clicking the Design tab in the AppBrowser.
  3. In the Component Tree, select the StorageDataSet component.
  4. Click its plus sign (+) to expand the StorageDataSet to display its columns.
  5. Select the Column you want to work with. The Component Inspector displays the column's properties and events.
  6. In the Component Inspector, select the Properties page and set the properties you want.

Setting properties in code

To set properties manually in your source code on one or more columns in a StorageDataSet:
  1. Provide data to the StorageDataSet. For example, run a query using a QueryDataSet component.
  2. Obtain an array of references to the existing Column objects in the StorageDataSet by calling the getColumns() method of the StorageDataSet.
  3. Identify which column(s) in the array you want to work with by reading their properties, for example using the getColumnName() property of the Column component.
  4. Set the properties on the appropriate columns as needed.

Note: If you want the property settings to remain in force past the next time that data is provided, you must set the column's persist property to true. This is described in the following section.

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. A persistent Column allows you to keep (persist) 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.

Creating persistent columns using the JBuilder visual design tools

Use the JBuilder visual design tools to work with columns more quickly. In the visual design tools, you can set properties of any Column in the Component Inspector. In the Component Tree, Column objects are listed under the DataSet object to which they are attached. Expand the plus sign (+) by the DataSet to display the list of existing columns. This list also contains a <new column> entry that you can use to create new persistent Column objects in the DataSet.

To create persistent columns visually in JBuilder:

  1. Select the file that will contain, or already contains, the StorageDataSet component that you want to work with in the Component Tree.
  2. Click the Design tab in the AppBrowser to open the UI Designer.
  3. If you don't already have one, add a StorageDataSet (for example, a QueryDataSet).
  4. Add a Database component, and set its properties.
  5. Set the query properties of the QueryDataSet and attach it to the Database component. Use the Test button on the query property editor to run the query.
  6. Select the StorageDataSet in the Tree.
  7. Expand its plus sign (+) to display the list of the columns that the QueryDataSet now contains.
  8. Select any of the columns, and look in the Inspector to see the properties that a Column exposes to the visual design tools. Setting properties here causes JBuilder to:
Your program now has a persistent column, with explicitly set properties.

When working visually in the Component Tree with columns under any StorageDataSet, JBuilder creates source code that

Creating persistent Columns explicitly in code

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 that the columns are recognized at design time. At runtime, there is no difference between using either method.

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 to which updating of metadata on persistent columns is done.

Suppressing the initial metadata query

In some situations, you may want to suppress the lookup of metadata from a data source, and instead use metadata that you provide in persistent Column objects. This can offer performance benefits and allow for customizing of the metadata.

For example, when executing a query via a QueryDataSet, the metadata is obtained in an initial query that fetches no data rows, and the Column objects are given their metadata from the results. Then a second query is run to fetch the actual row data. Of this process, the initial metadata query takes some time. To speed up the performance of your program's query operation, capture the metadata in persistent columns and change the DataSet component's metaDataUpdate property to None.

Note: This is safe to do only if you are certain that the schema (structure) of your database will not change in ways that affect the critical metadata needed by your DataSet, such as rowId and tableName. If the schema does change later, your program will most likely fail to run properly.

To suppress the metadata discovery using the JBuilder's visual design tools:

  1. Finalize your database schemas and query statements for your application.
  2. Follow the first 6 steps above in Creating persistent columns using JBuilder visual design tools to obtain the metadata into the columns listed in the Component Tree.
  3. For each Column, set any property. If you don't want to change any property settings, set any property to its default value. This forces the generation of a persistent column and captures the current state of the metadata.
  4. Select the StorageDataSet object in the Component Tree.
  5. Set its metaDataUpdate property to None.

Removing persistent columns

This section describes how 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 plus (+) sign in the Component Tree, 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 Component Tree. This executes the query again and append any new columns found in the modified query.

To delete a persistent column you no longer need, select it in the Component Tree and press the Delete key. This causes the following actions:

To verify that a deleted persistent column is no longer part of the QueryDataSet, double-click it in the Component Tree. 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 which are not provided from the data source and which 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 6 steps above in Creating persistent columns using the JBuilder visual design tools to obtain the metadata into the columns listed in the Component Tree. (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 Component 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.

Creating Column components

Most of the Column components you work with are created automatically by the StorageDataSet when data is provided from its data source. In some cases, however, you may want to add a Column component to a StorageDataSet, for example, to create a calculated field.

Adding a Column through the visual design tools

  1. Open (or create) a project that contains a StorageDataSet that you want to work with. If you are creating a new project, add any associated components the StorageDataSet requires, for example, for a QueryDataSet, verify that a Database component exists in the application and all properties are correctly set.
    Note: Click the Test button on the Query property editor to test your query and its connection to the Database.
  2. Select the Frame container object and click the Design tab in the AppBrowser to open the UI Designer.
  3. In the Component Tree, select the StorageDataSet component.
  4. Expand its plus sign (+) to display the list of the Column components that the QueryDataSet contains.
  5. Select the <new column> entry that appears at the bottom of the list.
You now have a new Column whose properties can be set in the Component Inspector.

Adding a Column manually in code

You can add Column components manually in code in two ways:

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 that the columns are recognized at design time. At runtime, there is no difference between using either method.

Controlling column order in a DataSet

When a StorageDataSet is provided data, it first deletes any non-persistent columns, moving the persistent columns to the left. Then it adds additional columns on the right, as needed, from the provided data.

To control or preserve the actual order of all columns across subsequent queries, you would need to make them all persistent.

To adjust the order of persistent columns, change the order in which they are specified in the setColumns() call.