Using the Editors

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:

• Use the screen wizard or build application screens from scratch using widgets from repository entries that were created as the result of importing your database tables into Panther (refer to "Populating a Repository with Database Objects" for information on populating the repository from a database).

  The table views and links on your application screens provide the transaction manager with the information it needs to access your database.

• Gain access to the transaction manager via application or test mode.

• Make sure that you are attached to the database. This can be done via server initialization in JetNet/Oracle Tuxedo applications, or via a direct connection (choose FileOpenDatabase in the editor or DatabaseConnect in test mode).

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:

• Choose ViewDB Interactions. Select the desired table view from the DB Interactions window.

• Choose ViewWidget List. Select the desired table view from the list of widgets.

• Select any widget that is a member of the table view. Then choose EditGroupSelect Groups.

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.

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:

• Yes—(default) The table view is updatable, and therefore, the transaction manager requires that the widgets associated with primary keys (defined in the Primary Key property) exist in the selected table view or in a parent table view on the screen. If widgets associated with the primary keys are not included on the screen, the transaction manager generates an error message.

• No—Protects the database table from being updated.

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:

• For the table view—If you name a model for the table view, this model takes precedence over the one specified for the screen.

• For the screen—If you name a model for the screen as a whole, this specification takes precedence over the default model of the default database engine. If no model is named, the transaction manager uses the default model of the default engine in combination with the common model.

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:

• In the WHERE clause of the SQL UPDATE and DELETE statements generated for the selected table view.

• During database import processing to define the Relations property for link widgets.

Insert Handling

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

• SQL Statement Generation—(default) Generates a SQL statement based on property settings. The Regenerate SQL sub-property can be set to Yes, No or Default.

• Function Call—Calls the specified function. You can specify a function for insert processing and a function for save processing.

• Nothing—No processing is done for the statement.

Update Handling

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

• SQL Statement Generation—(default) Generates a SQL statement based on property settings. The Regenerate SQL sub-property can be set to Yes, No or Default.

• Function Call—Calls the specified function. You can specify a function for update processing and a function for save processing.

• Nothing—No processing is done for the statement.

Delete Handling

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

• SQL Statement Generation—(default) Generates a SQL statement based on property settings.

• Function Call—Calls the specified function. You can specify a function for delete processing and a function for save processing.

• Nothing—No processing is done for the statement.

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:

• SQL Statement Generation—(default) Generates a SQL statement based on property settings.

• Function Call—Calls the specified function. You can specify a function for SELECT processing and a function for CONTINUE processing.

• Nothing—No processing is done for the statement.

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.

• Yes—Includes the DISTINCT keyword in the automated SQL SELECT statement. This eliminates duplicate rows in the results.

• No—(default) Duplicate rows are included in query results.

  Refer to "Eliminating Duplicate Rows in a Result Set" in Application Development Guide for more information.

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:

• Default—The screen's Directions property is used to specify the value. If the screen's value is also Default, this is the equivalent of Down only-all modes.

• None—This eliminates the possibility of doing CONTINUE-command processing on a server view. Since CONTINUE functionality can consume system resources, you have better control or how a SELECT is issued against the table view.

• Down Only-all modes—Only the CONTINUE command fetches additional data. CONTINUE_DOWN is not permitted.

• Up/Down-view mode—CONTINUE_BOTTOM, CONTINUE_DOWN, CONTINUE_TOP, and CONTINUE_UP are permitted in addition to CONTINUE if the current transaction mode is view.

• Up/Down-all modes—CONTINUE_BOTTOM, CONTINUE_DOWN, CONTINUE_TOP, and CONTINUE_UP are permitted in addition to CONTINUE if the current transaction mode is update or view. However, in update mode, data must be re-fetched after a SAVE command in order for updates to be displayed from the continuation file.

Count Select

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

• Yes—Determines whether there is a Warning message (yes or no). If Warning is set to Yes, you can specify the maximum size of the result set in the Threshold property.

• No—(default) The transaction manager fetches data regardless of the size of the result set.

(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.

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:

• Execute a self-join; that is, define a relationship that a table has with itself.

• Define a relationship between a database column in a table view and another database table that is not represented on your screen.

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:

   • Choose CreateLink.

   • Edit the link's Transaction properties, specifically the Parent, Child and Relations properties (refer to "Editing Link Properties" for details).

   • Identify in what order inserts, updates, and deletes should occur for the two table views represented in this link.

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.

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.

You can add members to an existing table view to:

• Include a widget in the SQL generation.

• Create a "virtual" column—that is, use a widget on the screen to display information from the database even though the widget does not have an associated column in the database.

• Add a database column back to the table view that you previously removed from the table view.

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.

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:

   • Default—When set to Default, the transaction manager automatically determines at runtime, based on the link properties, which of the table views on the screen is the root table view. This is the initial setting for this property. However, if the default root table view is ambiguous, and the transaction manager is unable to determine the root table view, the following error messages are issued: Root table view name not supplied or not valid and Transaction unspecified or unavailable. Specify the root table view by name instead.

   • None—The screen's transaction processing as performed by the transaction manager is turned off. Accessing transaction manager commands results in an error: Transaction unspecified or unavailable.

   • A table view name—Select one of the listed table views by name.

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.

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

• Click on it directly on your screen.

• Choose ViewWidget List. Select the link from the list.

• Choose ViewDB Interactions to display a graphical representation of your table views and links. Select the graphical image (an equal or less than sign) of the link from this window.

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.

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.

• Sequential—Widgets belonging to the parent and child table views are populated by two separate SQL SELECT statements. The SELECT for the parent table view is performed first, followed by the SELECT statement for the child table view. The joining of the two table views occurs because the results from the first SELECT statement provide the information needed by the second SELECT statement. Refer to "How to Join Two Tables with a One-to-Many Relationship" for details on creating a sequential join.

  For example, to find all the video rentals for a customer, you would specify a Sequential link type between the customers and rentals tables. One row in the customers table has the potential of having several associated rows in the rentals table. The processing for the customers table is done first, followed by the processing for the rentals table.

• Server—Widgets from both the parent and child table views are populated by a single SQL SELECT statement. Server links are possible when there is a one-to-one relationship between the rows in each of the table views. Refer to "How to Join Two Tables Using a One-to-One Relationship" for details on creating a server join.

  For example, to find the title for each video rented by a customer, you would specify a Server link type between the rentals and titles tables. For every rented video, there is an associated title; there is a one-to-one relationship between the title_id in the rentals table and the title_id in the titles table.

  If the Type property is set to Server, there is a subcategory called Join Type which lets you take advantage of SQL join facilities. You can use this to control the join operation of any SELECT statement which combines information from two database tables. The four choices are:

  • Inner (default): PV_INNER—Fetches all possible pairs, but excludes those rows that do not meet the matching column condition for the join.

  • Left Outer: PV_LEFT_OUTER—Compares the two tables, fetches all possible pairs and also those rows from the left table having no matching value in the right database table (NULL values are used).

  • Right Outer: PV_RIGHT_OUTER—Fetches all possible pairs, including unmatched rows from the rightmost database table using NULL values if no match is found in the left table.

  • Full Outer: PV_FULL_OUTER—Fetches rows from both tables even if when there is no (NULL) match.

    For an example using the various join types, refer to "Specifying the Join Type" in Application Development Guide.

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.

(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.

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.

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.

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.