Application Development

The screen wizard is the easiest way to build screens that use the transaction manager. The wizard leads you through choosing your root table view, selecting widgets from that table view, adding additional table views and their widgets, and it automatically changes the property settings.

Even so, if you want to modify screens created by the screen wizard or to build your own screens or service components that use the transaction manager, you need to understand how the transaction manager uses property settings, links and table views in order to automatically generate SQL statements and to perform database processing. This chapter summarizes how you can build a two-tier application screen in the editor.

To help explain the transaction manager concepts, a sample screen based on the videobiz database is used. The screen lets you enter a video title by name or identification code, and view the names of actors and their roles in that video. A picture of the screen appears as Figure 31-1.

Development Process for Transaction Manger

Database operations are integrated into the overall development of your application. Table 31-1 shows how database operations are effected by each step in developing a transaction manager application.

Copying Repository Objects

Building the sample screen was accomplished by using the database importer and the repository. The videobiz database was created in JDB and imported into the repository. The database importer created a repository entry for each database table. The name of the repository entry corresponds to the database table name so that entries can be easily identified. Since objects copied from the repository inherit property settings, the application screen contains much of the information needed for SQL generation and database access automatically.

Each repository entry contains:

• A widget corresponding to each database column. (One of the properties for the widget is the column name.)

• A label for each database column.

• A table view containing database table information.

• Links based on any foreign key definitions for the database table.
  

  Figure 31-1 Sample screen used to explain transaction manager processing.

Sequence for Copying Objects

When you create a screen with multiple table views, the order used to copy objects from the repository is important. You need to copy the information for the major table views in the screen first. This ensures that any primary key widgets copied to the screen are in the master, or parent, table view.

Since the focus of the sample screen is information about each video title, widgets and links from the titles repository entry are copied first.

Actor information is in two different database tables, actors and roles. The roles table with its a title_id column and link to the titles table view must be copied next. (Note that it is not necessary to copy the title_id widget itself from the roles entry; the transaction manager uses the title_id widget in the titles table view for SQL generation.) Just the actor_id and the role widgets are copied to the screen—actor_id because it is part of the primary key.

Since actor_id is already onscreen, all that is needed from the actors entry are first_name and last_name.

Finally, since the price category codes are not self-explanatory, pricecat_dscr is copied from pricecats to provide better descriptions.

Specifying the Traversal Order

Since the screen contains widgets from several database tables, you must specify in which order the tables will be processed. Copying the objects from the repository in the correct sequence is the first step. The next step is to modify table view and link properties.

Table Views

A table view is a group of related widgets, generally belonging to the same database table. Table view properties include the name of the database table, the names of columns that belong to the database table, and the columns that comprise the table's primary key. The import process creates a table view, an invisible widget type, for each imported table, and includes the widgets corresponding to the database columns in each table view.

If a widget is a member of a table view in the repository, Panther automatically adds the widget to a table view of the same name in the destination. If the table view does not exist, Panther creates it using the properties of the table view in the repository. Thus, most table views are created automatically by the database importer and copied from the repository as the widgets are copied.

However, just knowing the table views on a screen does not tell the transaction manager which table view should be processed first. To obtain this information, the transaction manager looks at the link properties for the screen.

Links

A link defines the relationship between two table views. The link properties list the columns or widgets connecting the two table views, the type of link—server or sequential, and the parent and child table view designations.

The import process creates links based upon foreign key information contained in the database. If the database contains no foreign key information, then you can create the links manually in the screen editor. Link widgets are only visible in the screen editor workspace, not at runtime.

How to Gain Access to Table View and Link Properties

You can select the table view or link either from the Widget List or the DB Interactions window.

How to View the Table Views and Links for a Screen

Choose ViewDB Interactions. The DB Interactions window provides a graphical representation of the table viers and links on your screen. You can also click on any of the objects in the window in order to gain access to its properties.

Setting Link Properties

The table view listed at the top of the DB Interactions screen is the root table view, the first table view to process for this screen. The transaction manager determines the root table view from the Parent and Child properties of all of the links on a screen. Since the purpose of the sample screen is to provide information about each video title, titles is the root table view, as illustrated in Figure 31-2.

If you get an error message that the root table view cannot be determined, check the Parent and Child properties for the link. Often, these properties need to be reversed for one or more links. If changing these properties does not resolve the error, you can set the root table view manually in the screen properties.

