Using the Editors


Chapter 22 . Table Views and Links

Table views and links are widgets used to provide Panther with the information it needs to support database access, specifically for the transaction manager in database applications. A table view is a group of widgets on a screen, generally from the same database table. If there is more than one table view on the screen, links define the relationships between two table views.

This chapter describes using and creating table views and links, and how their property settings are used by Panther's transaction manager to effect automatic SQL generation. Refer to Chapter 33, "Using Automated SQL Generation," in Application Development Guide for details on automated SQL generation.

To use the transaction manager:


Using Table Views

A table view—since it is a group widget—is not visible on the screen in the screen editor or at runtime; however, the widgets that make up the table view are visible. The table view is a set of related widgets, usually associated with and named for a single database table. The table view must be present on the screen if you want to use the transaction manager to provide automatic database access.

A table view is automatically created on each repository entry when you import a database table into a repository. When you copy a database-derived widget from a repository entry, Panther creates a table view on the destination screen, provided the copied widget belonged to a table view in the repository entry.

You can also include additional members (widgets) in a table view that are not a part of the database table in order to display derived data.

Accessing Table View Properties

To access a table view's properties, do any of the following:

The Properties window displays the properties associated with the selected table view.

The transaction manager uses table view properties (and link properties, discussed later in this chapter) to execute its commands. Table views are identified by the following properties, many of which are automatically defined through the import process. Refer to "Populating a Repository with Database Objects" for information on the import process.

Identity Properties

The following properties define the name of the table view and its source of inheritance, and provide for additional comments and programming use.

Name
Under the Identity properties, specify the name of the table view which generally is the same as the database table. A table view that you create manually by grouping widgets, as opposed to copying it from a repository entry, is assigned a name—tview followed by a number. Table views are numbered sequentially. Rename manually created table views to something more descriptive and useful for your development needs.

Inherit From
Use this property to specify the table's source of inheritance if you manually created the table view. Use the format repositoryEntry!repositoryTable View Name. This value is provided automatically when a table view is copied from a repository entry.

Memo Text
Attach up to nine separate lines of text (memo1...memo9) for additional comments or for entering string expressions to use programmatically.

Transaction Properties

The following properties define how the selected table view is used by the transaction manager:

Updatable
Defines whether the database table participates in INSERT, UPDATE, and DELETE statements during automatic SQL generation. The possible settings are:

Function
Usually blank. Use this property to specify the name of a transaction event function which will be called instead of the transaction model for the selected table view. For more information on creating custom functions, refer to Chapter 32, "Writing Transaction Event Functions," in Application Development Guide.

Model
Usually blank. Use this property to specify the name of the transaction model to use for the selected table view or screen. When the property is left blank, the transaction manager uses the model specified at the next level.

You can specify a transaction model in the Model property under Transaction at the following levels:

Database Properties

The Table, Columns and Primary Key properties define how the selected table view is defined in the database; they are automatically set when a database table or view is imported. The Insert Handling, Update Handling and Delete Handling categories determine how processing occurs for these operations.

Table
Specifies the name of the database table. This name is provided automatically if the table view was copied from a repository entry generated by the import process. The table name is used by the SQL generator in the FROM clause of the SQL statement.

Columns
Lists the columns belonging to the database table associated with the selected table view. This information is provided automatically if the table view was copied from a repository entry generated by the import process. The column list is not used for SQL generation. It is used during help processing to provide a list of possible columns for use in the link Relations dialog box.

Primary Key
Lists the columns composing the database table's primary key. This information is generated automatically if it was available from the database engine. Choose More to see all primary keys. The columns listed are used:

Insert Handling
The following settings are available for inserting data in the transaction manager:

Update Handling
The following settings are available for updating data in the transaction manager:

Delete Handling
The following settings are available for deleting data in the transaction manager:

Server View Properties

The following properties define how the server view fetches data. A server view is either a single table view or a group of table views connected with server links. The setting for the Method property determines which sub-properties are displayed.

Select Handling
Under Method, specify how the transaction manager will fetch data:

Sort Widgets
Specify the sort order by entering a list of widget names and optional order specifiers. The order specifiers are ASC, for ascending (default), and DESC, for descending, and are case insensitive.

The SQL generator uses the widget name to determine the associated column or select expression to be sorted when generating a SQL SELECT statement.

