Pageflow and Form Guide

CHAPTER 7

Database Pageflow Wizard

This chapter provides instructions for using the Database Pageflow Wizard. It contains the following sections:

About the Database Pageflow Wizard

exteNd Director provides the Database Pageflow Wizard to help you create database pageflows. A database pageflow is a flow that gives the user a way to find, display, and modify records in a database.

The Database Pageflow Wizard lets you create flows that access a single database table, or flows that navigate to other tables that are related by many-to-one relationships (lookups) and/or one-to-many relationships (master/detail).

After you use the wizard, you can run the generated pageflow right away. You can also use the Pageflow Modeler to make changes to the pageflow, just as you would with any other pageflow.

The Database Pageflow Wizard generates a set of forms (XHTML pages that use XForms technology) as well as one or more pageflow processes that tie the forms together into a simple database application. This application provides a convenient and easy-to-use interface for accessing one or more database tables.

Flows that access a single table The following example shows a typical database pageflow used to access a single table:

DBPageFlow1

Master/detail database flows When you build a database pageflow, you specify which table is the primary table. When the wizard knows which table is primary, it can examine the relationships among the various tables you've selected to determine how many levels there are within the query. A lookup (many-to-one relationship) is treated as a single level within the query, whereas a master/detail (one-to-many relationship) is treated as two levels.

When you build a database pageflow that combines data from tables that have a master/detail relationship, the wizard generates multiple pageflow processes, one for each level within the query.

The flow that accesses data in the primary table is the parent flow. The parent flow has a Pageflow activity (View Details) that references a child flow. The child flow accesses data in the detail table. When the user clicks the View link, the parent flow reaches its Pageflow activity, which passes control to the child flow. When the user clicks on the Back link, the child flow reaches its Finish activity, which passes control back to the parent flow.

Here is an example of a parent flow:

Here is an example of a child flow that is referenced by the View Details activity:

Forms

When you create a database pageflow, the following forms (XHTML files) are generated for you:

Form	Description
Search Form	Provides a user interface for entering search criteria. When the user presses the Search button, the search terms are sent to the server as an XML document.
View Results	Displays summary data for a single page of search results. The summary data are presented in tabular format. This form has View, Edit, and Delete buttons that let the user perform operations on the currently selected row. It also has a New Record button that lets the user insert a new database record. When you indicate that you want to use a change log to store modifications, the View Results form also includes Apply Changes and Cancel Changes buttons.
View Details	Displays details for one record in the result set. It also contains an Edit button to allow the user to modify the values associated with the current record. In a master/detail situation, the View Details form also displays the results view of the child table in a tabular format. It shows all of the records of the child table that are linked to the current parent record.
Edit Record	Provides a user interface for editing the detail fields associated with the primary table. The Edit Record form also contains Update and Cancel buttons. The Edit Record form automatically sets a validation on each field to ensure that the user does not try to enter a string that is longer than the width of the associated database column. NOTE: The Edit Record form does not allow you to modify columns in related tables.
New Record	Provides a user interface for creating a new record in the primary table. This interface provides fields for entering the column values associated with the new record. This form is very similar to the Edit Record form. The main difference is that the New Record form lets the user specify values for key fields (except for those that are not automatically incremented.) This form includes a Save button to allow the user to add the new record to the database. The New Record form does not allow you to enter values for columns in related tables.

Pageflows

Every pageflow process generated by the Database Pageflow Wizard includes a Data Set object that provides all of the information required to access the database.

For more information on the Data Set, see Working with the Data Set.

Each pageflow process created by the Database Pageflow Wizard includes a Form activity for each form created. In addition, the flow contains the following database activities:

Activity	Description
Initial Query activity	Obtains the keys of all records from the database that match the search criteria that the user specified on the Search Form. This activity takes the XML document that contains the search terms (which is generated by the Search Form) as input. Once the keys have been retrieved from the database, this activity stores the result set in a record cache.
Get Page activity	Retrieves the summary data for a single page of records. The Database Pageflow Wizard lets you control which columns are included in the summary data and specify how many records are included in each page. The Get Page activity uses the set of keys returned by the Initial Query activity to retrieve the records for one page of results. The order of records in the summary data is based on the order established when the Initial Query activity was executed, not by the order in which records are retrieved by the Get Page activity.
Get Record activity	Retrieves the detail fields for a single record.
Record Insert activity	Sends the data for the new record to the database by executing a SQL INSERT statement. NOTE: If you enable the change log, the SQL INSERT is not actually performed until the Apply Change Log activity is executed. Here's an example of the kind of statement that might be executed: INSERT INTO employees (firstname, lastname, city, state, phone) VALUES (`Bob', `Jones', `Boston', `MA', `617-555-9999') If the insert succeeds, the flow continues on to the next activity. Otherwise, an error message is displayed.
Record Update activity	Sends the updated data for one record to the database by executing a SQL UPDATE statement. To support optimistic concurrency control, the UPDATE statement includes the original (cached) values in the WHERE clause. If the WHERE clause fails to match any rows, that means that the data has been changed by another user, so a data concurrency exception is thrown to indicate this to the user. NOTE: If you enable the change log, the SQL UPDATE is not actually performed until the Apply Change Log activity is executed. Here's an example of the kind of statement that might be executed: UPDATE employees SET city = `Waltham', phone = `781-484-8200' WHERE firstname = `Joseph' AND lastname = `Smithe' AND city = `Boston' AND state = `MA' AND phone = `617-555-1213' AND employeeid = 61 If the update succeeds, the flow continues on to the next activity. Otherwise, an error message is displayed.
Record Delete activity	Deletes the records selected by the user by executing a SQL DELETE statement. To support optimistic concurrency control, the DELETE statement includes the original (cached) values in the WHERE clause. If the WHERE clause fails to match any rows, that means that the data has been changed by another user, so a data concurrency exception is thrown to indicate this to the user. NOTE: If you enable the change log, the SQL DELETE is not actually performed until the Apply Change Log activity is executed. Here's an example of the kind of statement that might be executed: DELETE FROM employees WHERE firstname = `Joseph' AND lastname = `Smithe' AND city = `Boston' AND state = `MA' AND phone = `617-555-1213' AND employeeid = 61 If the deletes succeed, the flow continues on to the next activity. Otherwise, an error message is displayed.

If you specify that you want to use a change log, the Apply Change Log activity is also included in the flow:

Activity	Description
Apply Change Log activity	Applies all changes from the record cache to the database. For more information on the Apply Change Log activity, see Apply Change Log activity.

Activity

Description

Apply Change Log activity

Applies all changes from the record cache to the database.

For more information on the Apply Change Log activity, see Apply Change Log activity.

The pageflow also contains the following additional activities:

Activity	Description
View	Mode activity that places the pageflow in View mode.
CheckPoint	CheckPoint activity that handles page refreshes within the flow. The CheckPoint activity acts as a transaction marker, indicating the starting point for processing whenever the user refreshes the View Results, View Details, Edit Record, and New Record forms.
Exception handler	Exception activity that handles all exceptions thrown during the course of processing.
Exception page	An HTML activity that displays exception information in an HTML page.

Using the Database Pageflow Wizard

Procedure To create a database pageflow process:

With your project open in exteNd Director, select File>New.
Click the Portlet tab.
Select Database Pageflow and click OK:

If you do not have a profile for the database you want to connect to, create one by clicking the New button.

DBPageFlowWizard1b

Specify settings in the Create a New Database Profile dialog as follows:

Setting	Description
Profile name	Enter any name to identify the profile.
JDBC Driver	Enter the class name of the JDBC driver. You can specify any JDBC 2.0-compliant driver. To use the Sun JDBC-ODBC bridge driver (which is included in the JRE), specify sun.jdbc.odbc.JdbcOdbcDriver. If you specify a JDBC driver other than Sun's bridge driver, make sure the driver class can be loaded by the development environment. NOTE: The jConnect driver should be used with Adaptive Server Anywhere instead of the Sun ODBC/JDBC bridge driver. To use MySQL, specify com.mysql.jdbc.Driver.
JDBC URL	Enter an URL that specifies the database you want. For example, you might specify jdbc:odbc:TestDB (if TestDB were your ODBC data source name). For a MySQL database, you might specify jdbc:mysql://localhost:63306/ExpressPortal?user=root&password=novell The text you enter after the first colon is driver specific.
Connection Catalog	(Optional) Specify which SQL catalog (subset) of the database to connect to—for example, PayrollDb. If your database driver does not support catalogs, it will ignore this request. If supported, the connection catalog lets you set up which database tables are retrieved. Connection catalogs are useful when you are connecting to a very large database or only want to connect to a subset of database tables (for example, to exclude production database access).
Datasource Name	Specify the name of the data source to associate with this database profile. You can specify either the datasource or the full JNDI specification. For example, you might specify JDBC/ExpressPortal to use a connection pool called ExpressPortal.

A filled-in panel might look something like this:

DBPageFlowWizard2

Click Test to check the connection to the database specified by the JDBC URL.

This test makes a JDBC connection to the database. The test will fail when a connection is not available or a setting is not correctly specified.
On the test popup, enter your database user name and password and click OK to verify access.
Click OK to close the Create a New Database Profile dialog.

Optionally enter your database user name and password on the Database Profile dialog and click OK:
Select the primary table for your pageflow and click Next:
Select the columns you want to use in your database pageflow in the Database Columns dialog and click OK:

Any tables directly related to the primary table are listed as choices in the Database Columns box, along with columns defined on the primary table.

To select columns for a related table, first open the table in the Database Columns box, then select the columns.
Specify the user interface options for the flow as follows:
1. Specify how many rows you want to show on each Results List page in the Preferred number of results per page field.
2. Indicate whether you want the wizard to add search fields to the top of the Results List page.
3. Click Next.
Specify how you want to format the controls on forms:

These settings control the appearance of controls on the Search, View Details, Edit Record, and New Record forms.
Click Next.
Select the details for each column as follows:
1. Select a column in the Column list.
2. Specify the details for the column:
Click Next.
Specify the Update Strategy and click Next:
Specify the base names for the files that will be generated. You need to specify a separate base name for each database table you selected:
Click Finish.

Modifying a database pageflow

After you run the wizard, you use the Pageflow Modeler to make changes to the pageflow. You can also use the Form Designer to make changes to the generated forms.

General guidelines for editing a database pageflow

Here are some things to keep in mind when editing a generated database pageflow:

When you make a change to a pageflow, this does not change any of the forms used by the flow.
If you want to add or remove a column in a database pageflow, you need to manually add or remove the corresponding field in one or more forms. To add or remove a database column, you need to make changes in the property sheet for the Data Set. Once you've done this, you typically need to add the column to the design-time instance data in each form, as well as add the input or output control to the form.

For complete details on making changes in the property sheet for the Data Set, see Working with the Data Set. For complete details on modifying the instance data or the controls associated with a form, see Form Designer.
If you prefer not to make manual changes to a database flow, you can run the Database Pageflow Wizard again. When you run the wizard, you can either overwrite your old flow and forms, or create new ones.

Working with the Data Set

DataSet

The Data Set provides all of the information that a pageflow requires to connect to a database and access rows and columns of data. The Data Set lets you make changes to various database settings. In many cases, it simply allows you to modify settings you specified when you first ran the Database Pageflow Wizard. In other cases, it lets you make changes to more advanced settings that are not available in the wizard.

Each Data Set has a unique name that is referenced by the following database activities:

Initial Query activity
Get Page activity
Get Record activity
Record Insert activity
Record Update activity
Record Delete activity

Since the Data Set encapsulates all of the information needed to access the database, these activities do not need to specify this kind of information. Instead, they simply point to a Data Set object:

DBPageFlowDataSet1

The properties of the Data Set are:

Property Inspector tab	Property name	Description
Activity	Name	Specifies a name for the activity.
Activity	Description	Describes what the activity does.
Data Set	Advanced Settings	Allows you to specify concurrency and caching settings, as well as other details that control database access. For more information, see Advanced settings.
	Enable Changelog	Indicates whether the change log will be enabled for this flow. Changing this setting does not add or remove activities or links within the flow, nor does it alter the forms associated with the flow. Therefore, if you want to add or remove the Apply Change Logs activity or the Apply Changes links in the forms, you need to make these changes by hand.
	JNDI Data Source	Identifies the data source. The valid data source types are: `JDBC/xxx` Use this syntax to reference a database that is set up as a connection pool. `jdbc/ref name` Use this syntax to reference a resource property in your deployment plan. If you do this, you must also have a resource data source in the web.xml file. `Databases/xxx/DataSource` Use this syntax to reference a database that was added to the exteNd^TMApplication Server.
	Columns	Lets you add or remove columns from the pageflow or change their properties.
	Row Limit	Specifies the maximum number of rows that will be retrieved.
	Rows Per Page	Specifies the number of rows that will be displayed on each page of the View Results form.
	Sort Order	Allows you to specify the order in which rows will be sorted in the result set. For more information, see Sort order.

Advanced settings

The Advanced Settings dialog lets you specify several settings that are not available in the Database Pageflow Wizard.

Concurrency control

On the Concurrency tab, you can select columns for concurrency checking:

DBPageFlowDataSet2

The columns you select are used to verify that no changes were made to the database record since the time it was first retrieved. If any changes were made, an attempt to modify the record will fail.

Caching

On the Caching tab, you can specify a caching strategy for the flow, as shown below:

DBPageFlowDataSet3

The caching strategy used for a database pageflow has a direct effect on how well the flow performs at runtime. The strategy you select will depend on your application requirements. If you're most concerned about memory usage, select Strategy 1. If you're most concerned about minimizing database access operations, select Strategy 3. For a more balanced approach, select Strategy 2.

SQL Details

The SQL Details tab provides some additional settings for those who want greater control over how the pageflow accesses the database at runtime. It lets you override the default SQL handler class and also specify whether identifiers will be wrapped in quotes in SQL statements.

DBPageFlowDataSet4

Sort order

The Sort Order dialog lets you specify the order in which rows will be sorted in the result set. A database pageflow can have up to four levels of sorting:

DBPageFlowDataSet5