Determining the Order of Processing

In a link widget, two table views are identified: one table view is designated as the parent table view and the other is designated as the child. This designation helps determine the root table view and the order of processing for the table views.

Generally, the database table associated with the Parent table view is different than the one associated with the Child table view. One exception to this condition is for SQL self-joins where the same database table (using different table view names) is associated with both the Parent and Child table views.

When you copy links from the repository, the settings for the Parent and Child properties might need to be reversed for a particular screen. You can easily determine the current values by looking at the link in the editor. The link is displayed as the parent table view name plus (+) the child table view name.

In the sample screen, some of the Parent and Child properties had to be edited. Since titles is the root table view, it must be the parent table view for any link in which it appears. Since the K1titles link had titles as the child table view, the Parent and Child properties of that link were changed for this screen. titles became the Parent and pricecats the Child.

At the next level, roles needs to be the parent table view for any link in which it appears. For the K2roles link, roles became the Parent, and actors the Child.

Notes: When you reverse the Parent and Child settings, you must also edit the Relations property if the columns joining the two tables do not have the same name. This was not needed for our sample screen since the pricecat column in the titles table has the same name as the pricecat column in the pricecats table.

Restrictions

You cannot have a cycle appearing in the link specifications. For example, if link1 declares the titles table to be the parent and the roles table to be the child, link2 cannot have the roles table be the parent and titles be the child. That constitutes a circular link. Remember, however, that links are specific to one screen. On another screen, the relationship specified in link2 could exist.

You cannot have the same table view in both the Parent and Child properties. If this occurs, the error message "Maximum depth exceeded" is displayed.

Specifying the Link Type

There are two types of links:

• Sequential link represents a one-to-many relationship between two table views.

• Server link represents a one-to-one relationship between two table views.

A group of table views connects by server links is referred to as a server view. Therefore, a table view can also be a server view which means that it is either the root table view or is connected to a parent table view by a server link.

The transaction manager uses server views to process your database operations more efficiently. When the transaction manager retrieves data from the database (invoking the SQL SELECT command), it processes all of the table views in a server view as a single SQL SELECT, rather than repeating the SELECT once for each table view. Other database operations (such as those that update the database) are processed differently; refer to page 36-7 for more information.

In the sample screen, a sequential link between the titles and roles table views was appropriate, but the Type property had to be updated to Server for the links between the titles and pricecats table views and the roles and actors table views.

Tree Traversal

The DB Interactions screen graphically illustrates the tree of table views and links that the transaction manager uses to perform its processing. On this screen, < (less than) designates a sequential link; = (equals) designates a server link.

Figure 31-2 DB Interactions screen for the sample screen showing the linked table views and the link type.

When a command is selected, the transaction manager traverses this table view tree, issuing statements to each table view, or server view, in order to fetch or update data in the database.

In our sample screen, there are two server views:

• titles (which includes the pricecats table view)

• roles (which includes the actors table view)

For server links, you can specify the Join Type—the join operation the SELECT statement will use to combine information from the database tables. In order to be available, the database engine must support outer joins. The Join Type property can be set to: Inner (default), Left Outer, Right Outer, or Full Outer. For more information on how the Join Type affects data retrieval, refer to page 33-28.

Setting Table View Properties

For a widget to participate in transaction manager processing and SQL generation, the widget must be a member of a table view. In addition, if members of the table view participate in SQL INSERT, UPDATE and DELETE statements, all members must have the same number of onscreen occurrences and the same number of maximum occurrences.

Typically, widgets are automatically assigned to a table view when you copy them from the repository to your screen. You can add members to an existing table view in order 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.

Refer to page 22-10 in the Using the Editors for details on how to define a widget's membership in a table view.

You can specify whether a widget or a table view participates in SQL generation and database updates. For table views, the Updatable property determines if data in the corresponding table can be updated. If set to No, widgets in that table view are protected from focus and data entry.

Specifying Widget Properties

Once the screen contains the necessary widgets, table views and links, you might choose to edit some of the Database or Transaction properties. Editing the properties can change the transaction manager processing or SQL generation performed for commands.

Changing SQL Generation

For the sample screen, properties were changed for the title_id and name widgets in order to fetch a specific video title.