Distinct
Defines whether duplicate rows are included or omitted from query results.

Directions (fetch_directions)
Specifies whether the following commands are available in the transaction manager (in two-tier architecture only): CONTINUE_BOTTOM, CONTINUE_DOWN, CONTINUE_TOP, and CONTINUE_UP. If they are available, the transaction manager creates a continuation file for select (query) results so that you can scroll forward and backward through the data. The following options are available:

Count Select
Determines whether the transaction manager finds the size of the result set before fetching data.

Service Properties

(For JetNet/Oracle Tuxedo only) The following properties define the type of transaction manager command that a service implements in a three-tier architecture. These Service properties are automatically set for the root table view on client screens and selection screens (if any) when you use the screen wizard to generate your screens.

If you specify these properties in the editor, they must only be set for the root table view. Check that the service component contains all needed widgets from the client screen for all server views.

Select Service
Specifies a service that implements a Select operation which retrieves information stored in a database table.

Insert Service
Specifies a service that implements an Insert operation which adds information to the database table.

Update Service
Specifies a service that implements an Update operation which allows you to make modifications to the data stored in the tables.

Delete Service
Specifies a service that implements a Delete operation which removes information from the database table.

Setting Table View Properties to Generate SQL

Panther's SQL generator uses properties found under Database and Server View in the Properties window to generate SQL statements. Panther's transaction manager—via the SQL generator—can construct SELECT, INSERT, UPDATE, and DELETE statements using the property specifications for table views on the screen. To control or change the generated SQL, you can change the Database or Server View properties for widgets representing database columns, table views, or server views.

When you copy widgets from a repository entry that was created as the result of the import process, most of the properties that the SQL generator needs are set automatically. If you want to modify the SQL, you can change the property specifications. For more information about automatically generated SQL statements, refer to Chapter 33, "Using Automated SQL Generation," in Application Development Guide.

Creating and Linking a Table View

When you populate a screen with widgets from a repository that was created as the result of the import process, a table view is automatically copied to your application screen as well. You can have multiple table views on a screen—each representing a different database table.

However, you can create table views manually if you want to:

How to Create a New Table View

  1. Create (or copy) and name the widgets that will be members of the new table view if you have not already done so.
  2. Select those widgets that are to be members of the table view.

    Note: If any widget in your selection set is currently a member of another table view, it will be removed from that table view and added to the new one. In other words, a widget cannot be a member of two different table views.

  3. Choose EditGroupCreateTable View.

    The table view is created and currently selected. It is given a default name, tview#.

  4. (Optional) Rename the table view to reflect its content (in the Name property under Identity in the Properties window).
  5. To connect the new table view to the database, create a link and set its properties accordingly:

Manipulating Table View Members

To display or identify those widgets that compose a table view on your screen, you can use either of two methods: select the table view or select one member of the table view.

How to Identify the Members of a Specific Table View

  1. Choose ViewDB Interactions.
  2. Select the table view from the DB Interactions window.
  3. Choose EditGroupSelect Members. All widgets that are members of the table view are selected.

How to Identify the Table View Members Associated with a Specific Widget

  1. Select at least one member of the table view.
  2. Choose EditGroupSelect Members. All widgets that are members of the table view are selected.

Adding Table View Members

You can add members to an existing table view to:

How to Add Widgets to a Table View

  1. Use one of the methods described above for displaying table view members.
  2. With all members still selected, select the widget or widgets you want to add to the table view. The selection set should include the table view members and any new widgets. Remember to name the new widgets.
  3. Choose EditGroup Update Group Members. The table view is now redefined.

    If you omitted a widget that was originally a table view member, it will no longer be a part of any table view. Repeat the procedure to update the table view's membership.

  4. (optional) To check which widgets make up the table view: With at least one member of the table view selected, choose EditGroupSelect Members.
  5. Choose OK.

Identifying the Root Table View

The root table view determines the basis of the event processing occurring on a screen. In most cases, the transaction manager sets the root table view automatically. A root table specification must be set (to Default or to a table view name) if you want the transaction manager to perform the screen's transaction processing.

If the transaction manager is unable to determine the root table view for the screen, either because the Root property is set to None, or Default and the transaction manager is unable to determine the root table view, the following error message is displayed: "Root table view name not supplied or not valid." Make sure the Root property under Transaction for the screen specifies the correct table view.

