DX Tips and Tricks

Formating and Validating Data

Query by Example

Tables and Graphs

Updates and Commits

Master/Detail Screens

Enumerated Widgets

Stored Procedures

Software Reuse Features

Enumerated widgets map database values that computers understand to values that people understand. For example, the value 'M' could map to 'Male', the value '18203' to '3 inch drill bit' or the value 'RWC-14232' to 'Readings in Western Civilization'. Enumerated widgets allow database designers to represent data efficiently within the database -- without requiring users to learn arcane codes or memorize unwieldly abbreviations.

DX provides Enumerated Widget types that make these fields trivial to implement. Because enumerated widgets present users with a finite list of elements to choose from, they must be implemented with a one-of-many Object Type: a Combo Box, Radio Box, List, Option Menu or similar widget.

For example, to create an enumerated widget that maps product_code to product_name using a Combo Box:

• Select the Combo Box Object Type from the Schema Browser.
• Drag and drop the product_code on to the Browser. A new application is created.
• Click on the product_code widget to update the Resource Editor, then find the map resource.
• Map the name the computer understands to the name the user understands by entering the following in the map resource:

select Products.product_code,Products.product_name from Product

The map resource statement above selects two columns from the Product table: product_code and product_name. The first column specifies the value stored in the database. The second column specifies the value to be displayed and, if selected, mapped to the first column.

Next we'll change the ComboBox settings so that 4 items are displayed in the box. (By default, when you click on the arrow of a combination box, only one choice is displayed.)

• Locate the mapType resource in the Resource Editor, and change the resource setting to XiDB_MAP_SQL.
• Locate the visibleItemCount resource, and enter "4" in the visibleItemCount input field.
• Switch to Play Mode and click on the product code field. A list of product names appears!

Note that these product names are mapped to actual product codes. For example, when users add a product_name, the product_code will be stored in the database.

Additional Resources:

• Refer to Enumerated Type Widgets in DX HyperHelp.
• Tutorial 4: Handling Multiple Choices with Enumerated Widgets describes how to set up an enumerated widget that maps Employee ID to Employee name and, through a join operation, displays employee information.

DX lets you control formatting and input validation by setting resources instead of writing code.

The XmNformat resource controls how data should be displayed and interpreted using familiar printf-style specifications. These specifications support numeric, money, text and date/time formats in a wide variety of styles. and include complete support for internationalized applications.

All DBPak Data Presentation widgets perform basic validation against the format, and will not let users enter invalid data.

The Data Presentation widget XiDBDataField handles the display and entry of data as text. Within XiDBDataField, The XmNpicture resource setting specifies a picture for data entry in the widget. The picture is used to validate characters entered into the field.

The following table defines the characters you can use in a picture:

Many other formatting and validation resource settings are available for the XiDBDataField widget, including XmNmaximum, XmNminimum, XmNmustFill and XmNnotifyPerKeystroke. By setting XmNautoReturnOnFill and XmNautoTraversal resources, you can also make the application behave like 3270-style data entry screens.

The XmNpictureFailedCallback resource specifies a list of callbacks to be called when users enter information that is invalid based on resource settings.

Additional Resources:

For a complete description of these and other resource settings for XiDBDataField:

• Refer to XiDBDataField in DX HyperHelp.

Master/Detail screens are one of the most common application requirements. Whenever you display a one-to-many relationship, such as one customer/many invoices, one invoice/many invoice items, one part/many part numbers, you are working with master/detail relationships. Setting up these relationships with traditional client/server tools can require hours of tedious programming. Not with DX!

DX lets you create master/detail screens simply by dragging and dropping columns of data. First you create a master field. Then you create a slave table. Next you specify the relationship between master and slave by modifying the where widget of the Table resource.

For example, to display an invoice number along with all invoice details, follow these simple steps:

1. With the Object Type Data Field selected in the Schema Browser, drag and drop the Invoice column on to the Browser. A new application is created in a separate window.
2. Resize the application container so there is enough space for a table.
3. Select Table from the Object Type menu of the Schema Browser.
4. Now select all the columns you want to display with each invoice number. Drag and drop them on to the application window to create add a table to your application.
5. Now specify the relationship between master and slave by adding a "where" clause to the Table resource. Click on the dbTable widget in the Browser. The widget's resources appear in the Resource Editor. Locate the where resource in the Resource Editor, and enter the following in the input field:

"InvoiceDetail.Invoice_Number=@value(invoice_Invoice_number)".

The "where" clause creates a relationship between the dbTable widget and the invoice_Invoice_number widget. When the value of invoice_Invoice_Number changes, it is substituted into the "where" clause and the query is re-evaluated.

Switch to Play Mode and press Search. As you move through successive invoices, note that the invoice detail table is automatically updated!

Additional Resources:

• Tutorial 3: Master-Detail Display and Entry Form Application provides a detailed walk through the Master-Detail application creation process.

DX builds powerful QBE-capabilities into every Data Presentation widget.

It's easy to use QBE, either in Play Mode or while running the application. Simply type in the value you want to search for in any field. Click the "Search" button, and DX automatically retrieves all matching records. Use the arrow keys to review each match. Click the "Stop" button, and the field reverts to its initial setting.

DBPak resource settings make it easy to fine-tune QBE operations or turn them off completely. By modifying QBE resource settings you can create widgets that perform:

• pattern matching searches ('like', 'contains', 'begins with', 'ends with', etc.)
• relational searches ('greater than', 'less than', 'greater or equal to', 'not equal to', 'exact match', etc.)
• user-defined searches that allow the user to type in their own relational operators (<,>,...) or SQL wildcards (% and _).

QBE behavior is controlled by one resource on the Query widget and several resources on Data Presentation widgets.

Within the Query widget:

• XmNallowQueryByExample turns QBE capability on or off for all the Data Presentation Widgets controlled by the Query.

Within Data Presentation widgets:

• XmNuseInQBE -- indicates whether or not the widget's value should be used to filter retrieved data.
• XmNqbeStyle -- determines the look and feel of the widget when in QBE mode.
• XmNqbeValueString -- sets the default value for a Data Presentation widget when entering QBE mode.
• XmNqbeOperator -- controls QBE operands. Over 30 choices are available, providing developers with a wide range of relational, pattern-matching and user-defined QBE operations.

Additional Resources:

• Refer to Query By Example in DX HyperHelp.

DX provides advanced facilities to help developers leverage existing work. Together, these tools support a development approach that maximizes software reuse, improves application consistency and minimizes future maintenance requirements..

DX's software reuse features are organized into four categories:

• Classes and Subclasses
• User-Defined Collections
• Styles
• Managers

Classes and Subclasses

With DX, object-oriented design doesn't just mean generating C++. It means creating and modifying classes and subclasses, the ability to group resources into styles, and the availability of managers to further modularize application pieces. It also means the ability to easily share these resources with other developers and organize them for future application needs.

With DX, creating classes is as easy as selecting a widget or group of widgets and choosing the "Make Class" menu option. Once a class is created, it is automatically saved on the Palette and can be reused as often as needed. Classes can also be quickly saved into a local class section of the Palette for reuse in future applications, or in a system class section made available to all developers. This makes it easy to create a library of user interface elements ready to use in current and future projects.

Subclassing provides a way to create one or more variations of an existing class. For example, suppose you have a class that you are likely to use in different applications, but the class will be slightly different in each application. You can create the class, and create as many subclasses as necessary from that class. Each subclass inherits the attributes and behavior of the class from which it is created. You can then specialize each subclass by adding new behaviors.

User-Defined Collections

A user-defined collection is one or more widgets and associated resource settings that you save as a group on the Palette. Collections provide a handy way of providing convenient access to all your most commonly-used widgets and resource settings.