With these changes, when a user enters an identification code or part of a video title followed by the SELECT or VIEW commands, the transaction manager will display the desired information. For more information on setting properties for SQL generation, refer to page 33-1.

Using Grids

For the grid widget containing the actor information, the following Format/Display properties were changed:

• The Column Titles property was changed to Per Column in order to display the column name set by the importer in the widget's Column Title property.

• The Row Titles property was set to None.

Using Validation Links

The links that are defined for a screen can also be used to specify validation links. When a validation link exists, you can enter a value in a widget, 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.

It is very simple to specify a validation link. Create the desired link if it does not exist. Then, set the Validation Link property for the widget containing the entered value to that link.

To view a sample validation link, refer to page 33-46.

Specifying Transaction Manager Commands

Transaction manager processing is implemented by invoking transaction manager commands. In the sample screen, the commands are called via control strings on the screen's push buttons. When a transaction manager command is activated at runtime, the events associated with that command are generated, which in turn perform the processing needed.

On the sample screen, the push buttons are associated with the following commands:

• View—The VIEW command generates the SQL SELECT statements necessary to display data for all table views. If a video name or id is entered, the SQL generation is modified to display data for that entry only.

  Control String property: ^sm_tm_command("VIEW")

• Next—The CONTINUE command displays the next title and all its associated data.

  Control String property: ^sm_tm_command("CONTINUE")

• Reset—Two transaction manager commands are used to reset the screen. The CLEAR command clears data in all the screen's widgets; the CLOSE command resets the screen to initial mode in order to allow user input for the video name or id.

  Control String property:

  ^(^sm_tm_command("CLEAR"))sm_tm_command("CLOSE")

Refer to page 31-13 for information on specifying transaction manager commands.

Refer to page 35-2 for information on how the transaction manager generates events for each command.

Refer to page 33-43 for information on viewing the SQL that the transaction manager generates for each command.

Changing the Transaction Mode

Not only does calling a command generate the events for that command, it also sets the transaction mode for the screen. The transaction mode helps determine whether fields are protected from data entry and if push buttons and menu items are active.

The behavior of widgets in the current transaction mode is determined by the Class property setting. The push buttons were assigned the following values in order to make them inactive for certain modes:

• View—view_button—active in initial and view mode

• Next—continue_view_button—active in view mode

• Reset—clear_button—active in all modes

Refer to page 36-12 for more information on transaction modes and Class property settings.

Adding a Transaction Event Function

Part of the transaction manager's power is that it allows you, the developer, to modify its processing as needed. One way of customizing the transaction manager is to write an transaction event function and modify or replace the default processing for a transaction manager event. In our sample screen, an additional field was desired, stating whether a copy of the video is available.

Figure 31-3 An additional field, processed by a transaction manager event function, displays whether the video is available for rental.

In order to display this information, the following steps were taken:

• From the tapes repository entry, the title_id field and the titles+tapes link were copied to the sample screen. This automatically created the tapes table view.

• The title_id widget was renamed title_avail, since each widget's name on the screen must be unique.

• For the title_avail widget, the Use in Select property (under DatabaseFetch Data) was set to No.
  

  Figure 31-4 The field named title_avail, which also corresponds to the title_id widget, contains the results of the transaction manager event function.

• Under JPL Procedures, the following event function was added for the main request of the VIEW command:

  // TM event functions are passed one parameter:
  // the event name.
  
  proc find_tapes (event)
  
  // The VIEW command's major request is TM_VIEW.
  
  if event == TM_VIEW
  {
  // Create a JPL variable and alias it to receive 
  // the value from the subsequent SELECT statement.
  
  	vars numtitles
  	DBMS ALIAS numtitles
  	DBMS QUERY SELECT count (distinct title_id) \
  		FROM tapes \
  		WHERE title_id = :+title_id and status = 'A'
  
  // Update the title_avail field based on the value.
  
  	if numtitles>0
  	{
  	call sm_n_putfield("title_avail", "Yes")
  	}
  	else
  	{
  	call sm_n_putfield("title_avail", "No")
  	}
  
  // Event processing is self-contained. No further
  // processing is needed.
  
  return TM_OK
  }
  return

• The event function was attached to the tapes table view in its Function property.

Refer to page 32-1 for more information on writing transaction manager event functions.

Refer to page 36-1 for information about transaction manager processing at runtime.