In a master-detail screen, the database table which is the master would be the root table view.

How to Identify A Table View As The Root Table For The Screen

  1. Select the screen (no other widgets on the screen can be selected).
  2. Specify the table view's name in the Root property under Transaction for the screen. The Setting field option menu lists the names of all table views on the selected screen. You can choose from the following options:
  3. Choose OK.

Using Links

A link defines the relationship between two table views. If a screen contains more than one table view, you need to have a link describing the relationship between the two tables.

When you import a database table to the repository, the import process automatically creates links on the repository entry that correspond to the foreign key definitions for that database table. When you copy database-derived widgets to your application screens, you can copy the links directly from the repository entry or create links on your screen.

Creating Links

If a link does not exist for a pair of table views on your screen, you need to create it manually. The ability to join information from multiple database tables is based on the premise that a relationship exists between each of the represented tables on your screen. Therefore, for every pair of table views on your screen that will participate in a join operation with each other, you need a link widget to define the relationship. Links have their own properties—you can define the type of link as well as the parent-child relationship of the table views.

Use the DB Interactions window to display a graphical representation of the relationships between multiple tables on your screen. The link properties defined for a screen apply only to a single screen. Therefore, on other application screens, you can rearrange the relationship between the tables.

You cannot have a cycle appearing in the link specifications. For example, if link1 declares the customers table to be the parent and the rentals table to be the child, link2 cannot have the rentals table be the parent and the customers be the child.

How to Create a Link on Your Screen

  1. Choose CreateLink. Click on the screen to place the widget.
  2. Edit the link's Transaction properties, specifically the Parent, Child, Type of link, and Relations properties, if you want to join the table views.
  3. Identify in what order inserts, updates, and deletes should occur in the database for the two table views represented in this link.

Editing Link Properties

To access and edit link properties, select the link widget on your screen by doing any of the following:

Identity Properties

The following properties define the name of the link, the link's source of inheritance, and a location for additional comments as well as programmatic use:

Name
Use this property to specify the name of the link if you manually created the link. If you copied the link from a repository entry, a name is provided as the result of the import process. Its default name is in the form K#table_name. For example, the repository entry titles has a link widget named K1titles. If there was a second link on the entry, its name would be K2titles.

Inherit From
Use this property to specify the link's source of inheritance if you manually created the link. Use the format repositoryEntry!linkName. This value is provided automatically when a link is copied from a repository entry.

Memo Text
Attach up to nine separate lines of text (memo1...memo9) for additional comments or for entering string expressions to use programmatically.

Transaction Properties

The transaction manager uses the following properties to tell the SQL generator to build the appropriate SQL statement. Most of these properties obtain their values automatically via the import process.

Parent
Use this property to specify the parent table view. Panther gets this information during the import process from the primary and foreign key information in the database tables represented in the link. You can change this specification to change the relationship between the two table views.

Child
Use this property to specify the child table view. Panther gets this information during the import process from the primary and foreign key information in the database tables represented in the link. You can change this specification to change the relationship between the two table views.

Type
Use to specify the type of link.

Relations
The Relations property opens the Relations dialog box where you define how the two table views represented in the link are related and how they are used by the SQL generator, that is, what are the columns in the two tables that are related. Refer to "Joining Database Tables."

In addition to defining the join condition in a WHERE clause, you can also define a lookup specification whereby the data entered by the user at runtime can be used to fetch database column information when new or updated records are added to the database (refer to "How to Define a Lookup Specification for Either Link Type").

Insert Order
Use this property to specify which table view receives the INSERT statement first: Parent First (default) or Child First. SQL statements are generated for both the parent table and the child table.

Update Order
Use this property to specify which table view receives the UPDATE statement first, Parent First or Child First (default). SQL statements are generated for both the parent table and the child table.

Delete Order
Use this property to specify which table view will receive the DELETE statement first, Parent First or Child First (default). SQL statements are generated for both the parent table and the child table.

Service Properties

(JetNet/Oracle Tuxedo only) The following property defines which service the transaction manager uses to implement a Validation operation (in a three-tier architecture). This property is automatically set for link widgets on client screens when you use the screen wizard to generate your screens.

Validation Service
Specifies a service to implement a Validation operation that allows you to verify whether data entered in New or Update mode exists in the linked database table.

Joining Database Tables