An example of a collection is the Control Panel, which is a container of control buttons. When you click on the Control Panel widget icon on the Palette, then place the widget outline on an interface, a pre-defined collection of widgets with specific resource settings is created. You can then edit the resources of any widget in that collection, if needed.

Styles

DX styles are user-defined groups of widget resource settings. Styles act like a database of resource information: they remember which resources have been set for each widget throughout the application. Like classes, when you change a style, every widget governed by that style will immediately reflect the change.

Together, DX classes, controlling the user interface architecture, and DX styles, governing the application "look and feel", create a powerful object-oriented design environment capable of supporting large, complex GUI projects.

Managers

Managers enable you to customize DX by adding your own constants, identifiers and types.

Once you create and save a constant or identifier, it will be added to an option list in the Resource Editor and all appropriate extended editors. Alternatively, you can enter the constant or identifier name in a resource's input field. This puts your constants and identifiers at top-of-mind for every developer sharing your DX environment.

DX managers provide an easy way to organize all the pieces of GUI applications. By storing application pieces in a central, easy-to-access place, DX encourages project-wide consistency and software reuse.

Additional Resources:

• Refer to Object-oriented Features in DX HyperHelp.

DX provides extensive support for stored procedures. Stored procedures are SQL-based routines that define commonly used database interactions. Stored procedures are frequently implemented by database administrators in order to:

• model business rules;
• enforce security constraints;
• ensure that table inserts, updates and deletes are performed correctly.

DX supports stored procedures in two ways:

• Within DBPak Query widgets, you can set resources to specify Update, Insert and Delete stored procedures. DX will execute the stored procedure in place of, or in addition to, dynamically executed SQL.
• You can also use API calls to invoke a stored procedure at your discretion, by setting the XiDBDoProcedure() or XiDBDoSelectProcedure parameters of any Query widget.

Using Stored Procedures

All DBMSs have different methods for calling stored procedures. DBPak unifies them into a standard form which we call a stored procedure call, a string that specifies all of the following:

• Name of the procedure
• Parameters
• Widget which should receive the return value (if any)
• Set of widgets to which to distribute the results.

For each parameter, a stored procedure call specifies the following:

• Value or widget from which to retrieve the value;
• Type of parameter (in, out or in/out)
• Whether the parameter is required or optional

The basic syntax of a stored procedure call is:

{<widget> {,<widget>...<-} <proc_name> (<params>) {RETURNS <widget>}

where items in braces are optional, and an item followed by ... denotes an optionally repeated item.

Note than not all DBMS's support all features of the DBPak stored procedure call. The following table illustrates feature support among DBMS's:

Example 1: Inserting Records

This example describes how to use a stored procedure to create new Inventory records. The procedure below (written in Sybase Transact/SQL) ensures that the part number contains only uppercase characters, and fills in a few column defaults. It is installed in the sample database.

Code: Write the procedure as follows:

	/*newPart - creates a new part recorded
	* It forces the part number to upper case and fills in some
	* defaults
	*/
	
	create procedure newPart @pno PartType, @desc varchar(40),
		@price money, @quant int = 0, @ reorder int = 10
	as
		declare @newPno PartType

		
		select @newPno = upper(@pno)
		if @quant is NULL
			select @quant = 0
		if @reorder is NULL
			select @reorder = 10

		insert into Inventory (Part_Number, Description, Price,
			Quantity_On_Hand, Reorder_Quantity)
		values (@newPno,@desc,@price,@quant,@reorder)
		select Part_Number, Description, Price, Quantity_On_Hand,
			Reorder_Quantity from Inventory where
				Part_Number = @newPno

This procedure accepts as paramaters all of the fields of the Inventory record, and returns (with the final select statement) the same set of fields.

In our application, we create XiDBDataField widgets as described in the following table:

The XmNinsertProc resource of the Query would be:

part_num, description, price, quant, reorder <-newPart([part_num], [description],[price],[quant] opt, [reorder]

Procedure parameters that might be defaulted are marked as single 'opt' in the XmNinsertProc procedure call, and XmNrequiredForInsert is set to False in their corresponding widgets. This allows the application to call the newPart procedure with those fields left blank. You must correctly match the values returned from the procedure with the appropriate widgets.

Although this example requires an initial Part _Number, it's not a requirement. An application might have an insert procedure that takes no input parameters, creates a blank record with a new unique key, and returns the key for DBPak to send to a widget. After the insert, DBPak selects the new record (using the returned key) to fill in the remaining fields.

Note: to use this technique, all Data presentation widget must set to false.

Example 2: Updating a Value with a Stored Procedure

This example illustrates how to write a stored procedure for updating a value in the database (written in Sybase Transact-SQL) to update the Price column of the Inventory table in the OrderEntry database).

  
	/* changePrice - changes Price of an Inventory
	item. Will fail if you raise or lower more than 10%. There is no
	reason for that restriction other than to highlight what happens
	whan an update procedure fails when using DBPak.
	*/

         create procedure changePrice @pno PartType, @newPrice money
         as
	        declare @curPrice money

	        select @curPrice = Price from Inventory
		     		where Part_Number = @pno

			if @@rowcount != 1 begin
				raiserror 9999 "Part %1! not found", @pno
				return 1
	        	end

			if 	((@newPrice  <   @curPrice * .9) or
				 (@newPrice  >   @curPrice * 1.1))
			begin
                  		raiserror 40001 "price change for %1! is too large", @pno
                  		return 1
	        	end

 			update Inventory set Price = @newPrice
       				where Part_Number = @pno
        		return 0

To use this procedure, you need at least two Data Presentation widgets in the application: one to provide the Part_Number and one to provide the new Price.

The widget holding the Price uses the XmNupdateProc resource to specify the this stored procedure as follows:

	Widget parent, query;
	Widget partNo, price;
	...

	partNo = XtVaCreateManagedWidget("partnumber",
		XiDBDataFieldWidgetClass, parent,
		XmNexpression, "Inventory.Part_Number",
		XmNquery, query,
		XmNkeyField, True,
 		XmNdataType, XiDB_INTEGER,
		NULL);

	price = XtVaCreateManagedWidget("price",
		XiDBDataFieldWidgetClass, parent,
		XmNexpression, "Inventory.Price",
		XmNquery, query,
		XmNdataType, XiDB_FLOAT,
		XmNudpateProc, "changePrice ([partnumber],[price])",
		NULL);

Note that the XmNupdateProc value references the widgets that are parameters by the Xt name of the widget, not the name of the C variable holding the widget ID.

If this update fails , the application receives an XmNdbErrorCallback on the associated Database Access widget. The native_reason member will contain the error code procided by the procedure (either 99999 or 40001).

Additional Resources:

• Refer to Stored Procedures, in DX HyperHelp.
• See Considerations When Using Stored Procedures in DX HyperHelp for a usage checklist.
• Tutorial 6: Using Stored Procedures takes you step-by-step through the process of inserting a new record by creating and using stored procedures.

One of the most interesting features of DX is its ability to present information in tables and graph format. A wide variety of 2D and 3D widgets are available to help users visually explore and analyze data.

You create tables and graphs the same way you create other application windows: by dragging and dropping data-aware widgets. First you drag and drop the data column which defines the X-axis. Then you drag and drop the Y-axis column. DX even lets you drag multiple Y axes, so you can, for example, display a bar plot and line graph simultaneously.

When creating graphs, you will usually want to group the data by some common value. If you think about graphs you've seen, they seldom display actual data. They usually display sums or totals compared to each other. This grouping is accomplished with the XmNgroupBy resource on queries and widgets with query attributes.

In this example, we've already grouped data for you.. (In Tutorial 5: Graphs Applications, you can practice grouping data on your own.)

• From the Schema Browser, select the OrderEntry database, then the SalesByTerritory table. Choose the Bar Plot Object Typefrom the pull-down menu.

Selecting fields from the SalesByTerritory table

• Drag and drop the Territory and Total_Sales fields from the Schema Browser to the Browser.

• From the Browser menu, select Play Mode. Click the "Search" button. The Bar Plot graph appears!

Simple Graph Application

• Now select Show Compound Children from the Browser View menu. You see the instance hierarchy of the graphs application, with one dbPlotter widget and one child widget.
   

Instance hierarchy of Simple Graph Application

The dbPlotter widget is a special-purpose container widget that defines the labels and axes of your graph. Every graph or table application requires a dbPlotter widget.

Below the dbPlotter widget you see the child widget, defining...

Additional Resources:

• Tutorial 5: Graphs Applications describes how to create a bar plot that automatically calculates grouped sales data and presents it in graphical form.

DX gives you precise control over the sequence of commits and and the number of update statements per commit. By managing the transaction size of commits, you can optimize your application and minimize database lockouts.

DX default settings automatically manage database updates by:

• Collecting all edits made to a single record;
• Issuing one Update statement when the Query cursor moves off the current record;
• Committing the Update as soon as it occurs.

By modifying one Data Access widget resource and a series of Query widget resources, you can specify a wide range of alternative commit and update behaviors. DX also supports the use of stored procedures or triggers that issue their own commit or rollback statements.

Automatic vs. Manual Transactions

DBPak provides a choice of automatic or manual transaction control. The default is automatic, where DBPak begins a transaction before any updates and commits specified by the commitStyle resource on the Query widget. Manual transactions are also available, allowing applications to control transactions on their own.

Controlling Updates

Two Query widget resources, XmNupdateStyle and XmNcommitStyle, control how updates are collected and when commits are executed.

• XmNupdateStyle manages how updates are collected. By default, this resource is set to XiDB_UPDATE_AT_MOTION, which collects multiple updates to a record and issues one Update statement when the cursor moves off the record. Alternatively, XmNupdateStyle can be set to XiDB-UPDATE_PER_EDIT. This setting issues a separate Update statement for each record modification.

• XmNcommitStyle controls when updates are committed. The default setting (XiDB_COMMIT_PER_UPDATE) causes a commit to execute immediately after DBPak updates the database. This helps keep transactions short to provent other users from being locked out of the database.
   

Controlling Commits

DBPak increments its count of uncommitted work after every successful operation. This count may be queried with XtGetValues to retrieve the XmNuncommittedWork resource of the Database Access widget.

This count is also used to control the sensitivity of Control widgets whose XmNqueryOpcode is set to either XiDB_COMMIT or XiDB_ROLLBACK. The COMMIT and ROLLBACK controls automatically become sensitive whenever the uncommittedWork count is greater than zero.

Note that the count is kept by the Database Access widget, even though the Control widgets they effect are attached to Query widgets. This can cause situations where a "Commit" button would be sensitive on an application screen that has no data on it. This behavior is semantically correct because commit operations are made on behalf of all Query widgets in an application, and not just the widget controlling the button.

Explicit Commit or Rollback Operations

By calling the Query widget function XiDBQueryOperation with the XiDB_COMMIT or XiDB_ROLLBACK parameter, you can instruct applications to explicitly commit or rollback the current set of updates. DBPak keeps track of uncommitted transactions, and will not issue the commit or rollback unless there is uncommitted work. This allows developers to build update logic into their applications without having to worry about improper commands being sent to the server.

Additional Resources:

• Refer to the Transactions in DBPak, Database Access Widgets, and Database Access Widget Resource Definitions sections ofDX HyperHelp.
• Refer to the Programmers' Guide of your DBMS for specific information on how your database handles update and commit transactions.