In general, joins are built by comparing pairs of columns from two joined tables by testing the data from both columns for equality or other comparisons. Sometimes these joins have a one-to-one relationship while others have a one-to-many relationship. Most common multi-table queries use parent/child relationships created by primary keys and foreign keys. Panther lets you define these relationships by letting you define the join type and the relationship between table views.

How to Join Two Tables with a One-to-Many Relationship

Joining two tables with a one-to-many relationship creates a sequential join:

  1. Select the link widget. Under Transaction, specify Sequential in the Type property.
  2. Select the Relations property to define the join relationship. The Relations dialog box opens.
  3. Enter or modify the Parent and Child column names. The column names are case-sensitive. Choose Help to display and select from a list of columns associated with Parent and/or Child tables.

    When the SQL statement is generated, the WHERE clause associated with the Child table's column uses the value in the Parent table's column.

  4. (Optional) If the Parent's column is represented on the screen by two different widgets, enter the preferred widget's name instead using the following format (including the square brackets and the literal +0):
    ::widget_name[+0]
  5. Define the type of relationship—join—in the Rel (center) column of the Relations dialog box. The only relationship that can be specified for a join is the word join.

    For each join relation specified, the WHERE clause will include one expression of the form:

    widget_data_in_parent_tableview = child_table.child_column
  6. Choose OK to save the specifications and close the Relations dialog box.

How to Join Two Tables Using a One-to-One Relationship

Joining two tables with a one-to-one relationship creates a server join, which displays multiple records using a single condition.

  1. Select the link widget. Under Transaction, specify Server in the Type property.
  2. If the database engine supports outer joins, specify the appropriate Join Type: Left Outer, Right Outer, or Full Outer (the default type is Inner). For more information about the Join Type property, refer to "Specifying the Join Type" in Application Development Guide.
  3. Select the Relations property to define the join relationship. The Relations dialog box opens.
  4. Enter or modify the Parent and Child column names. The column names are case-sensitive. Choose Help to display and select from a list of column names associated with the Parent and/or Child tables.

    The columns you specify are used to build the SQL join condition in a WHERE clause.

  5. Define the type of relationship—join—in the Rel (center) column of the Relations dialog box. The only relationship that can be specified for joins is the word join.

    For each join relation specified, the WHERE clause will include one expression of the form:

    parent_table.parent_column = child_table.child_column

    Figure 22-1 The Relations dialog box lists the parent and child relationships for the selected link.

    If there are multiple joined columns, then the expressions are connected by the keyword AND. If more than one table view in the server join represents the same database table, the SQL generator automatically supplies table alias names as needed. Therefore, self-join expressions are automatically handled.

  6. Choose OK to save the specifications and close the Relations dialog box.

Optionally, you can look up a Child column in the database based on a value the user enters into the widget associated with the Parent column. The lookup is based on the specified join relationship that you defined in steps 3 and 4 for server or sequential joins.

Note: Lookup specifications are used only when adding (inserting) and updating records.

How to Define a Lookup Specification for Either Link Type

  1. Under Transaction, select the Relations property. The Relations dialog box appears.
  2. Enter the Parent column name under Parent in the Relations dialog box.

    If the parent column is represented on the screen by two different widgets, enter the widget's name instead using the following format (including the square brackets and the literal +0):

    ::widget_name[+0]
  3. Enter the keyword lookup in the center column of the Relations dialog box.
  4. Enter the Child column name under Child in the Relations dialog box.

    Figure 22-2 This specification takes the customer id entered by the user to find the corresponding last name in the database table.

  5. Choose OK to save the specifications and close the Relations dialog box.

Setting Validation Links

The links that are defined for a screen can be used to specify validation links. When a validation link exists, you can enter a value in a field, in either New or Update mode, and the transaction manager looks up that value in the linked database table. If the value exists, it displays data for any widgets in the child table view. If the value does not exist, it displays the error Invalid Entry.

How to Specify a Validation Link

  1. Create the desired link if it does not exist and name the link.
  2. Select the text widget that requires the validation link.
  3. Under Database, set the Validation Link property to the desired link.

    The parent table view in the link contains the text widget. The database table associated with the child table view contains the pre-existing values.

Validation link processing is only performed in New and Update modes, as part of the NEW, COPY or SELECT commands, when you are entering or updating data. Since the data in the child table view should already be entered in the database, set the child table view in a validation link to be non-updatable.